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

50 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

sunilkumarkuppam said...

I was working on the responsive design and this article provided me the lot of information about designing of website. Using this information i can create the look and feel websites.
PHP Training in Chennai | PHP Course in Chennai

sunitha vishnu said...


This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
Android training in chennai
Ios training in chennai

Luckperson said...

Thanks for sharing this informative tips with us. Keep updating.

php training institute in chennai

Aswin Karthick said...

Thank you very useful java training in chennai, core java training in chennaiThank you very useful seo training in chennai, struts training in chennai
me project centers in chennai

Aswin Karthick said...

Thank you very useful java training in chennai, java training in chennaiThank you very useful seo training in chennai, struts training in chennai
me project centers in chennai

Vinoth Kumar said...
This comment has been removed by a blog administrator.
svr online Training said...

good post.thank's for sharing.....
Salesforce Training Online

techvelocity partners said...
This comment has been removed by a blog administrator.
Bee Yes said...

Fantastic Article ! Thanks for sharing this Lovely Post !!

Mathi said...

The provided information’s are very useful to me. It’s a wonderful site for learning web application. Thank you for sharing this wonderful blog.
PHP Training in Chennai | PHP Course in Chennai | PHP Institutes in Chennai | PHP Training Center in Chennai

Arasu Groups said...

Very Nice Blog Updation Keep Updating !!

suvathi said...

wow great Article, the details you have provided are much clear, easy to understand, if you post some more Article, it will be very much useful for me.
PHP Training in Chennai

rajesh redddy said...

Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
Python online training

Nisha Premrahul said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
Graphic Designing Training Course in Chennai | Graphic Designing Training Course in Velachery

krishna swamy said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site...
No.1 UIPath Training Institute in Chennai | No.1 UIPath Training Institute in Velachery

Gandhi I said...

Thank you very much for your valuable article useful for many netizens. Expecting more from you.
Digital Marketing Training Institute in Chennai | SEO Training in Chennai

Mary Adlena said...

What a innovative blog!!..Keep updating
RPA Training Course in Chennai | RPA Training Course in Velachery

Mary Adlena said...

Nice post..Keep blogging..
Graphic Designing Training Institute in Chennai | Graphic Designing Training Institute in Velachery

krishna swamy said...

I really enjoyed while reading your article, the information you have delivered in this post was damn good. Keep sharing your post with efficient news..
Blue Prism Robotic Process Automation in Chennai | Blue Prism Robotic Process Automation in Velachery

krishna swamy said...

This information is very useful and innovative..I gained more knowledge from this.
BluePrism Training Institute in Chennai | UIPath Training Institute in Chennai | Automation Anywhere Training Institute in Chennai

Nisha Premrahul said...

Looking great article thanks much for sharing the wonderful article, would like to read this blog regularly to get more stuff...
Graphic Designing Training Institute in Chennai | UIPath Certification Training in Chennai

Unknown said...

The information shared is very much helpful Thanks for sharing it
ISTQB Certifications Exams Course in Chennai | QA Testing in Meenambakkam

shalinipriya said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
Devops training in Sollonganallur

Deops training in annanagar

Devops training in chennai

Devops training in marathahalli

Devops training in rajajinagar

Devops training in BTM Layout

krishna swamy said...

Great information you are shared, Thanks for sharing most valuable information.
No.1 Microsoft Azure Training Institute in Chennai | No.1 Microsoft Azure Training Institute in Velachery

rajendran said...

Nice post. This post is very helpful. Thank you so much for sharing this post.
Certified Ethical Hacking Training in Chennai | Blue Prism Training Institute in Chennai

nikile siva said...

nice post
good information from the blog
Android Training Institute in Chennai | Android Training Institute in Velachery

thomas hardy said...

Wonderful post..Keep rocking..
Robotics Project Center Training in Chennai | Best Robotics Project Course in Tambaram

saraswathy r said...

Great information you are shared.This post is very helpful. Thank you so much for sharing this post.
Web Designing Training Institute in Chennai | Web Designing Training Institute in Velachery

Nandhu said...

Really great blog, it's very helpful and has great knowledgeable information.
Embedded Project Center Training in Chennai | Best Embedded Project Training in T.Nagar

nikile siva said...

nice article

saraswathy r said...

This information is very useful and innovative.Thanks.
Selenium Training Institute in Chennai | Selenium Training Insritute in Velachery

saraswathy r said...

Awesome Post!Thanks for sharing your view.VMWare Training Institute in Chennai | VMWare Training Institute in Velachery

fawaz shaik said...

Interesting themes you have selected. It would be welcome if you can provide more details about it. Thanks to you.
Certified Ethical Hacking Training in Chennai | Certified Ethical Hacking Training in Velachery

ancy said...

Really great blog, it's very helpful and has great knowledgeable information.
Blue Prism Training Institute in Chennai | VMWare Training Institute in Velachery

deepti said...

Fantastic Article ! Thanks for sharing this Lovely Post !!
CLOUD COMPUTING Classes in Chennai | CLOUD COMPUTING Courses in Velachery

ancy said...

This information shared is very much useful. Thanks for this blog.VMWare Training Institute in Chennai | VMWare Training Institute in Velachery

Saravanan said...

I read your blog it's really good, thanks for sharing valuable information with us.
Selenium Testing Course in Chennai | Selenium Testing Course in Velachery

rupiya rupi said...

You shared a Wonderful article..Thanks..Automation Anywhere Training Institute in Chennai | Automation Anywhere Training Institute in Velachery

revathi said...

I enjoyed this article..Good information that you shared is very useful..Thanks..
Software Testing Training Institute in Chennai|Software Testing Training Institute in Velachery

victoriya said...

This information is great and I got valuable information in this technology..Thanks for sharing your wonderful knowledge..
Java Training Institute in Chennai|Java Training Institute in Velachery

krish said...

The details you shared about this technology is informative..Thanks for posting..
Python programming Training Institute in Chennai|Python Programming Training Institute in Velachery

harsha hai said...

This is great.Thanks for sharing the wonderful article.
No.1 Automation Anywhere Training Institute in Chennai | No.1 Automation Anywhere Training Institute in Kanchipuram | No.1 Automation Anywhere Training Institute in Velachery

Vishal said...

I really enjoyed your article..Your gathered information is very nice... Keep sharing your post..Thanks ..
VLSI Training Institute in Chennai|VLSI Training Institute in kanchipuram

adhi said...

This is Awesome Post. I liked your way of writing,you described the topics very effectively.You keep updating your post.Thanks for sharing your views.
Dot Net Training Institute in Chennai | Dot Net Training Institute in Guindy

Post a Comment