5 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

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