HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

Availability Group Read Only Routing Not Working for SQL Logins

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
groupreadsqlloginsworkingforavailabilityroutingnotonly

Problem

We have following config of Availability Group:

server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary

sql-listener.domain.com - availability group listener DNS name


Recently we have setup Read Only Routing (ROR)

Read Only Routing URLs:

server1 - TCP://server1.domain.com:1433
server2 - TCP://server2.domain.com:1433
server3 - TCP://server3.domain.com:1433


Read Only Routing Lists:

server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3


It worked fine when tested in SSMS using Windows Authentication Login (Domain\MyLogin) with Options >> Additional Connection Parameters tab -> ApplicationIntent=ReadOnly

While server1 is primary, I connect in SSMS to AG Listener (sql-listener.domain.com) using above parameter and run select @@servername, and it shows server 2, which means ROR works

Issues/Questions:

When I try to connect in SSMS to AG Listener using SQL Authentication Login (SQLAuthLogin), and ApplicationIntent=ReadOnly parameter, and then run select @@servername, it shows me server1

>>> So in my case, ROR does not work with SQL Authentication Login
is there any way to fix this ?


add: double checked SQLAuthLogin's SID on server1 and server2 - SID is the same, SQLAuthLogin can login to both servers and query MyDatabase just fine

Solution

The issue was not in SQL or Windows Authentication methods, but in default_database setting

Found that for my Windows Authentication Login, default_database = [MyDatabase] (which participates in availability group), while my SQL Authentication Login has default_database = [master]

Because of above setting, while connecting in SSMS using Windows Authentication, Read Only Routing worked right after ApplicationIntent=ReadOnly was specified. For the SQL Authentication Login, not only ApplicationIntent=ReadOnly needs to be specified, but also it should contain Initial Catalog=[MyDatabase]

Adding Initial Catalog=[MyDatabase] fixed the issue, SQL Authentication Login could connect and was directed to Read-Only secondary

Context

StackExchange Database Administrators Q#245785, answer score: 2

Revisions (0)

No revisions yet.