CODEWITHSUNDEEP
sql-serversql-server-2008
Sergio Romero
Sergio Romero

Reputation: 6607

Cannot connect remotely to a SQL Server named instance

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

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.

Upvotes: 13

Views: 25141

Answers (7)

StatsStudent
StatsStudent

Reputation: 1604

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!

Upvotes: 11

vladitu
vladitu

Reputation: 1

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)

Upvotes: 0

LonelyRogue
LonelyRogue

Reputation: 466

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'...

Upvotes: 0

Nanda Nandula
Nanda Nandula

Reputation: 11

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

Upvotes: 1

Steve_Malcolm
Steve_Malcolm

Reputation: 53

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.

Upvotes: 0

Goody
Goody

Reputation: 416

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.

Upvotes: 7

Emilio Mendez
Emilio Mendez

Reputation: 31

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

Upvotes: 3

Related Questions