How to Connect to a SQL Server Named Instance
My network admins have tightened down the network and while we were once able to connect to our named instances via MySQLServer\MyNamedInstance, we're now only able to connect if we specify the port. What happened and what do I need to tell them to fix the issue?
Back before the days of SQL Server 2000, only one instance of SQL Server could run on a given computer. By default, it listened on a particular network port (TCP port 1433) and/or named pipe (\sql\query). Clients only had to specify the name of the computer in order to try and establish a connection to SQL Server.
With SQL Server 2000 came the ability to run more than one instance on the same computer. However, only one instance could listen on a particular network port or named pipe. Therefore, a mechanism had to be built such that each instance could be connected to separately without confusion. That's what brought about named instances. While there can still be a default instance on a given computer, there doesn't have to be. You could have a named instance with just one instance installed. However, if you ever have 2 or more instances installed, you will have named instances.
SQL Server Listener Service vs. SQL Server Browser Service
So how do clients tell the named instances apart on the connection? Each named instance listens on its own TCP port and/or pipe. The TCP port is, by default, determined dynamically which means it could change any time SQL Server starts up. That causes a problem for clients trying to connect. They don't know what pipe or TCP port to use. As a result, SQL Server 2000 introduced the SQL Server Listener Service, which scans the configurations of all of the instances on a given computer and is prepared to tell a client how to connect if the client requests this information about an instance. In SQL Server 2005 and above, this is replaced by the SQL Server Browser Service. The big difference between the two is with the SQL Server Listener Service, you could never shut it down. The SQL Server Browser Service looks more like the services we're used to, and you can see it if you look at the list of services on a computer:
Therefore, if you don't have named instances on the computer, it doesn't need to be running. Also, if you don't want clients to be able to discover the instances on a given system, you can turn off the SQL Server Browser service, meaning it won't respond to requests on how to connect to a given named instance.
Using These SQL Server Services
When a client wants to find out how to connect to a named instance, it sends a message via the UDP protocol to the computer where the named instance is hosted. It sends specifically to port 1434, asking for the named instance. Here's a packet from a client's attempt to talk to a named instance named SQL2008R2. Note the area called out by the red box. This is where we can see what instance the client was trying to connect to.
The computer hosting the named instance SQL2008R2 will respond back, also sending a message via UDP to port 1434 for the client like so:
Note that the SQL Server Browser service told the client that the named instance SQL2008R2 is listening on TCP port 5555. With this information, the client can now attempt to make a connection to the SQL Server.
Why You Can't Connect to SQL Server
If you can't connect via MySQLServer\NamedInstance, there are three likely possibilities. They are:
- The network admins are blocking UDP traffic on port 1434.
- The SQL Server Browser service was stopped and possibly disabled on the computer hosting the named instance.
- Thepersonal firewalls of your Windows systems may be configured not to allow incoming communications of UDP traffic on port 1434.
If you're a DBA, chances are you can check the SQL Server Browser service, and if so that's the first place to start. If it is up and running, stop and restart it so that it refreshes and checks the network configurations of the instances installed on the computer. Then try to connect.
If that's not it, inquire among your admins if anyone blocked UDP traffic on port 1434. Those are the typical reasons if the SQL Server Browser service is up and running.
Failing those simple checks, you'll likely need to get your network admins involved and they'll need to do packet traces and look for the interchange like what we looked at above. If they don't see it, where it breaks down will give them clues of where to look next.
- Learn more about the SQL Server Browser service.
- Read up and understand why you might use multiple instances of SQL Server on a given computer.
- Look for other reasons you might not be able to communicate with SQL Server.
- Learn how to configure a SQL Server alias if you can't resolve the issue without having to repoint all your connections.
- Understand how to set up static port assignments for named instances (they don't have to stay using dynamic ports)
About the author
View all my tips