12 thoughts on “Availability Groups: How to sync logins between replicas

  1. Hi , I was testing this, but it fails (error below) specifically on the code below as the password_hash on sql for user type S is NULL (what do you recommend?)

    Code:
    INSERT INTO @queue ([sql])
    SELECT N’
    CREATE LOGIN [‘+p.[name]+’] ‘+(CASE
    WHEN p.[type]=’S’
    THEN N’WITH PASSWORD=0x’+CONVERT(nvarchar(max), p.password_hash, 2)+N’ HASHED, CHECK_POLICY=OFF, SID=0x’+CONVERT(nvarchar(max), p.[sid], 2)+N’, ‘
    WHEN p.[type] IN (‘U’, ‘G’)
    THEN N’FROM WINDOWS WITH ‘ END)+
    N’DEFAULT_DATABASE=[‘+p.default_database_name+N’]’+
    ISNULL(N’, DEFAULT_LANGUAGE=’+p.default_language_name, N”)+N’;’
    FROM @primaryLogins AS p
    WHERE p.[sid] NOT IN (SELECT [sid] FROM master.sys.server_principals) AND
    p.[type] IN (‘U’, ‘G’, ‘S’);

    Error:
    Msg 515, Level 16, State 2, Procedure LMC_SyncLogins, Line 166
    Cannot insert the value NULL into column ‘sql’, table ‘@queue’; column does not allow nulls. INSERT fails.

      • Hi Daniel,
        Thanks for your prompt response , all the sql_logins have password_has NULL, this is SQL Server 2014 Enterprise, I can’t exclude them because I need to recreate them on the secondary AG, any advise ?

        Thanks again

        • My question was if you’re using contained database users (i.e. users that are local to the database and don’t have a login)? Or, maybe, it’s a permission issue that the security context that you’re using doesn’t have sufficient permissions?

          • Hi Daniel,
            You are right, it was the security context, once I gave sysadmin to the Linked server user then I see the password Hash. Now in order to restrict the permissions to this user, before assigning the sysadmin role, this user has securityadmin, serveradmin, db_owner over master system database, but it was not enough to see the password_hash, any advise on restricting security rights to be able to see the hash? besides the initial rights

            Thanks again

  2. The procedure does not transfer any database-level permissions, it only creates server-level logins – totally useless

    • It’s not supposed to. I’m not sure what you were expecting, but in an AG, the database-level permissions are synced by the AG. The procedure transfers *server-level* principals.

  3. Hi,

    How far are you with the other articles?
    “logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings”
    I could use all them currently :)

  4. Thanks for the tip. Just a note that there’s a typo in your code – you need to add an “=” after the WITH PASSWORD clause in your section titled “Here’s how to get the information you need from the primary replica:”.

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s