Cannot connect remotely to a SQL Server named instance

Question

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.

Answer 1

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.

Answer 2

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!