Share via

Can't connect using named instance from 1 client

PeterVR 26 Reputation points
2022-02-01T15:18:12.963+00:00

I have an Availability Group on server1 and (unreadable) server2 with AG listener sqllistener, and an SQL instance sqlinstance on port 1433. The SQL Server Browser service is running.
I have 2 identically configured clients in another subnet, client1 and client2.

On client1 I can connect to the SQL instance using:
Invoke-Sqlcmd -ServerInstance "sqllistener\sqlinstance" -Query "SELECT name FROM master.sys.databases"

On client2 the exact same command 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

On client2 I can connect using:
Invoke-Sqlcmd -ServerInstance "sqllistener" -Query "SELECT name FROM master.sys.databases"

The same behaviour occurs when trying to connect in SSMS, using the same strings.

There are firewall rules allowing tcp:1433 and udp:1434 traffic from both clients. Firewall logging is on, no packets are dropped.

How can I get client2 to connect using the full sqllistener\sqlinstance connection string?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2022-02-01T19:32:13.173+00:00

    That is a generic error and does not help diagnose the actual problem in any way.

    I suggest you start here:
    https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15#testTCPIP

  2. YufeiShao-msft 7,091 Reputation points
    2022-02-02T02:59:55.223+00:00

    Hi @PeterVR ,

    (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    Get this error message may be the client stack could not receive SSRP response UDP packet from SQL Browser, please check:

    1) Make sure your server name is correct, e.g., no typo on the name.
    2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \ to . If you are not sure about your application, please try both ServerInstance and Server\Instance in your connection string ]
    3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
    4) Make sure SQL Browser service is running on the server.
    5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

    check out this doc:https://techcommunity.microsoft.com/t5/sql-server-blog/sql-network-interfaces-error-26-error-locating-server-instance/ba-p/383277

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.