debugMinor
Availability Group Read Only Routing Not Working for SQL Logins
Viewed 0 times
groupreadsqlloginsworkingforavailabilityroutingnotonly
Problem
We have following config of Availability Group:
Recently we have setup Read Only Routing (ROR)
Read Only Routing URLs:
Read Only Routing Lists:
It worked fine when tested in SSMS using Windows Authentication Login (Domain\MyLogin) with Options >> Additional Connection Parameters tab -> ApplicationIntent=ReadOnly
While
Issues/Questions:
When I try to connect in SSMS to AG Listener using SQL Authentication Login (SQLAuthLogin), and
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
server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary
sql-listener.domain.com - availability group listener DNS nameRecently 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:1433Read Only Routing Lists:
server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3It 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 worksIssues/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,
Because of above setting, while connecting in SSMS using Windows Authentication, Read Only Routing worked right after
Adding
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 secondaryContext
StackExchange Database Administrators Q#245785, answer score: 2
Revisions (0)
No revisions yet.