RSS

Find which port SQL Server is using

10 Sep

By default, SQL Server listens on port 1433 for TCP/IP connections . But what if this was changed and what about a named instance? You can find the listener port in 3 ways:

  1. Check the Windows Application event log – Event ID 26022
  2. Use SQL Server configuration Manager
  3. Query the Windows Registry

Windows Application Event Log

Each time an instance starts, SQL Server will record its listening port(s) in the Windows Event Viewer. To find this, open up the Windows Event Viewer and choose the ‘Application’ event log. If you filter on Event ID 26022, you should see four events associated with the start-up. Look for the event that has this in the message body:(Server is listening on any ‘1432’)

 

 

 

 

 

 

SQL Server Configuration Manger

 SQL Server Configuration Manager. Open it up and expand ‘SQL Server Network Configuration’ in the left hand pane. You’ll see menu items for protocols for your various instances. Single click one of the protocol menu items and you’ll get a list of protocols for that instance appearing in the right hand pane. Right click ‘TCP/IP’ and choose ‘Properties’:

Choose the ‘IP Addresses’ tab. Scroll to the bottom of the list and find the ‘IPAll’ section. The ‘TCP Port’ setting will have the listening port, unless the instance is set up to listen dynamically. If so, the ‘TCP Dynamic Ports’ setting will contain the listening port:

Using the Windows Registry

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’

 
Leave a comment

Posted by on September 10, 2011 in SQl server Administration

 

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