Different ways to find the SQL Server Port Number

We all know that SQL Server by default listens to the port number 1433. But, due to security reasons, the default port number will/can be changed to a different port in order to minimize hacks on SQL Server.

There are many ways using which you can find the port number of the SQL server which it is listening to.

1. Using the SQL Server Configuration Manager
2. Using Registry
3. Using Error Log
4. etc...

Using SQL Server Configuration Manager:
Click Start >> Run
For SQL Server 2005 Type "SQLServerManager.msc" and click OK
For SQL Server 2008 Type "SQLServerManager10.msc" and click OK

This will Open the SQL Server Configuration Manager for you.
Now in the Left Pane, expand "SQL Server Network Configuration" and click on "Protocols for <InstanceName>"
Now In the right pane, right-click on the "TCP/IP" protocol and go to properties

In the TCP/IP properties, go to the "IPAddresses" Tab and scroll down and locate the section "IPAll"
In the "IPAll" Section, the "TCP Port" represents the Port Number SQL Server is listening to.

Note: <InstanceName> is your SQL Server Instance Name

Using Registry:
To find the SQL Server Port number using registry, execute the below query.

DECLARE @InstanceName nvarchar(50) DECLARE @value VARCHAR(100) DECLARE @RegKey_InstanceName nvarchar(500) DECLARE @RegKey nvarchar(500) SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER')) --For SQL Server 2000 if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8 BEGIN if @InstanceName='MSSQLSERVER' Begin SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\' END ELSE BEGIN SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\' END EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'TcpPort',   @value = @value OUTPUT   Select @@SERVERNAME as ServerName,@value as PortNumber END --For SQL Server 2005 and up if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey_InstanceName,   @value_name = @InstanceName,   @value = @value OUTPUT SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'TcpPort',   @value = @value OUTPUT   Select @@SERVERNAME as ServerName,@value as PortNumber END

Using Error Log:

To find the SQL Server Port number using error Log, execute the below query.

SET NOCOUNT ON if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8 BEGIN Create Table ##ErrorLog_2K (ErrorLog nvarchar(1000), ContinuationRow int ) INSERT INTO ##ErrorLog_2K Exec master..xp_readerrorlog SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(ErrorLog,5),1,4) as [PortNumber] FROM ##ErrorLog_2K where ErrorLog like '%SQL Server listening on 1%' DROP TABLE ##ErrorLog_2K END if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN Create Table ##ErrorLog (Logdate datetime, ProcessInfo nvarchar(100), [Text] nvarchar(1000)) INSERT INTO ##ErrorLog exec master..xp_readerrorlog SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(text,6),1,4) as [PortNumber] FROM ##ErrorLog where text like 'Server is listening on % ''any'' %' DROP TABLE ##ErrorLog END SET NOCOUNT OFF

You can download the scripts from here.

1. SansSQL_FindPortNumber_UsingRegistry.sql
2. SansSQL_FindPortNumber_UsingErrorLog.sql