Always On Availability Group: Connection to readable secondary fails when login SIDs are different or missing

  • Article

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:

  1. Create several logins and users on a standalone SQL instance.
  2. Create a simple RS report in VS2010 while connected as one of the new users
  3. Click Preview and the report runs fine.
  4. Create an Availability Group with a readable secondary, restore, and make sure everything is active and synchronized.
  5. Create a Listener.
  6. In the Visual Studio report, change the connection string to connet to the listener and add ApplicationIntent=Readonly.
  7. Click Preview. A login error is generated on the screen. And an error gets generated in the SQL Server log.
  8. Add the logins and users to secondary server, but in a different order than on the primary.
  9. Click Preview on the report. Again, the login error is generated.
  10. Sync the SIDs using either method in the links below.
  11. Click Preview. The report is generated immediately.

Fixing the problem:

Either of the following methods can be employed to synchronize Login SIDs:

  1. Create the logins on the secondary with the SID=sid option in the CREATE LOGIN command. In order to do this one, you will have to get the SIDs and logins from the primary before you create them on the secondary. Or you can get them from the user record in the secondary database.
  2. Follow the instructions and use the script from KB 918992. Even though it says it is used to transfer logins from SQL Server 2005 to SQL Server 2008, it can also be used to create a script from your primary server to be run on the secondary to create the same logins with the same SIDs.