I have run into an issue whilst configuring a multi-instance SQL Server when assigning a specific IP address to the fist instance.
Dedicated IP Setup
The steps I perform to configure a single instance to a dedicated IP address are:
Open SQL Server Configuration Manager (SSCM)
Navigate down the tree to:
SQL Server Configuration Manager (local) +-- SQL Server Network Configuration +-- Protocols for <INSTANCENAME>In the right pane I then doubl-click on TCP/IP which open the Properties of TCP/IP in the tab Protocol
I then ensure that Enabled is set to yes and the option for Listen All is set to No.
I switch tabs into the IP Addresses and ensure that
- Enabled is set to No for all IPv4 and IPv6 addreses, except for the IP address that I want to assign to this instance. For this IP address I set the setting to Yes.
- The TCP Dynamic Ports is empty for all IP addresses.
- For the IP address that I want to assign to this instance I assign the TCP port 1433.
I then close all windows and SSCM displays the standard message:
--------------------------- Warning --------------------------- Any changes made will be saved; however, they will not take effect until the service is stopped and restarted. --------------------------- OK ---------------------------I restart the specific instance and it will generally allow connections to my instance via the dedicated IP address.
I test the instance is listening on the given IP address and port with:
c:\> netstat -abonTCP 10.58.212.112:1433 0.0.0.0:0 LISTENING 2172 [sqlservr.exe]
Above results are from a working server
So far so good. I have done this for the umpteenth time and never had any issues.
Case
Two days ago the SQL Server service for a freshly configured instance on a test server wouldn't start after configuring the 10.58.194.5 address for the instance. I tried the following steps to fix the "broken" test instance:
- Repair Shared Tools
- Repair Instance
- Deinstall and Reinstall SQL Server
- Run a ProcMon to figure out where things were broken.
But nothing has helped so far.
The relevent output from the ERRORLOG file is as follows:
2021-12-17 08:00:03.76 spid17s Error: 26024, Severity: 16, State: 1. 2021-12-17 08:00:03.76 spid17s Server failed to listen on 10.58.194.5 <ipv4> 1433. Error: 0x2741. To proceed, notify your system administrator. 2021-12-17 08:00:03.76 spid17s Error: 17182, Severity: 16, State: 1. 2021-12-17 08:00:03.76 spid17s TDSSNIClient initialization failed with error 0x2741, status code 0xa. Reason: Unable to initialize the TCP/IP listener. 2021-12-17 08:00:03.76 spid17s Error: 17182, Severity: 16, State: 1. 2021-12-17 08:00:03.76 spid17s TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. 2021-12-17 08:00:03.76 spid17s Error: 17826, Severity: 18, State: 3. 2021-12-17 08:00:03.76 spid17s Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. 2021-12-17 08:00:03.76 spid17s Error: 17120, Severity: 16, State: 1. 2021-12-17 08:00:03.76 spid17s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the operating system error log for information about possible related problems.
This is the first time that I am unable to configure/set a SQL Server instance to a specific IP address.
Event Log Entries
The following errors are logged in the Application Event Log of Windows when trying to start the service (newest first):
Level | Date / Time | Source | Event ID Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17120 Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17826 Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17182 Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17182 Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 26024
These Event IDs resemble the information from the ERRORLOG.
Additional entry in the System Event Log is:
Level | Date / Time | Source | Event ID Error | 17.12.2021 11:00:45 | Service Control Manager | 7024
The details being:
The service "SQL Server (<INSTANCENAME>)" has been stopped with the following error: The requested address is invalid in this context.
The XML details being:
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> - <System> <Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" /> <EventID Qualifiers="49152">7024</EventID> <Version>0</Version> <Level>2</Level> <Task>0</Task> <Opcode>0</Opcode> <Keywords>0x8080000000000000</Keywords> <TimeCreated SystemTime="2021-12-17T10:00:45.876740800Z" /> <EventRecordID>15418</EventRecordID> <Correlation /> <Execution ProcessID="736" ThreadID="372" /> <Channel>System</Channel> <Computer>SERVER.DOMAIN.TLD</Computer> <Security /> </System> - <EventData> <Data Name="param1">SQL Server (<INSTANCENAME>)</Data> <Data Name="param2">%%10049</Data> <Binary>4D005300530051004C00240041004C004C00470045004D00450049004E000000</Binary> </EventData> </Event>
Network Interface Card Configuration
There is only one NIC configured on the test server. And it is a virtual one at that. The name is Ethernet0 and the type is Ethernet Adapter for vmxnet3.
The network is configured as follows:
IP address: 10.58.194.4 Subnet mask: 255.255.255.192 Standard gateway: 10.58.194.2
The additional IP addresses that we are using for the SQL Server instances are:
10.58.194.5 / 255.255.255.192 10.58.194.6 / 255.255.255.192 10.58.194.7 / 255.255.255.192 10.58.194.28 / 255.255.255.192 10.58.194.29 / 255.255.255.192
The single NIC is configured to accept connections for the six IP addresses.
Question
Does anybody have an idea how I can fix this problem?
Additional Information via Comments
If I set the Listen All option to Yes in the TCP/IP configuration, then the instance will start. But this is not what I am seeking. I am unable to connect to the instance using the alias or the IP address. The sqlserver.exe process is then no longer listening on port 1433.
After contemplating my observation that the sqlserver.exe process is then no longer listening on port 1433 it seems like the server is unable to bind the IP address to port 1433 for my first instance.
After configuring Listen All to yes and the IP address to have port 1433 , then sqlserver.exe doesn't listen on any IP address (0.0.0.0) for port 1433. It's like the configuration setting for the input box for the port number is corrupt. SQL Server ERRORLOG displays:
2021-12-17 16:27:36.35 spid17s Error: 26058, Severity: 16, State: 1. 2021-12-17 16:27:36.35 spid17s A TCP provider is enabled, but there are no TCP listening ports configured. The server cannot accept TCP connections.`
Side Notes
I was able to set up and configure a productive server, that I received the same day, to use a specific IP without any issues. The only difference was maybe that the six IP addresses I received for the productive server, were in sequential order like this:
10.xx.xxx.111 / 255.255.255.0 -- server 10.xx.xxx.112 / 255.255.255.0 -- instance_1 10.xx.xxx.113 / 255.255.255.0 -- instance_2 10.xx.xxx.114 / 255.255.255.0 --... 10.xx.xxx.115 / 255.255.255.0 10.xx.xxx.116 / 255.255.255.0
