Cannot connect to SQL Server named instance from another SQL Server

Question

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    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   I ve verified that SQL Browser is working  which it is since I can connect from development machine  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   I ve restarted the SQL Server and verified that browser   instance services are running Checked event log - nothing of note 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

User · Answer

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

User · Answer

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

User · Answer

To solve this you must ensure the following is true on the machine hosting SQL Server      Ensure Server Browser service is running  Ensure TCP IP communication is enabled for each instance you wish to communicate with over the network   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   Ensure the firewall has an entry to allow communication with SQL Server browser on port 1434 over the UDP protocol   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

User · Answer

I had to specify a port in the SQL Configuration manager   TCP IP Open the port on your firewall Then connect remotely using   server name other database instance  port number   Connected

User · Answer

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    That SQLA and SQLB are either in the same domain  or that there is not a Trust issues between them  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

User · Answer

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

User · Answer

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  lt instance name gt  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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2012