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

Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.
Method 1:
You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.
Here is the TSQL:
select * from master..sysaltfiles
Method 2:
There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry.  There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.
declare @regreader int, @directory nvarchar(4000)
exec @regreader = master.dbo.xp_instance_regread
     N’HKEY_LOCAL_MACHINE’,
     N’Software\Microsoft\MSSQLServer\Setup’,
     N’SQLPath’,
      @directory output, ‘no_output’
select @directory AS InstallationDirectory
GO
If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.
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

Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.
Method 1:
You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.
Here is the TSQL:
select * from master..sysaltfiles
Method 2:
There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry.  There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.
declare @regreader int, @directory nvarchar(4000)
exec @regreader = master.dbo.xp_instance_regread
     N’HKEY_LOCAL_MACHINE’,
     N’Software\Microsoft\MSSQLServer\Setup’,
     N’SQLPath’,
      @directory output, ‘no_output’
select @directory AS InstallationDirectory
GO
If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.
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