SQL SERVER – Read Only Routing Error: Client Unable to Establish Connection Beca...

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

WORKAROUND/SOLUTION

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

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 )