[sql-server] Cannot connect to SQL Server named instance from another SQL Server

I would appreciate some help as I've been stuck for 2 days on this issue!

Scenario: I can connect to SERVER\INSTANCE from my development machine (and another colleagues), but cannot connect from another SQL Server. The error I get is the generic "...verify the instance name is correct..". Things I have done/verified:

  1. I've disabled the firewall on the destination (and source) server to see if it is a firewall issue (this seems most likely since I can connect from my machine but this didn't help).

  2. I've verified that SQL Browser is working (which it is since I can connect from development machine)

  3. Since both SQL Servers have multiple instances and hard-coded ports I even made sure that they were different ports incase there was some conflict (this didn't help).

  4. I've restarted the SQL Server and verified that browser / instance services are running

  5. Checked event log - nothing of note

  6. Interestingly enough if I don't connect over the instance name but connect via the dynamic port (i.e. SERVER,PORT) from the second server it works fine - which suggests to me SQL Browser is at fault, except that it works fine locally to the server and from my development machine.

Any ideas and suggestions? Thanks.

Edit: For comment clarification I'll refer to the data SQL Server as SQLA and the non-data SQLB.

Edit #2: Adding more test cases / info:

Info: The above tests were all done via the SSMS interface to establish a connection to the database, the databases involved are both 2012.

New Test Case: I tried running a script to setup a linked server instead and found that running the script on a SQL Server 2005 box worked fine, but running the same script on the SQL Server 2012 server (SQLB) failed to connect to SQLA with the error: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Edit #3: Narrowed down the potential issue:

Downloaded and ran PortQry and when run from my dev box I get all the instances returned with querying 1434 over UDP, running the same query from SQLB returns NO instances and it states 1434 as being FILTERED whereas on the dev box it returned as LISTING. I can only think this is firewall related except that I've disabled the firewall on both machines

This question is related to sql-server sql-server-2012

The answer is


To solve this you must ensure the following is true on the machine hosting SQL Server...

  1. Ensure Server Browser service is running
  2. Ensure TCP/IP communication is enabled for each instance you wish to communicate with over the network. enter image description here
  3. If running multiple instances, ensure each instance is using a different port, and that the port is not in use. e.g for two instance 1433 (default port for the default instance, 1435 for a named instance. enter image description here
  4. Ensure the firewall has an entry to allow communication with SQL Server browser on port 1434 over the UDP protocol.
  5. Ensure the firewall has an entry to allow communication with SQL Server instances on the ports assigned to them in step 3 over the TCP protocol enter image description here

well after spending about 10 days trying to solve this issue, i finally figured it out today and decide to post the solution

in the start menu, type RUN, open it the in the run box, type SERVICES.MSC, click okay

ensure that these two services are started SQL Server(MSSQLSERVER) SQL Server Vss writer


Not sure if this is the answer you were looking for, but it worked for me. After spinning my wheels in Windows Firewall, I went back into SQL Server Configuration Manager, checked SQL Server Network Configuration, in the protocols for the instance I was working with look at TCP/IP. By default it seems mine was set to disabled, which allowed for instance connections on the local machine but not using SSMS on another machine. Enabling TCP/IP did the trick for me.

http://technet.microsoft.com/en-us/library/hh231672.aspx


You've tried alot. And I feel for you. Here is an idea. I kinda followed everything you tried. The mental note I have in my head goes like this: "When Sql Server won't connect when you've tried everything, wire up your firewall rules by the program, not the port"

I know you said you disabled the firewall. But something is telling me to give this a try anyways.

I think you have to open the firewall "by program", and not by port.

http://technet.microsoft.com/en-us/library/cc646023.aspx

To add a program exception to the firewall using the Windows Firewall item in Control Panel.


On the Exceptions tab of the Windows Firewall item in Control Panel, click Add a program.


Browse to the location of the instance of SQL Server that you want to allow through the firewall, for example C:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn, select sqlservr.exe, and then click Open.


 Click OK.

EDIT..........

http://msdn.microsoft.com/en-us/library/ms190479.aspx

I'm a little cloudy on which "program" you're trying to use on SQLB?

Is it SSMS on SQLB? Or a client program on SQLB ?

EDIT...........

No idea if this will help. But I use this to ping "ports" ... and something that is outside of the SSMS world.

http://www.microsoft.com/en-us/download/details.aspx?id=24009


  1. I had to specify a port in the SQL Configuration manager > TCP/IP
  2. Open the port on your firewall
  3. Then connect remotely using: "server name\other database instance,(port number)"
  4. Connected!

I've finally found the issue here. Even though the firewall was turned off at both the locations we found that a router in the SQLB data center was actively blocking UDP 1434. I was able to determine this by installing the PorQry tool by Microsoft (http://www.microsoft.com/en-ca/download/details.aspx?id=17148) and running a query against the UDP port. Then I installed WireShark (http://www.wireshark.org/) to view the actual connection details and found the router in question that was refusing to forward the request. Since this router only affected SQLB this explains why every other connection worked fine.

Thanks everyone for your suggestions and assistance!


I need to do 2 things to connect with instance name

1. Enable SQL Server Browser (in SQL server config manager)
2. Enable UDP, port 1434 trong file wall (if you using amazon EC2 or other service you need open port in their setting too)

Restart sql and done


Do you have any Client Aliases defined on your Development Machine? If so, then define them the same on SQLB also. Specifically, I suspect that you have Client Aliases in InstanceName format that are defining the ports, thus bypassing the actual Instance names and the need for SQL Browser (partially). There are other possibilities with Client Aliases also though, so just make sure that they are the same.

To check for SQL Client Aliases, use the SQL Server Configuration Manager, (in the microsoft SQLServer, Program Start menu). In there, goto Client Configuration, and then "Aliases".


Other things to check:

  1. That SQLA and SQLB are either in the same domain, or that there is not a Trust issues between them.

  2. Make sure that SQLB has TCP/IP enabled as a Client Protocol (this is also in SQL configuration Manager).


By some of your responses I think that you may have missed the point of my statements about Domains and Trusts. You cannot connect to a SQL "Server\Instance" unless there is sufficient trust between the client and the server. This is because the whole Instance-Naming scheme that SQL Sevrer uses is dependent on SPNs (Service Principal Names) for discovery, location and authorization, and SPNs are stored in the AD. So unless the client is on the same box, the instance needs to be able to register its SPN and the client needs to be able to browse whatever AD forest the server instance registered it's SPN into.

If you cannot do that, then Instance names effectively do not work and you have to use the Port number (or pipe name) instead. This is what I now suspect is going on.