Thursday, June 4, 2015

Do you know which windows port is used by Microsoft SQL Server?

The default TCP port of Microsoft SQL Server default instance is 1433. But if the named instance is different than default instance then SQL server could use different port. Here I found a useful script to find out which TCP port is currently used by SQL server.


DECLARE @InstName VARCHAR(16) 

DECLARE @RegLoc VARCHAR(100) 

SELECT @InstName = @@SERVICENAME 

IF @InstName = 'MSSQLSERVER' 
BEGIN 
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\' 
END 
ELSE 
BEGIN 
    SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\' 
END 

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'


Now, why it is important to know this TCP port number?
If you are planning to access database from remote computer and/or
if the firewall of your computer is opened then you have to allow that TCP port number as exception.

Thats it.

Happy Coding.