When using ApplicationIntent=ReadOnly to access a readable secondary via the Availability Group Listener, the login SIDs have to be the same between primary and secondary. The connection attempt generates an error saying the login does not exist. This happens because the restored database on the secondary has a defined user, but no associated login.
Two scenarios can cause this behavior:
1. Logins exist on primary but not on secondary
2. Logins exist on both primary and secondary but have mismatched SIDs
Both scenarios give same results.
Reproducing the problem:
Fixing the problem:
Either of the following methods can be employed to synchronize Login SIDs:
what about using Contained Users feature of SQL Server 2012?
you don't have to create the user in node Security->Login as it is kept in the database (similiar like user instance feature of SQL Server Express edition)
where is my previous comment ?
Contained databases will avoid the problem.
Hi Kevein, thanks for the post.
There is a simpler way to transfer logins from primary to secondary.
Shangzhou, thanks for the links. I had to respond with the official link from Microsoft of course. Hopefully anyone using other links and scripts will inspect them carefully to make sure they will do no harm.