How can I find sql server port number from windows registry?

Question

How can I find sql server port number from windows? Is there any generic way to find port number for sql server 2000, 2005 and 2008?

Answer 1

SQL Server 2000

Default instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

Named instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP

SQL Server 2005

There is no distinction between default and named instances. An instance is assigned a number based on the order it was installed. We first need to locate the registry key for the instance, which looks like

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#

'#' is the number assigned to the instance. The instance name is stored as the default value for this registry key. For a default instance, it is MSSQLSERVER.

Once the registry key for the instance is found, we know the TCP/IP registry key is

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#\MSSQLServer\SuperSocketNetLib\TCP\IPAll

SQL Server 2008

Default instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Named instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll

More information here.

Answer 2

You can also find the port in the SQL Server Configuration Manager:

• Select Protocols for SQL2008 under SQL server Network Configuration
• Right click "TCP/IP" -> select Properties
• Select the IP Addresses-tab
• In the section IP ALL, you should see the ports