Find TCP IP Port in SQL Server 2012

 

 

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.

[sourcecode language=”sql”]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[/sourcecode]

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.

[sourcecode language=”sql”]
USE master
GO
XP_READERRORLOG 0, 1, N’Server is listening’
GO[/sourcecode]

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:

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.