Cannot connect remotely to a SQL Server named instance

12
votes

I have SQL Server 2008 installed on a VM in which I have three instances:

  • server name
  • server name\DB1
  • server name\DB2

From my local machine I am trying to use Management Studio to connect to those three instances. I can connect without any problem to the server name instance but when trying to connect to the other two it fails with error:

"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"

I have already verified that all three instances allow remote connections, the port is the default one on all three, all of them have the TCP/IP protocol enabled and I can connect locally to all three by using the server name \ instance name format.

What else could possibly be preventing me from connecting to the two named instances?

Thank you.

7
sql-serversql-server-2008
Have you tried using the [server ip address] to the remote servers? If you can use the IP address and cannot use the server name, then it's likely the server's aren't publishing their name on the network. If you can't connect with the IP address, then there is likely a firewall somewhere that is blocking access. It would help if you describe more specifically what all three instances allow remote connections [on the default port] means. – James L.
As a first test try turning off firewalls both in the host and in the VM. It's possible that 1433 is available for the default instance but the ports for the named instances are not. The port can't possibly be the default port (1433) for all three instances. – Aaron Bertrand

7 Answers

9
votes

I had this exact same problem today, but think I finally found the solution to this. Once I implemented the steps below, it fixed the problem for me.

You need to start the SQL Server Browser service on the VM that hosts the SQL Server instances. You can find this by going to start-->run and typing "services" and then press return. Expand the services Window and look for the service called "SQL Server Browser." Mine was disabled and was not running. So, I highlighted the service by selecting it, and changed Startup type to Automatic (Delayed Start) and then pressed OK. Then right click the service again and click Start. Once the service starts you should be able to connect to all your instances again, assuming your firewall issues aren't presenting any problems (you can disable them temporarily on both the client and host to be sure).

The reason why this works is explained well in this article: https://www.mssqltips.com/sqlservertip/2661/how-to-connect-to-a-sql-server-named-instance/

Best of luck!

4
votes

Firewall(s) is the first thing to check... Port 1434 on UDP (I think) to the server should be open. Then if that is open you need to make sure that the actual database listen ports are open. I think if memory serves you can find these in th SQL config tool on the SQL instance itself.

Chances are you have port 1433 open for the default SQL instance, but possibly none of the others.

After that you'll need to do a more detailed check of what network config the SQL instances are set up for with the SQL config tool.

Oh yeah, you need to make sure that the SQL Browser service is running too... That's all from memory... Could be a bit scratchy.

3
votes

SQL uses another protocol: "named pipes", try enabling that one, as well as TCP/IP.

1
votes

Start the SQL Server Browser service on the hosts SQL Server instances were installed.

0
votes

Mine is an instance of SQL Server 2017. After enabling TCP/IP and ensuring that the server was listening on the proper ports, I was stumped. I finally realized that the SQL Server Browser service was not running. It has been installed as 'Disabled'. Using SQL Server Configuration Manager I went to SQL Server Browser Properties and changed to Start Mode to Automatic on the Service tab. Once I started the service I could connect successfully. Thank you Goody.

0
votes

Enabling TCP/IP for the Named Instance and starting SQL Browser Service worked. Ensure that default "Local System" is updated with apt Service Account.

--In 'thoughts'...

0
votes

None of previous answers worked for me as SQL Browser was started and the firewall allowed for UDP 1434.

Instead, I had to configure the firewall to allow the instance's sqlserv.exe:

  1. Go to the firewall advanced settings
  2. Inbound\New Rule
  3. Check "Program" and go next until you have to specify the path
  4. Point to "C:\Program Files\Microsoft SQL Server\MSSQLxx.yyyyy\MSSQL\Binn\sqlserv.exe" (xx = SQL Server version; yyyyy = instance name)