Using SQL Logins with “AlwaysOn” Availability Groups

I had some fun today configuring a SQL 2012 “AlwaysOn” Availability Group working with a database that was configured to use SQL Logins.  I am working with the vendor to see if we can use Integrated Authentication instead, but in the meantime I managed to get failover functional.

The problem I was experiencing was that on failover, the database users defined in the database lost their mappings to the SQL Login on the server.  I had created SQL Logins on both nodes of the Availability Group with identical usernames and passwords, but the mapping still failed.  Turns out this was happening because SQL Logins (like AD and NT accounts) have SIDs, and these SIDs are used to map database users to logins.  To correct the problem, I needed to create SQL Logins with identical SIDs on the two servers.

Procedure:

  1. Create SQL Logins on one node of the Availability Group and perform mappings.
  2. Lookup the SIDs of the users using the following query:
    SELECT SUSER_SID (‘[SqlLogin]‘)
  3. Script out creation of the matching logins:
    USE [master]
    GO
    CREATE LOGIN [SqlLogin] WITH PASSWORD=N’[Password]‘, SID=[HexidecimalSID], DEFAULT_DATABASE=[myDatabase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
  4. Failover the Availability Group and verify that user mappings are working as planned.  Verify that passwords are working, too.

Another useful tidbit here is a script to re-map SQL database users to local SQL logins:

USE myDatabase
GO
sp_change_users_login @Action=’update_one’, @UserNamePattern=’databaseUserName’, @LoginName=’SqlLoginName’;
GO

Really I am surprised that I did not run into this problem with our SQL 2008 mirrored databases.  The problem should have been present there as well.

Comments are closed.