1

I have researched a lot on this issue but can't seem to find a solution for it. I have a named instance created on a server but I can't connect using the Servername\InstanceName.

I am using SQL Server 2016 Enterprise Edition.

Here is the setup:

Servername: QA1

Has default instanced installed and also named instance: BUInt0

The SQL Browser service is started and I checked and UDP 1434 is allowed.

When I tried to connect from a remote server, I cannot reach it using QA1\BUInt0. I get the following 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

But it works if it use IPAddress\BUInt0.

I had my system engineers check DNS but they don't see any issues.

Anyone have any ideas what is going on and why I can't connect using Servername\NamedInstance format?

Improve this question
3
  • Can you perform connection to default instance with QA1? can you assign static port to BUInt0 and try to connect QA1,portNumber? Sounds like firewall configuration issue
    – SergeyA
    Commented Nov 30, 2023 at 19:53
  • I can connect to default instance with SERVERNAME, no issue. I can connect to the NamedInstance using ServerName, PortNumber. I just can't connect using ServerName/NamedInstance.
    – kaitlyn
    Commented Nov 30, 2023 at 20:05
  • We'd need to see the list of IPs associated with the server (nslookup output is fine) and then the IP address you used to connect successfully. We'll also need the output of netstat -ano -p tcp | findstr {PID} where PID is the PID of SQL Server instance BUInt0. Finally, a copy of the SQL instance IP config registry would be nice but a screenshot of the TCP/IP config for the instance would suffice. Given everything, though, it might also need a network trace.
    – Sean Gallardy
    Commented Dec 1, 2023 at 13:31

2 Answers 2

Reset to default
0

I ended up changing the named instance to use a static port instead of a dynamic port. Then I changed the AGL to use the static port and I am now able to connect using the ServerName/NamedInstance format. I also had the static port opened up on the FW as well.

I must be missing a step or two in regards to using dynamic ports instead of static ports for the Named Instance but just couldn't figure it out so just went to static ports. I had UDP 1434 opened up but that didn't help so wasn't sure what was missing.

Improve this answer
1
  • You were missing an ACL / FW rule for dynamic TCP port (that was set on instance startup). Browser only tells the client where he should send next package to connect to certain instance. Of course, after instance restart, you would end up with another port, so another FW rule would have to be added / modified.
    – Marcin S.
    Commented Dec 4, 2023 at 22:33
0

SQL Browser does not redirect traffic from UDP 1434 port to SQL Server instance. It just sends information about TCP/IP port or a named part of instance client is trying to connect to, as an answer to client.

From Microsoft Learn page, How SQL Server Browser Works:

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server 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 returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

When SQL Server 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. SQL Server Browser Service does not provide port resolution for default instances.

That means that you were missing another ACL / firewall rule that opens traffic to TCP/IP port that named instance started on. And, as it is dynamic port, this will change upon instance restart.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.