4

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:

  1. Open SQL Server Configuration Manager (SSCM)

  2. Navigate down the tree to:

    SQL Server Configuration Manager (local)
    +-- SQL Server Network Configuration
        +-- Protocols for <INSTANCENAME>
    
  3. In the right pane I then doubl-click on TCP/IP which open the Properties of TCP/IP in the tab Protocol

  4. I then ensure that Enabled is set to yes and the option for Listen All is set to No.

  5. 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.
  6. 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   
    ---------------------------
    
  7. I restart the specific instance and it will generally allow connections to my instance via the dedicated IP address.

  8. I test the instance is listening on the given IP address and port with:

    c:\> netstat -abon
    
    TCP    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:

  1. Repair Shared Tools
  2. Repair Instance
  3. Deinstall and Reinstall SQL Server
  4. 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
1
  • Error 26058 should only happen if there aren't entries for a port number on the configured IP. Please double check the registry under the supersocketnetlib to make sure that IP# entry does in fact have either a tcp port or dynamic port entry. If not, create one and try to start up again. Dec 17, 2021 at 18:07

1 Answer 1

2

I am documenting the answer in order to provide others with ways of finding out why an IP address might be causing an issue when configuring an IP address for an individual SQL Server instance.

The following IP addresses were the IPs that were assigned to the NIC on the server and to the server itself:

IP address:        10.58.194.4       (server)
Subnet mask:       255.255.255.192
Standard gateway:  10.58.194.2
IP address:        10.58.194.5       (instance 1)
IP address:        10.58.194.6       (instance 2)
IP address:        10.58.194.7       (instance 3)
IP address:        10.58.194.28      (instance 4)
IP address:        10.58.194.29      (instance 5)

Following is a section of the IP range and what they were actually used for from a network perspective:

IP address:        10.58.194.0       (reserved; don't use)
IP address:        10.58.194.1       (reserved; don't use)
IP address:        10.58.194.2       (virtual Gateway)
IP address:        10.58.194.3       (reserved; don't use)
IP address:        10.58.194.4       (Actual Gateway Data Centre 1)
IP address:        10.58.194.5       (Actual Gateway Data Centre 2)
IP address:        10.58.194.6       (reserved; don't use)
IP address:        10.58.194.7       (reserved; don't use)

As you can see the IP address of the server matches the IP address of the Actual Gateway Data Centre 1. And the IP address that had been reserved for the first SQL Server instance was the IP address of the Actual Gateway Data Centre 2.

What had happened?

During the initial configuration of the server the IP addresses were partially in use be the network technicians. Because the current physical gateway for the server was running on 10.58.194.5 the server was able to be configured to run and start on 10.58.194.4.

The configuration of the additional IP addresses on the NIC wasn't an issue, because it is initially just an entry in the registry. The server was then handed over the DBA team.

Then along came the DBA guy (me) and tried to configure a new SQL Server instance on the IP address of the Actual Gateway Data Centre 2. Because this address was actually in use as the current gateway of the Windows Server, the SQL Server instance was unable to start.

Hint 1

Running an ipconfig showed the actual IP addresses that were active on the server:

Ethernet-Adapter Ethernet0:

   Verbindungsspezifisches DNS-Suffix: 
   Verbindungslokale IPv6-Adresse  . : 
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.4
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.7
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.28
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.29
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   Standardgateway . . . . . . . . . : 10.58.194.2

This showed us that ...194.5 and ...194.6 weren't available for the server/instance even though they had been configured for the NIC.

Hint 2

Running an tracert to a different server in a different IP range would have equally shown that the IP address 10.58.194.5 was in use as the current gateway for the server.

Solution

The solution to our problem was to move the server to a different IP range and to document that the first seven IP addresses of a range are reserved for the network technicians.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.