Read default SQL Server port and change it in SQL Server 2008

Listening port plays really very vital role in any version of SQL Server. There are some port number like 1433, 1533, 1434 etc. are really widely known and famous so whenever hackers try to scan port of your SQL Server, they first try to scan these ports as those are very famous.

It is really good practice to change default SQL Server port to something else from 1433. After reading this short message, if you are not experience DBA and SQL Server developer, you might think, how can read the port of SQL Server? It is really very easy with few different ways.

Read/Change Default SQL Server Port:

Method 1:

1.)   
Go to Start->All Programs->Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager

2.)    From the configuration manager, expand “SQL Server Network Configuration” from left hand side tree.

3.)    Now click on “Protocols for YourSQL ServerInstance”

4.)    From the right hand side, you can find “TCP/IP”, right click on it and click on property

5.)    From the dialog box, click on the “IP Addresses” tab and look at port no in “TCP Port” property under “IP ALL”.

6.)    If you want to change your port, change it in “TCP Port” and Restart your SQL Server instance

Method 2:

Read the port from your registry with below code snippet.

DECLARE @tcp_port nvarchar(10)

EXEC xp_regread

@rootkey    =    'HKEY_LOCAL_MACHINE',

--SQL2K8 is my instanace name of SQL Server

--may be different in your case

@key        =    'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll',

@value_name    = 'TcpPort',

@value        =    @tcp_port OUTPUT

select @tcp_port

If you wish to change port # from registry, open “regedit” from command prompt, go to the path given in above SP. Path is already provided in @key argument. Double click on “TcpPort” key and change the value right from there.

I have used XP_RegRead stored procedure to read registry. Sometime back I have used other system stored procedure to read registry and find default installation path of your sql server. The name of that stored procedure is XP_Instance_RegRead.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com