Running SQL Server on a Static Port

General 2 Minutes

Configure SQL Server to listen on a Static Port:

Go to Configuration manager–> SQL Server Configuration Manager–> Protocols for ‘Instance-name’; –> Right click on TCP/IP Properties.On the IP addresses tab, on the bottom,In the ‘IPAll’ section : blank out the TCP Dynamic Ports and enter a port number in TCP Port option. Restart the SQL Server service.
Configuration
We can look into the sql server error log to validate on which port SQL Server is listening.
If the SQL Browser service is running, no need to mention the port number in the connection string.
If the SQL Browser service is not running, we can either connect as ServerName/InstanceName,PortNumber or create an alias on each client.

To Create an alias –> On each Client, Go to configuration manager–>SQL Native Client Configuration –> Aliases.
Enter an Alias Name and port number on which the connecting server is listening and the connecting server Name.This should be done on every client machine that needs to connect to the server. To connect to the server,enter the Alias name in the SSMS, instead of ServerName\InstanceName.

Sometimes, even though we stopped running the SQL browser service ,we can still connect to the server(other protocols are disabled as well.).The reason here is SQL server is still listening on the same port like it was before(when the browser was running).That’s why we can still connect to the server.

If SQL Server changes the dynamic port number, we will not be able to connect to the server.
One thing I learned is that even though we restart the SQL server,it will try to use the same port number even when dynamic ports are enabled unless it was taken by some other service.Only when the port number was taken by some other service, it will use a new port.

Example: Let’s say,we enabled dynamic ports. SQL Server is running on port 61023. SQL Server Browser is running.We can connect to the SQL Server just fine.Now,Stop the SQL Server Browser. Restart the SQL server. Connect to the SQL server.If you can still connect to the server, it means that the SQL server is listening on the same port as before(when SQL browser was running).

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published