Quickest way to find a SQL Server instance PORT number!

Brilliant piece of code to quickly find out the Port SQL server is running from, without having to trawl through the logs, or the registry, or the SQL Server Configuration Manager!

Developed by Rudy Panigas, Published on SQLServerCentral

-- Show instance, SQL version, and port number for SQL 2000/2005/2008/2008R2 Version
set nocount on
go
DECLARE @SqlPort Nvarchar(10)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
DECLARE @value_name Nvarchar(20)
DECLARE @ProductVersion Nvarchar(10)

-- Scan for SQL 2008R2
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.50'
BEGIN

select @ProductVersion = '2008R2'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
--END
--ELSE BEGIN
--set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='TcpDynamicPorts',
@value=@SqlPort output

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

END

-- Scan for SQL 2008
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.0.'
BEGIN

select @ProductVersion = '2008'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
--END
--ELSE BEGIN
--set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='TcpDynamicPorts',
@value=@SqlPort output

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

END

-- Scan for SQL 2005
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN

select @ProductVersion = '2005'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='TcpPort',
@value=@SqlPort output

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

END

-- Scan for SQL 2000
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN

select @ProductVersion = '2000'
Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))
insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'
select @SqlPort = Data from #Port_2000
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port
drop table #Port_2000

END

Other ways of finding the PORT Number…

SQL Server Logs:

When an Instance is started, it outputs the port number it is listening on to the log:

Server is listening on [ 'any' <ipv4> 1855].

SQL Server Configuration Manager (SSCM):

  1. Open SSCM
  2. Expand SQL Server Network Configuration
  3. Highlight ‘Protocols for <instance name>’
  4. Double Click TCP/IP
  5. Click on the IP Addresses Tab
  6. The assigned Dynamic Port is shown on the bottom of the properties:

image

In the Registry:

The dynamic port is listed in the registry. The path is different depending on the version of SQL Server…

SQL 2000

‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp’, ‘tcpPort’

SQL 2005 Local Instance

‘SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp’

SQL 2005 Names Instance

‘SOFTWARE\Microsoft\Microsoft SQL Server\’ + <instance_name> + ‘\MSSQLServer\SuperSocketNetLib\Tcp’

SQL 2008 Local Instance

‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp’

SQL 2008 Names Instance

‘SOFTWARE\Microsoft\Microsoft SQL Server\’ + <instance_name> + ‘\MSSQLServer\SuperSocketNetLib\Tcp’

SQL 2008 R2 Local Instance

‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp’

SQL 2008 R2 Names Instance

‘SOFTWARE\Microsoft\Microsoft SQL Server\’ + <instance_name> + ‘\MSSQLServer\SuperSocketNetLib\Tcp’

Dynamic Port Allocation

Only named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.

The allocated port number is written to the Windows registry. Every time that you start that named instance of SQL Server, it uses that allocated port number. However, in the unlikely case that another program that is already running on the computer is using that previously allocated (but not static) port number when you start SQL Server, SQL Server chooses another port.

Advertisements

One Response to Quickest way to find a SQL Server instance PORT number!

  1. lostpacket says:

    So much thankful to you. I have been fighting with a connection error. I couldn’t open the configuration manager for sql 2008. I had to manually enable tcp protocol and I came across your post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: