How to Connect to a SQL Server Named Instance

By:   |   Comments (21)   |   Related: > Security


Problem

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?

Solution

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:


SQL Server Browser Service

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.

Client requesting SQL2008R2

The computer hosting the named instance SQL2008R2 will respond back, also sending a message via UDP to port 1434 for the client like so:

SQL Server Browser service responds

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, April 25, 2016 - 6:05:16 AM - David Harding Back To Top (41322)

 The article doesn't actually do waht it says in the title.

I have two instances on SQL server 2014 express on my server. Now the MS has in esence made the named instance all but pointless, how do I find out which port each of them is listening on? and how do I set up my ODBC driver to connect to that port?

 That was what I was hoping to find out.

Friday, September 18, 2015 - 5:25:08 AM - Maurits Back To Top (38704)

Very helpful post, thank you very much for sharing.

Thursday, July 11, 2013 - 9:04:50 AM - K. Brian Kelley Back To Top (25794)

Shine,

 

There's a whole lot of reasons you could be getting this error. It would probably be better to post that a forum dedicated to helping solve problems. 

Wednesday, July 10, 2013 - 11:31:49 AM - Shine Back To Top (25776)

Hi all,

Kindly help me with this error:

Unable to connect to server"Server2"

Reason: [DBNETLIB][ConnectionOpen(PreloginHandshake()).]General network error. Check your network documentation

This error pop up when i was connecting my network station to our server using pastel evolution.

Tuesday, May 28, 2013 - 1:00:00 PM - Carter Back To Top (25169)

Thanks for this post, very helpful!

 

Wednesday, March 13, 2013 - 1:38:18 AM - Deepak Back To Top (22769)

Hi Brian,

You saved my day  cheers :-)

 

 

Many Thanks

Deepz

 

 

 

Friday, October 12, 2012 - 5:37:32 PM - Soo Back To Top (19901)

Hi I have strage problem here

sqlserver 2005 express doesn't want connect to server, but when i open any table on database from management studio, application connect to server.

Whould you please help me what happen?

thanks

 

Monday, April 30, 2012 - 11:40:05 AM - Natali Back To Top (17210)

Thank you, Brian. We had similar situation in our company.

I would like to add very helpful msdn articles that helped to resolve our issue:

http://technet.microsoft.com/en-us/library/ms177440(v=sql.105).aspx; http://technet.microsoft.com/en-us/library/cc646023(v=sql.105).aspx

And one more tip. If we want to hide a Sql instance from the "advertising" via UDP 1434 and do NOT harm client applications, use "hide" option setting in the particular instance network configuration: http://technet.microsoft.com/en-us/library/ms179327(v=sql.105).aspx

Thanks!

Thursday, April 12, 2012 - 2:36:46 PM - ALZDBA Back To Top (16882)

Thank you for this lovely synthesis.

@John Miceli :

- Did you apply SP1 ( or SP1+CU5 ) or are you still on SQL2008R2 RTM ?

- are you using a linked server definition ? If yes:  Did you enable collation compatibiliy ?

 

Thursday, April 12, 2012 - 10:20:25 AM - John Miceli Back To Top (16875)

We have a very interesting additional problem that goes along with this article (which was excellent, by the way):

Our new SQL Server 2008 R2 box uses the standard 1433 port and it is connecting to a SQL Server 2005 box that has a named instance operating on port 2300.  We can make the connection, but the performance is HORRID! A SELECT against a table with one row on the 2005 box takes over a minute to return the row (which has 8 fields). 

There is a known issue that is on the MSDN site and we are working with Microsoft on this problem, but if anyone has any experience with this and suggested solution, I would be extremely grateful to hear them.

Thanks in advance for any assistance you can offer,

John Miceli, MCP, MCDBA

Thursday, April 12, 2012 - 9:12:49 AM - AnonymousCoward Back To Top (16874)
@Brian: thanks. Glad it wasn't me this time - it often is ;)

Thursday, April 12, 2012 - 8:01:09 AM - K. Brian Kelley Back To Top (16870)

Anonymous Coward, you're correct. It should read *FROM* 1434. The SQL Server Listener/Browser service will send to the port that the client used as its source port. And when it comes from the server, the source port will be 1434. I'll get with the admins to fix the typo.

Thursday, April 12, 2012 - 5:07:42 AM - Daklo Back To Top (16867)

Don't know if this helps but to connect to sql with a non default port number all you have to do is put it after the server name;

So for port 6500 e.g.;

myserver\mydatabase,6500

 

Thursday, April 12, 2012 - 4:29:06 AM - AnonymousCoward Back To Top (16866)
Interesting, but: "The computer hosting the named instance SQL2008R2 will respond back, also sending a message via UDP to port 1434 for the client like so:" If I'm not mistaken, the screenshot shows a packet being sent FROM port 1434 at the server end to another port (63614 in the screenshot). This does make sense as it's trying to "connect" back to the client. Suppose a client initiated multiple requests for named instance resolutions (to multiple servers). Then the client would have to have a server on udp 1434 and unravel the responses?! If I'm correct, you might want to change that sentence to: "The computer hosting the named instance SQL2008R2 will respond back by sending a message from port 1434 via UDP to the client like so:" Also: your possibilities for failure: "Thepersonal firewalls of your Windows systems may be configured not to allow incoming communications of UDP traffic on port 1434." would change to "The personal firewalls of your Windows systems may be configured not to allow incoming communications of UDP traffic from port 1434." ... though I'd suspect this will not be very likely as support for allowing established/related connections should be enough (not totally sure though).

Friday, April 6, 2012 - 11:36:03 AM - K. Brian Kelley Back To Top (16802)

Manually allow udp/1434 inbound from any IP addresses the cluster uses (physical nodes and the one for any virtual nodes). The problem may be that the workstation is sending to one IP (the one for the virtual instance) and the response is coming back from a different IP (like one of the physical nodes).

 

Friday, April 6, 2012 - 9:59:17 AM - sda Back To Top (16801)

We have a starnge situation -Windows 7 workstations CANNOT connect to a SQL2005 NAMED instance ON A cluster from SQL2005 Enterprise Manager or SQL2008 Enterprise Manager. The same workstation can connect to SQL2005 named instances on non-clusters, and dafault(one instance only) instance on a cluster.

All non-windows 7 machines CAN connect to the Named Instance on the SQL2005 cluster. Have you faced this and can you please coment how to proceed. Thank  you.

Tuesday, March 27, 2012 - 8:16:15 AM - kiran Back To Top (16633)

i not getting

Saturday, March 24, 2012 - 12:48:04 PM - K. Brian Kelley Back To Top (16602)

Standard warning applies... NetMon and any packet sniffer could be considered a hacking tool by your org. So either do this in your private lab or get permission from someone who has the authority to give it. And if the latter, get it in writing (actual letter or an email you can archive off).

 

Friday, March 23, 2012 - 1:43:03 AM - Chintak Back To Top (16585)

Thanks Jason...

Thursday, March 22, 2012 - 2:01:14 PM - Jason Back To Top (16583)

Go download Microsoft Network Monitor 3.4, this is a sniffer you can use locally.

Regards,

Jason

http://dbace.us

Thursday, March 22, 2012 - 1:22:14 AM - Chintak Back To Top (16576)

Hi Brian,

Thanks for this helpful tip.

Can you add another tip for how to view the netwrok packet? This will be really helpful.

- Chintak.














get free sql tips
agree to terms