Find TCP IP Port in SQL Server 2012

As an SQL Server Administrator you might need to find SQL Server TCP IP port configured for your SQL Server Instance. There are many reason to find it but most famous reasons is to give access to developer and set firewall exception if there is any. Generally 1433 is the default port but you may configure different ports if you have more than once SQL Server instance running or may be due to security reason you would like to change the default port of SQL Server.

There are few different ways to find the SQL Server 2012 TCP/IP port out of which I am going to mention few of the most used ways to reach TCP/IP port of SQL Server 2012.

1.) Read TCP IP Port of SQL Server 2012 from Registry:

This is one of the most used ways. Either go to registry to find TCP IP port or execute extended stored procedure in SQL Server itself to read registry.

DECLARE @tcp_port nvarchar(10)
 EXEC XP_REGREAD
 @rootkey    =    'HKEY_LOCAL_MACHINE',
 @key        =    'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll',
 @value_name    = 'TcpPort',
 @value        =    @tcp_port OUTPUT
 SELECT @tcp_port AS TCPIP_Port
 GO

2.) Read TCP IP Port From SQL Server 2012 Configuration Manager.

Open “SQL Server Configuration Manager” from Start Menu-> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager. Refer following screen shot for more details:

Now move towards “SQL Server Network Configuration”-> Protocol for “SQLServerInstance”->TCP/IP property.

Note: My SQL Server instance name is “SQL2K12DEV”, you may have different name

From the property dialog box of TCP/IP, move to the “IP Addresses” tab and all the way down, you can fine “IP ALL”. You can read or configure TCP port from here.

3.) Find TCP IP port from XP_ErrorLog extended Stored Procedure.

 USE master
 GO
 XP_READERRORLOG 0, 1, N'Server is listening'
 GO

You can get output something like this:

4.) Read SQL Server TCP IP Port from Event Viewer

Now last but not least “Event Viewer” which you can find under Control Panel -> Administrative Tools -> Event Viewer. Go to Windows Log->Application in Event Viewer and read the log as shown in following screen capture: