SQL SERVER – Read Only Routing Error: Client Unable to Establish Connection Because an Error was Encountered During Handshakes Before Login

I have helped many clients in deploying Always On Availability Group. Based on their requirement they keep using additional features provided by availability groups. This time they wanted to use always-on availability group read-only routing feature. Their goal was to offload read-only workload to the secondary replica. This was failing with error: Client unable to establish a connection because an error was encountered during handshakes before login.

I informed them that while making a connection from the application, they need to make sure of three things.

  1. The routing URL is setup correctly in SQL Server.
  2. The routing list is setup correctly in SQL Server.
  3. Connect to the listener in the connection string.
  4. Provide default database name in the connection string.
  5. Provide application intent parameter in the connection string.

As per them, the above things were checked already so they wanted me to look into it and fix it.

Here is the error message which they were getting while connecting to the listener using read-only intent.

Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server: TCP Provider: An existing connection was forcibly closed by the remote host.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server: Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server: Client unable to establish connection due to prelogin failure.

Here is the image

SQL SERVER - Read Only Routing Error: Client Unable to Establish Connection Because an Error was Encountered During Handshakes Before Login ao-ror-err-01

WORKAROUND/SOLUTION

Without wasting a lot of time, I asked them to show me the routing via SSMS.

SQL SERVER - Read Only Routing Error: Client Unable to Establish Connection Because an Error was Encountered During Handshakes Before Login ao-ror-err-02

This UI the Availability Group properties window. If you are using a lower version of SSMS then you might want to use the catalog views to query them. The latest SSMS can be downloaded free from below link

Download SQL Server Management Studio (SSMS).

As we can see, the routing URL is setup incorrectly. The port in the routing URL should be the port on which SQL connections are made to the instance. Typically, for default instance, it is 1433.

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

As soon as the URL was changed, the read-only routing started working like a charm.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

AlwaysOn, Computer Network, SQL High Availability, SQL Server, SQL Server Management Studio, SSMS
Previous Post
SQL SERVER – Stretch Database – Free Trial Subscriptions Can Provision Basic, Standard S0 through S3 Databases, up to 100 eDTU Basic or Standard Elastic Pools and DW100 Through DW400 Data Warehouses
Next Post
SQL SERVER – Script Level Upgrade for Database ‘master’ Failed Because Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 8649, State 1, Severity 17

Related Posts

2 Comments. Leave new

  • I got same situation or error message and the issue in my particular case was that the number of connections was set to a maximum of 500. similar issue happened when this limit is reached therefore you can either reboot the SQL instance to get rid of idle connections. But if this happens frequently change the number of connections to unlimited (Value 0).
    Alemayehu G. Desta

    Reply
  • Thanks a lot, this help me resolved my prolem :D

    Reply

Leave a Reply