Problems connecting to your newly installed SQL Server instance? Did you happen to create a named instance (e.g. <servername>\sql01)?

Microsoft SQL Server named instances use dynamic TCP ports and rely on the SQL Browser service to provide the port number to the client in order to establish a connection. In a lot of cases I have seen people trying to connect to their new SQL Server named instance (e.g. \sql01) and assuming that SQL Server was listening on port 1433. They open the Windows Firewall to allow inbound TCP traffic in on port 1433 but still cannot connect. Some even specify the port in the connection string if they can.

There are two options – rely on the SQL Browser service or change the named instance to operate on a fixed port.

Using the SQL Browser service
You need to be able to connect to the SQL Browser service (which is often not running) which operates on UDP port 1434. Even then, if your local Windows Firewall on the server hosting SQL Server does not allow inbound traffic on whatever TCP port is being used, then you won’t be able to connect remotely.

Make sure the SQL Browser service is running also, which you can configure in the SQL Server Configuration Manager.

Configuring SQL Server to listen on a fixed port
On the SQL Server, open up the SQL Server Configuration Manager, and navigate to SQL Server Network Configuration > Protocols for <named instance> and right-click on TCP/IP and select Properties

In that dialog, click on the IP addresses tab. You can scroll to the bottom and clear the value for TCP Dynamic Ports and specify a fixed port of your choice.

This entry was posted on August 16, 2011 at 11:47 am. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Posted in sql server by Craig Harvey No Comments Yet