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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: