15

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

user1839820
  • 193
  • 1
  • 1
  • 9
  • Try to create a SYSTEM DSN (on the sql server that does not hold the desired data) ... that talks to the sql server with the data. This is my quick and dirty "can this machine get to that machine" ... – granadaCoder Jun 10 '13 at 17:46
  • As in .. Control Panel -- Admin Tools -- ODBC. Note, on 64 bit machines, there is a 64 bit and a separate 32 bit ODBC manager. 32 bit version is located at : C:\Windows\SysWOW64\odbcad32.exe – granadaCoder Jun 10 '13 at 17:47
  • @granadaCoder - I can connect from (lets call the non-data one SQLB and the data SQLA) if I use the port style SQLA,PORT versus SQLA/INSTANCE, it seems only when trying to connect with the instance that it is unable to get through. – user1839820 Jun 10 '13 at 17:49
  • Ok...let me search for something..give me a few minutes. – granadaCoder Jun 10 '13 at 17:50
  • What is your "connection string" (I used the term loosely) when trying to wired up SQLB to SQLA. Have you done a "sp_addlinkedserver" ? (Is this the way your trying to get them to talk to each other) – granadaCoder Jun 10 '13 at 18:00
  • What do you mean by "*connect from another SQL Server*"? What are you using to connect with and how are you doing it? – RBarryYoung Jun 10 '13 at 19:59
  • Are SQLA and SQLB in different domains? If so, have you confirmed that there is not a trust issue between them? – RBarryYoung Jun 10 '13 at 20:45
  • @RBarryYoung There are no domains in this setup, each SQL server is independent (and connections are made via SA account so not Windows Auth involved). – user1839820 Jun 11 '13 at 01:10
  • Ideally it would be nice to compare if this server can only not connect to my SQLA versus not able to connect to any SQL Server 2012 Instance - does anyone have a public server with multiple instances I can test connecting too? – user1839820 Jun 11 '13 at 01:12

9 Answers9

15

Your test cases where you cannot connect with "ServerName\Instance" but ARE able to connect to the server via "ServerName,Port" is what happens when you VPN into a network with Microsoft VPN. (I had this issue). For my VPN Issue I simply use the static port numbers to get around it.

This is appearently due to VPN not forwarding UDP Packets, allowing only TCP Connections.

In your case your firewall or security settings or antivirus or whatever may be blocking UDP.

I would suggest you check your firewall setting to specifically allow for UDP.

Browser Artical

On startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all SQL Server instances on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser will return all ports enabled for SQL Server. SQL Server 2005 and SQL Server Browser support ipv6 and ipv4.

When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

Using a Firewall

To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434 in addition to the TCP port used by SQL Server (for example, 1433).

DarrenMB
  • 2,342
  • 1
  • 21
  • 26
  • I've taken the firewall out of the question however you have a point about VPN - I am VPNed into SQLB in order to make the necessary queries (unfortunately it's the only way to access that machine). However that machine itself isn't VPNed anywhere so I'm not sure the VPN would be an issue (my client isn't doing the UDP negotiation the server is). Can you confirm if your VPN issue might still be relevant? At this point I don't want to ignore any possibility! – user1839820 Jun 10 '13 at 20:35
  • 1
    The VPN was just my example of a system that was preventing UDP traffic. If you can connect by Port number you are establishing a TCP/IP connection without any other protocal involved. If your 2 DB Instances are on different machine then there is some level of network between them. On my corporate network we have considerable firewall rules between server systems, sometimes even at the same physical location. If a router is between them and is not allowing UDP traffic that system could be the blocking point. There is not very many other cases that could be your issue. – DarrenMB Jun 10 '13 at 23:35
  • It is also worthy to note that most corporate systems do not use SQL Server instances at all. Which is why this kind of issue would rarely come up. With the default instance and default port the SQL Browser services is completely unused. – DarrenMB Jun 10 '13 at 23:42
  • 1
    @DarrenMB While I agree with everything else you've said (+1), the last bit about corporate systems not using SQL Server Instances is definitely *not* true. I work for a lot of major corporations and the vast majority of them do use Instance names, and most use them extensively. And problems with their names are usually quite rare and easily resovable. – RBarryYoung Jun 11 '13 at 13:38
  • 1
    (And FWIW, I suspected your answer was the most likely correct all along :-) ) – RBarryYoung Jun 11 '13 at 13:42
  • @RBarryYoung Interesting, I prefer the idea of isolating instances myself. I have worked directly, in IT/software, in a company with 60,000 people for 15 years and we interact with many other companies and systems. Not once have I even seen someone configure an instance, connect to or deal with an instance (other then myself). Even our development environments are single instance dedicated servers, where and instance is just not required. Though if I was setting these up I would probably isolate them to instances. And my wife is the DBA Team Lead in my devision and even she doesn't see them. – DarrenMB Jun 11 '13 at 15:51
  • 2
    Exactly. I got into `Windows Firewall with Advanced Security` and there I've enabled my incoming rule, which was for port 1433 on TCP to any port, inside the `Ports and Protocols` tab. And it worked. Thanks! – ilans Jul 10 '14 at 08:56
6

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

Francisco Garcia
  • 974
  • 1
  • 15
  • 26
  • 1
    It was the exact issue and your solution helped me. I went to SQL Server Configuration Manager >> SQL Server Network Configuration >> Protocols for MSSQLSERVER20126 >> TCP/IP and enabled it. I was able to connect the named instance from other machine. – Raghav Nov 26 '16 at 13:54
4

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!

user1839820
  • 193
  • 1
  • 1
  • 9
  • 1
    No problem. It would be nice to be marked as the "answer". this is what I suggested was your issue :) – DarrenMB Jun 11 '13 at 12:52
3

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

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • Sorry I should have been clearer in both cases (from SQLB or my dev machine) I am connecting via SSMS in order to test the connection (using sa account in SQL Server Authentication). – user1839820 Jun 10 '13 at 18:17
  • I just tried your suggestion and added the SQL Server executable for that instance to the firewall but that didn't help either. There must be something special with SQLB that is causing it to have difficultly with SQLA's SQL Browser but I can't think of what! – user1839820 Jun 10 '13 at 18:19
  • Not sure if this helps or causes more confusion, but I tried connecting to the default instance (i.e. no instance name) and SQLB can connect to SQLA fine, but if I try to connect to the default instance with the instance name it fails. – user1839820 Jun 10 '13 at 18:21
  • 1
    Which way are you using SSMS on SQLB? Like, when the credentials screen comes up (when you first start SSMS), are you putting in servername\instance (referring to SQLA) and username for a user that exists *on SQLA*? OR are you logging into SQLB as "sa"...then using TSQL trying to connect back to SQLA ? – granadaCoder Jun 10 '13 at 18:37
  • I'm just using the credential screen to establish a connection. Entering in SQLA\instance with user SA to connect (same thing I'm doing from the development machine). – user1839820 Jun 10 '13 at 19:14
  • On SSMS login window ... go to "Connection Properties"...try selecting "Tcp/Ip"......to avoid a named-pipe attempt. – granadaCoder Jun 10 '13 at 19:17
  • I tried that as well but that didn't help. On another note I came up with a new test: Created a linked server on a SQL Server 2005 database I have and it worked fine (proving firewalls are not the issue). Then ran the same script on the SQL Server 2012 database that is at fault and it errors with: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. – user1839820 Jun 10 '13 at 20:24
  • @user1839820 Where was the SQL Server 2005 instance in relation to SQLA and SQLB? – RBarryYoung Jun 10 '13 at 20:50
  • @RBarryYoung The SQL Server 2005 instance, SQLA and SQLB are all in separate data centers and have only public access to each other - which is why I don't think there are any firewall issues since I can connect from the Sql 2005 as well as from my development machine both using the instance name, and from SQLB with just the port number. – user1839820 Jun 11 '13 at 00:21
  • Try (re)installing the "Feature Pack". (This is out of desperation me thinks) http://www.microsoft.com/en-us/download/details.aspx?id=29065 – granadaCoder Jun 11 '13 at 02:03
  • 1
    You should mark one of the responses as "the Answer" and upvote anyone's responses who gave you helpful information. Yeah, not sure of you saw it, but I mentioned http://www.microsoft.com/en-us/download/details.aspx?id=24009 as the PortQry tool that tells a quick story about talking to a different IP:Port (I know that was only part of what you found) – granadaCoder Jun 11 '13 at 13:03
2

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thanks for the comment - I don't have any aliases defined and I was able to reproduce the "good" behavior on a SQL Server 2005 instance I have where I was able to successfully add a linked server to "SQLA". The same script on SQLB failed to receive the UDP packet. It's almost like SQLB cannot negotiate (despite no firewalls enabled) to SQLA - but it can negotiate with its own SQL Browser fine (it can access itself without an issue) – user1839820 Jun 10 '13 at 20:30
  • 2
    I don't think that SQL Browser is necessary for local connections. – RBarryYoung Jun 10 '13 at 20:39
  • that is an interesting and valid point, on local system it wouldn't need the SQL Browser service so my test was flawed to begin with. – user1839820 Jun 11 '13 at 17:57
2
  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!
Armand G.
  • 1,817
  • 14
  • 8
1

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

PEN SOLUTIONS
  • 7
  • 1
  • 5
0

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

Wolf
  • 6,361
  • 2
  • 28
  • 25
0

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
Mick
  • 6,527
  • 4
  • 52
  • 67