Tuesday, October 11, 2011

How do you specify a different port number in SQL Management Studio?

Specifying a port number on SSMS is pretty easy

Under the server name , specify the server(or ip)\instanceName, ( comma) port


Another way is to setup an alias in Config Manager. Then simply type that alias name when you want to connect. This makes it much easier and is more prefereable when you have to manage several servers/instances and/or servers on multiple ports and/or multiple protocols. Give them friendly names and it becomes much easier to remember them.

Finding the Port Number for a particular SQL Server Instance

One of the developers recently asked me this question: “I have a SQL Server instance running – how can I tell what port it is running on?”.  There are a couple of ways of finding this information.

1) Using the GUI:

If you expand the SQL Server Network Configuration and select Protocols for MSSQLSERVER (default instance) in the right hand pane you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).  If you double click on TCP/IP the properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.

2)  Check the error log.  You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].”  The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.

3)  Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCPPort as one of the entries and it’s value represents the port number for that instance.  In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

4) You can also chose to trace the client to server communication by using Microsoft Network Monitor or a network sniffer utility.  You can also use the TCP/IP netstat utility

Task Manager
View | Select Columns | Check PID | OK
Find sqlservr.exe as the Image Name, note the PID
Command prompt:
netstat -ano | findstr *PID*

The PID is displayed in the far right. Look for the TCP entry. The port will be after the colon (e.g. means the port is 1433).”