Different ways to find the SQL Server Port Number | SansSQL

Saturday, February 12, 2011

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

6 comments:

Anonymous said...

A fourth way is to look in 'sys.dm_exec_connections' Something like ...

SELECT local_net_address,
local_tcp_Port,
count(*) as connections
FROM sys.dm_exec_connections
WHERE local_tcp_port is not null
GROUP BY local_net_address,
local_tcp_Port


SR said...

Thank you for providing these very useful and robust scripts for finding port number quickly. I like the registry and error log scan method best.

Melisa said...

Really good explanation about to find sql server port number, it help me a lot to do this correctly.
Regards,
SAP Training in Chennai|SAP Course in Chennai

Amirtha rao said...

Awesome Post! I like writing style, how you describing the topics throughout the post. I hope many web reader will keep reading your post at the end, Thanks for sharing your view.
Regards,
Angularjs training in chennai|Angularjs training chennai|Angularjs course in chennai|Angularjs training center in Chennai

Paul Miller said...

Interesting Post! Thank you for sharing the recent technological updates.
Regards,
PHP Training in Chennai|PHP Course in Chennai

Priya R said...


Great content thanks for sharing this informative blog which provided me technical information keep posting.
Selenium Training in Chennai | Selenium Testing Course in Chennai

Post a Comment