2

I am experiencing an issue where my instance of SQL Server Management Studio 2014 will not connect to any of our production clustered SQL 2000 and 2005 instances. It will however connect to non-clustered 2000, 2005 and 2014 instances without issue.

I can connect to our clustered instances via SQL Server Management Studio 2005 without issue.

It used to work, stopped a month or so back and I just haven't been able to resolve it yet. The other three team members can all connect with SSMS2014 without an issue, it's just my machine. I have performed a repair on SSMS2014 install but this has not helped.

I have no access to the servers as they are our production servers, so cannot make any changes to them. Also the fact that three other team members can connect indicates to me it is NOT server specific by is instead client specific.

Error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Thanks in advance.

GregL
  • 9,370
  • 2
  • 25
  • 36
Dib
  • 71
  • 7
  • did you solve that issue @Dib? – vittore Nov 23 '15 at 20:48
  • No i never hot to the bottom of the issue. Also, another team member got the same problem a few months later and we ended up getting both of our laptops rebuilt and ssms reinstalled. Would still like to know what causes it. Thankfully all our databases are being migrated to non clustered environment so hopefully we will not see it again. – Dib Nov 23 '15 at 21:31
  • thank you. have exactly the same issue, didnt think it was clustered instance being an issue but that instance is in fact clustered. and werid thing is , studio 2008r2 on the same box , can connect to the cluster just fine. – vittore Nov 23 '15 at 21:55
  • I wonder if there was an update to my machine or an add-in or something that just changed something on our machines just enough to prevent connection? – Dib Nov 23 '15 at 21:58
  • I think it is management studio 2014,because as i am saying , studio 2008r2 on the same box can connect just fine. – vittore Nov 23 '15 at 22:10
  • You could be right. Ssms 2005 was fine for me too. The thing is ssms2014 DID work for a bit but then stopped! – Dib Nov 23 '15 at 22:21
  • actually what i was able to do is create a server alias and force named pipes connection - that way ssms2014 worked too. but my another issue was that all .net apps needed to specify connection string with forced named pipes (`np:`) and python apps didnt work at all – vittore Nov 24 '15 at 00:07
  • Interesting. Assuming ssms2014 is written in .net, maybe it is a .net framework related issue not server related? – Dib Nov 24 '15 at 08:30
  • It might be. but it is hard to say exactly as there is also the same issue with python driver. ( but only on the machine that has ssms2014 and ss2014 installed, other boxes are fine) – vittore Nov 24 '15 at 16:11

4 Answers4

3

Windows Firewall is blocking sql server 2005 clustered instance connection. It needs UDP 1434 to be open. The down voted reply put me on the right path.

Mariano C.

MS Support

Stackcraft_noob
  • 115
  • 4
MarianoC
  • 31
  • 2
1

You need to either add a rule for UDP port 1434, or a rule for sqlbrowser.exe so that the SSMS client can talk to the SQL Server Browser service.

I actually see people having this problem surprisingly often so I wrote up an explanation here: http://blog.dereenigne.com/microsoft-sql-server-dynamic-ports-windows-firewall-and-you/

jmorris
  • 111
  • 1
  • The thing is it used to work, so I am assuming the rule was in place once.. was there any windows updates that would have removed the firewall rule? – Dib Feb 08 '16 at 05:21
0

I would recommend that you would only add the old instances as data sources from inside SQL Management Studio -that uses a more generic connection method opposed to the direct connection via SQL Management Studio, this will not have all the functionality but you will still be able to access the data. You can also try to open SQL browser on both ends, have a look into the connection properties for the clusters and use alternative methods.

Alex H
  • 1,814
  • 11
  • 18
  • Thank you, but we need full access, including the ability to kick off jobs, etc, so I am assuming this approach wont allow that? Th thing is IT USED TO WORK on my machine, and does still on the other team members. At some point something has gotten changed on my machine but we haven't been able to find out what. – Dib Apr 22 '15 at 14:18
  • For nothing, I just tried to provide an alternative, not really counting that as an answer, added the answer for others to see the possibilities. – Alex H Apr 22 '15 at 14:50
-1

Just turn off Windows Firewall or add exception for sql server.

V.D
  • 1
  • 2
    Turning off Windows Firewall is never the solution. Details on how to add the necessary exception would be handy to include in your answer. – womble Sep 18 '15 at 01:25