Freigeben über

How to configure SQL server to listen on different ports on different IP addresses?

  • Artikel

Technorati Tags: SQL Port

The following post describes how you can configure your SQL Server to listen on a different port(s) on different IP addresses that are available on your system. This procedure applies to both SQL server 2005 and SQL server 2008.

Case 1: SQL Server is installed in an environment where the IP addresses had not changed since the time it is originally installed.

1) Open SQL Server configuration manager.

2) Ensure that TCP/IP protocol is enabled

image

By default, all the IP addresses listen the same port or ports configured in SQL Server configuration manager. The SQL Server error log will have an entry like the following:

2009-07-15 17:40:06.39 Server      Server is listening on [ 'any' <ipv4> 2675].

3) In the TCP/IP properties set ‘Listen All’ to ‘No’

image

4) Go to the IP addresses tab for the instance, set Enabled to Yes and TCP port number field for the specific IP address to the desired

port. The screen will look as follows:

image

5) Restart the SQL Server. Now you should see an entry similar to the following in the SQL error log:

2009-07-15 18:03:10.58 Server      Server is listening on [ x.y.z.w <ipv4> 2000].
2009-07-15 18:03:10.59 Server      Server is listening on [ x.y.z.v <ipv4> 2001].

As you can see from the above each of the IP addresses is listening on different port.

Case 2: SQL Server is installed in an environment where the IP addresses change dynamically, but the number of IPs active on the system are the same (For example there are two IPs active on the system, but because of lease expiry or when moving to a new subnet, the system hosting SQL Server got either one or both of its IPs changed). In this case, get the output of ipconfig /all on the system, and edit one or all the IP addresses as needed with the new IP addresses that are active on the system using a similar procedure discussed in Case 1.

Case 3: You add an additional IP address on the system:

In that scenario, you will not be able to use the procedure documented in Case 1  or Case 2 above as the Configuration Manager’s IP address list will just only have as many entries as the number of IPs that SQL Server found when it is installed

In this scenario, you can take the following steps to update the registry values SQL server looks at to listen on different ports on different IP addresses.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk. . For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756 How to back up and restore the registry in Windows

1) Navigate to the following registry key on the SQL server machine:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\!INSTANCEID!\MSSQLServer\SuperSocketNetLib\Tcp\

Note: !INSTANCEID! is a place holder for your SQL server instance.

2) Right click on IP1, export the registry key as SQLIP template.reg

3) Edit the  key name and IP address key .reg file that you exported in step 2 with notepad with the new IP address. (You can get the IP address list on the system by executing ipconfig /all > ipconfig.txt command from the command prompt).

The contents would look as follows:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL10\MSSQLServer\SuperSocketNetLib\Tcp\IP3]—> Change this name to a new value for example IP4
"Enabled"=dword:00000000
"Active"=dword:00000001
"TcpPort"="2001"
"TcpDynamicPorts"=""
"DisplayName"="Specific IP Address"
"IpAddress"="a.b.c.d" –> Update this with new IP address value 

4) After editing the file save it with a different name – for example new IP4.reg

5) Double click the .reg file from step 3 to import the key as a new entry under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL10\MSSQLServer\SuperSocketNetLib\Tcp] registry entry.

6) Repeat steps (3),(4) and (5) for any other new IP addresses that you currently want to configure on the system.

Note: After adding the above registry keys, the new IP addresses should now show up in SQL server configuration manager.

7) Optional: Clean up any IPs that are no longer active by deleting the associated <IP_n> registry keys.

8) In SQL Server configuration manager, IP addresses tab, ensure that only the addresses that are listed in the ipconfig output on the system have Enabled property set to Yes and the other set to No

image

Note: If IP All is set to No and if the IP addresses tab has some IP addresses that have ‘Enabled’ set to ‘Yes’ but the actual IP is no longer active on the system, SQL Server service fails to start with an error message like the following logged to SQL Server error log:

2009-07-16 15:43:07.87 Server Server is listening on [ 127.0.0.1 <ipv4> 2753].
2009-07-16 15:43:07.89 Server Error: 26024, Severity: 16, State: 1.
2009-07-16 15:43:07.89 Server Server failed to listen on x.y.z,w <ipv4> 2000. Error: 0x2741. To proceed, notify your system administrator.
2009-07-16 15:43:07.95 Server Error: 17182, Severity: 16, State: 1.
2009-07-16 15:43:07.95 Server TDSSNIClient initialization failed with error 0x2741, status code 0xa. Reason: Unable to initialize the TCP/IP listener. The requested address is not valid in its context.

2009-07-16 15:43:07.95 Server Error: 17182, Severity: 16, State: 1.
2009-07-16 15:43:07.95 Server TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The requested address is not valid in its context.

2009-07-16 15:43:07.95 Server Error: 17826, Severity: 18, State: 3.
2009-07-16 15:43:07.95 Server 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.
2009-07-16 15:43:07.95 Server Error: 17120, Severity: 16, State: 1.
2009-07-16 15:43:07.95 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Case 4: SQL Server is installed in a clustered environment.

On cluster, you cannot configure SQL Server to listen on a specific IP addresses. You must chose IPALL. The IP addresses on which the cluster instance will be listening on is determined by cluster resources (configurable through Cluster Administrator, by adding IP Address resources under SQL Network Name resource).

Additional links:

 

 

 

 

Ramu Konidena

Microsoft SQL Server Support Technical Lead

Comments

  • Anonymous
    May 09, 2015
    great explain

  • Anonymous
    October 06, 2015
    Good One...

  • Anonymous
    September 21, 2016
    This is an excellent post. This really, really needs to be part of BOL.

  • Anonymous
    January 24, 2018
    Hello, this is very good solution, Good job :)