Finding what port SQL Server is listening on

By bradyupton in Monitoring 0 Comment

SQL Server communicates over TCP/IP and listens for incoming connections over what is called a port. The default TCP/IP port for SQL Server is 1433. The port doesn’t have to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server. (http://support.microsoft.com/kb/287932)

Named instances are configured for dynamic ports. This means when the SQL Server service is started it selects a random port. Best practice is to configure named instances to listen on a specific port.

You need to know what port your SQL Server is using so you can allow the ports through the firewall otherwise you can run into connection problems between the client and the server.

There are a few different ways to find the port number that SQL Server is listening on. Below you will find T-SQL statements that will allow you to find the port number for default and named instances.


--SQL Server 2000 Default Instance 
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

--SQL Server 2000 Named Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =     'SOFTWARE\Microsoft\Microsoft SQL Server(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

--SQL Server 2005 Default Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\TCP\IPAll',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

--SQL Server 2005 Named Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.(#)\MSSQLServer\SuperSocketNetLib\TCP\IPAll',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

--SQL Server 2008 R2 Default Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

--SQL Server 2008 R2 Named Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

 --SQL Server 2012 Default Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port

--SQL Server 2012 Named Instance
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll',
 @value_name    =    'TcpPort',
 @value        =    @tcp_port OUTPUT

select @tcp_port