3

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

  • 3
    Have you checked the errorlog in Server2/Server3 for failed logins from said SQL logins? – Sean Gallardy Aug 20 at 15:32
  • 1
    This might similar case: dba.stackexchange.com/a/245389/185080. question defined nicely. up voted! – Shekar Kola Aug 20 at 19:14
  • @SeanGallardy checked the errorlog but there is no records related to failed logins – aleksey vitsko Aug 21 at 7:32
2

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.