13

We have 2 servers in an AlwaysOn group.

While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.

Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).

How do I overcome this issue? Was I supposed to set up the user account in a special way?

2
  • I've started looking at this one recently too, I rolled my own and am currently describing it in my brand new blog (Mostly for my own benefit as I go forward and forget things but if its of help to others all the better) life-and-dev.blogspot.co.uk/2015/04/… this and the following couple will show AlwaysOn failover tracking and job/user synching – user64462 Apr 30 '15 at 13:15
  • You can use SSMS plugin from sqlskills Synchronize Availability Group Logins and Jobs – Kin Shah Apr 30 '15 at 13:25
15

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.

Something like the script from SQLSoldier, originally posted on the article Transferring Logins to a Database Mirror, should do the trick.

5
  • I'm looking forward to converting several of our production '05 databases to the new contained format once we switch to '12 in the next week or two. In the mean time, I'm using Idera's tools to copy logins, jobs, etc, to the secondary instance. – Hannah Vernon Dec 6 '12 at 2:40
  • Mark is correct, you'll need to either use contained databases or to manually copy the logins to all the instances in the cluster using the same SID on all the instances. – mrdenny Dec 6 '12 at 12:00
  • Keep in mind that I am asking about AlwaysOn, not mirroring. I tried the script, however the sys.servers table only contains the local server so I get the following error when I run the stored proceedure: Msg 7202, Level 11, State 2, Line 1 Could not find server 'otherserver' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. – John Dec 6 '12 at 14:52
  • I figured it out and posted a new answer appropriate for AlwaysOn. – John Dec 6 '12 at 15:46
  • @JohnHughes This script is appropriate for AlwaysOn. You will however, as indicated by the error message, need to create a linked server between the instances. – Mark Storey-Smith Dec 6 '12 at 15:56
7

You must either use a Contained Database, or you must recreate the users on the other server(s) with the same password hash and SID.

A script to do this is provided by Microsoft: How to transfer logins and passwords between instances of SQL Server

Mark's solution was partially right however his recommended solution was for Mirrored databases, as opposed to AlwaysOn which is what the questions asks for.

2
  • 2
    How is the solution for mirrored databases any different from the solution for Availability Groups (note: AlwaysOn is a marketing label for a bunch of technology, not a feature)? The title of a blog post doesn't change the fact that it's restoring missing logins - which, in and of itself, has nothing to do with whether you're using mirroring, availability groups, log shipping, manual backup/restore, etc. – Aaron Bertrand Jun 4 '13 at 3:30
  • Unfortunately, I cannot always use AD accounts. We're struggling with a way to compare the passwords. Are we not correct in assuming that if the passwords don't match, the failover between replica servers will fail? – user108386 Oct 18 '16 at 17:45
1

I am responding to the post after a long time but it may help someone else with the similar issue. PowerShell can be used to copy logins from Primary replica to secondary replicas. Details can be found here https://maq.guru/synchronizing-sql-server-logins-in-an-always-on-availability-group/.

Full disclosure: I own the above site.

The PowerShell script:

$Conn=New-Object System.Data.SqlClient.SQLConnection
$QueryTimeout = 120
$ConnectionTimeout = 30

###########################################################
# Execute Query function 
###########################################################
Function executequery($Query, $QueryTimeout, $ServerName)
{
    $Datatable = New-Object System.Data.DataTable
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$Database,$ConnectionTimeout
    $Conn.ConnectionString=$ConnectionString
    $Cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$Conn)
    $Cmd.CommandTimeout=$QueryTimeout

            do
                {   
                    $Conn.Open()
                    Start-Sleep -Seconds 2
                }while ($Conn.State -ne 'Open')

            $Reader = $cmd.ExecuteReader()
            $Datatable.Load($Reader)
            $Conn.Close()
            return $Datatable    
}


###########################################################
# Create spHexaDecimal Stored Procedure
###########################################################

Function CreatespHexaDecimal ($ServerName)
    {
    $Query='USE [master];
                GO
                SET ANSI_NULLS ON;
                GO
                SET QUOTED_IDENTIFIER ON;
                GO
                CREATE PROCEDURE [dbo].[spHexaDecimal]
                (
                    @BinValue VARBINARY(256)
                    , @HexValue VARCHAR(514) OUTPUT
                )
                AS

                DECLARE @CharValue VARCHAR(514)
                DECLARE @i INT
                DECLARE @Length INT
                DECLARE @HexString CHAR(16)

                SET @CharValue = ''0x''
                SET @i = 1
                SET @Length = DATALENGTH(@BinValue)
                SET @HexString = ''0123456789ABCDEF''

                WHILE (@i <= @Length)
                BEGIN

                    DECLARE @TempInt INT
                    DECLARE @FirstInt INT
                    DECLARE @SecondInt INT

                    SET @TempInt = CONVERT(INT, SUBSTRING(@BinValue, @i, 1))
                    SET @FirstInt = FLOOR(@TempInt/16)
                    SET @SecondInt = @TempInt - (@FirstInt * 16)
                    SET @CharValue = @CharValue 
                                        + SUBSTRING(@HexString, @FirstInt + 1, 1)
                                        + SUBSTRING(@HexString, @SecondInt + 1, 1)

                    SET @i = @i + 1

                END --WHILE (@i <= @Length)

                SET @HexValue = @CharValue'

                Invoke-Sqlcmd -Query $Query -ServerInstance $ServerName
    }


###########################################################
# CheckStroedProc 
###########################################################

Function CheckStoredProc ($Server)
{
    $Query= 'SELECT 1 AS ExistCheck
             FROM   sysobjects 
             WHERE  id = object_id(N''[dbo].[spHexaDecimal]'') 
                 AND OBJECTPROPERTY(id, N''IsProcedure'') = 1 '

    $Result=executequery $Query $QueryTimeout $Server
    $Exist=$Result | SELECT -ExpandProperty ExistCheck
    IF ($Exist -ne 1)
        {
            CreatespHexaDecimal -ServerName $Server
        }
}

###########################################################
# Get Login Script
###########################################################

Function Get-Script ($Server)
{

$Query='DECLARE @TempTable TABLE
(Script NVARCHAR(MAX))
DECLARE @Login NVARCHAR (MAX)
DECLARE CURLOGIN CURSOR FOR
SELECT name 
FROM sys.server_principals
WHERE CONVERT(VARCHAR(24),create_date,103) = CONVERT(VARCHAR(24),GETDATE(),103)
    OR CONVERT(VARCHAR(24),modify_date,103) = CONVERT(VARCHAR(24),GETDATE(),103)

OPEN CURLOGIN
    FETCH NEXT FROM CURLOGIN INTO @Login

WHILE @@FETCH_STATUS = 0
BEGIN
    SET NOCOUNT ON
    DECLARE @Script NVARCHAR (MAX)
    DECLARE @LoginName VARCHAR(500)= @Login
    DECLARE @LoginSID VARBINARY(85)
    DECLARE @SID_String VARCHAR(514)
    DECLARE @LoginPWD VARBINARY(256)
    DECLARE @PWD_String VARCHAR(514)
    DECLARE @LoginType CHAR(1)
    DECLARE @is_disabled BIT
    DECLARE @default_database_name SYSNAME
    DECLARE @default_language_name SYSNAME
    DECLARE @is_policy_checked BIT
    DECLARE @is_expiration_checked BIT
    DECLARE @createdDateTime DATETIME



    SELECT @LoginSID = P.[sid]
        , @LoginType = P.[type]
        , @is_disabled = P.is_disabled 
        , @default_database_name = P.default_database_name 
        , @default_language_name = P.default_language_name 
        , @createdDateTime = P.create_date 
    FROM sys.server_principals P
    WHERE P.name = @LoginName

    /** Some Output **/
    SET @Script = ''''




    --If the login is a SQL Login, then do a lot of stuff...
    IF @LoginType = ''S''
    BEGIN

        SET @LoginPWD = CAST(LOGINPROPERTY(@LoginName, ''PasswordHash'') AS VARBINARY(256))

        EXEC spHexaDecimal @LoginPWD, @PWD_String OUT   
        EXEC spHexaDecimal @LoginSID, @SID_String OUT

        SELECT @is_policy_checked = S.is_policy_checked
            , @is_expiration_checked = S.is_expiration_checked
        FROM sys.sql_logins S

        /** Create Script **/
        SET @Script = @Script + CHAR(13) + CHAR(13)
                        + ''IF EXISTS (SELECT name FROM sys.server_principals WHERE name= ''''''+ @LoginName + '''''') '' 
                        + CHAR(13) + '' BEGIN ''
                        + CHAR(13) + CHAR(9) + '' ALTER LOGIN '' + QUOTENAME(@LoginName)
                        + CHAR(13) + CHAR(9) + ''WITH PASSWORD = '' + @PWD_String + '' HASHED''
                        + CHAR(13) + CHAR(9) + '', DEFAULT_DATABASE = ['' + @default_database_name + '']''
                        + CHAR(13) + CHAR(9) + '', DEFAULT_LANGUAGE = ['' + @default_language_name + '']''
                        + CHAR(13) + CHAR(9) + '', CHECK_POLICY '' + CASE WHEN @is_policy_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
                        + CHAR(13) + CHAR(9) + '', CHECK_EXPIRATION '' + CASE WHEN @is_expiration_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
                        + CHAR(13) + '' END ''
                        + CHAR(13) + ''ELSE''
                        + CHAR(13) + '' BEGIN ''
                        + CHAR(13) + CHAR(9) + '' CREATE LOGIN '' + QUOTENAME(@LoginName)
                        + CHAR(13) + CHAR(9) + ''WITH PASSWORD = '' + @PWD_String + '' HASHED''
                        + CHAR(13) + CHAR(9) + '', SID = '' + @SID_String
                        + CHAR(13) + CHAR(9) + '', DEFAULT_DATABASE = ['' + @default_database_name + '']''
                        + CHAR(13) + CHAR(9) + '', DEFAULT_LANGUAGE = ['' + @default_language_name + '']''
                        + CHAR(13) + CHAR(9) + '', CHECK_POLICY '' + CASE WHEN @is_policy_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
                        + CHAR(13) + CHAR(9) + '', CHECK_EXPIRATION '' + CASE WHEN @is_expiration_checked = 0 THEN ''=OFF'' ELSE ''=ON'' END
                        + CHAR(13) + '' END ''

        SET @Script = @Script + CHAR(13) + CHAR(13)
                        + '' ALTER LOGIN ['' + @LoginName + '']''
                        + CHAR(13) + CHAR(9) + ''WITH DEFAULT_DATABASE = ['' + @default_database_name + '']''
                        + CHAR(13) + CHAR(9) + '', DEFAULT_LANGUAGE = ['' + @default_language_name + '']''

    END
    ELSE
    BEGIN

        --The login is a NT login (or group).
        SET @Script = @Script + CHAR(13) + CHAR(13)
                        + ''IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name= ''''''+ @LoginName + '''''') '' 
                        + CHAR(13) + '' BEGIN ''
                        + CHAR(13) + CHAR(9) + '' CREATE LOGIN '' + QUOTENAME(@LoginName) + '' FROM WINDOWS''
                        + CHAR(13) + CHAR(9) + ''WITH DEFAULT_DATABASE = ['' + @default_database_name + '']''
                        + CHAR(13) + '' END ''
    END

    /******************************************************************************************/
    --This section deals with the Server Roles that belong to that login...
    /******************************************************************************************/

    DECLARE @ServerRoles TABLE
        (
        ServerRole SYSNAME
        , MemberName SYSNAME
        , MemberSID VARBINARY(85)
        )

    INSERT INTO @ServerRoles EXEC sp_helpsrvrolemember

    --Remove all Roles
    SET @Script = @Script + CHAR(13)
    SET @Script = @Script 
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''sysadmin''''''
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''securityadmin''''''
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''serveradmin'''''' 
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''setupadmin'''''' 
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''processadmin''''''
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''diskadmin'''''' 
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''dbcreator'''''' 
                            + CHAR(13) + ''EXEC sp_dropsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + ''''''bulkadmin'''''' 

    /** Output to script... **/
    --SET @Script = @Script + CHAR(13) + CHAR(13)

    --Test if there are any server roles for this login...
    IF EXISTS(SELECT 1 FROM @ServerRoles WHERE MemberName = @LoginName)
    BEGIN

        SET @Script = @Script + CHAR(13)

        DECLARE @ServerRole SYSNAME
        DECLARE curRoles CURSOR LOCAL STATIC FORWARD_ONLY

        FOR SELECT ServerRole 
            FROM @ServerRoles
            WHERE MemberName = @LoginName

        OPEN curRoles

        FETCH NEXT FROM curRoles
        INTO @ServerRole

        WHILE @@FETCH_STATUS = 0
        BEGIN

            /** Output to Script **/
            SET @Script = @Script 
                            + CHAR(13) + ''EXEC sp_addsrvrolemember '' + QUOTENAME(@LoginName, '''''''') + '', '' + QUOTENAME(@ServerRole, '''''''')

            FETCH NEXT FROM curRoles
            INTO @ServerRole

        END

        --Cleanup.
        CLOSE curRoles
        DEALLOCATE curRoles

    END
    INSERT INTO @TempTable
    VALUES(@Script)

    FETCH NEXT FROM CURLOGIN INTO @Login
END
CLOSE CURLOGIN;
DEALLOCATE CURLOGIN;
SELECT Script FROM @TempTable'

$Result=executequery $Query $QueryTimeout $Server

If($Result -eq $null)
    {
        break
    }
Else
    {
        [Void][System.IO.Directory]::CreateDirectory("C:\temp")
        $Path = "C:\temp"
        $Acl = (Get-Item $Path).GetAccessControl('Access')
        $Username = Get-WmiObject win32_service | Where name -EQ 'SQLSERVERAGENT' | Select -ExpandProperty StartName
        $Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($Username, 'Full', 'ContainerInherit,ObjectInherit', 'None', 'Allow')
        $Acl.SetAccessRule($Ar)
        Set-Acl -path $Path -AclObject $Acl
        $Result | select -ExpandProperty Script | Out-File C:\temp\Script.txt
    }
}


###########################################################
# SCRIPT BODY 
###########################################################

$Query= "SELECT ISNULL(SERVERPROPERTY ('InstanceName'), 'DEFAULT') InstanceName 
            , name AGName
            , replica_server_name Replica
            , role_desc 
            FROM sys.dm_hadr_availability_replica_states hars 
            INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id 
            INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id
            WHERE role_desc = 'PRIMARY'
            ORDER BY role_desc asc"
Write-Host "Is this Primary Replica?"
$Result=executequery $Query $QueryTimeout $PrimaryReplica
If ($Result -eq $null)
    {
        Write-Host "No, it's not."
        break
    }
Else
    {
        Write-Host "Yes, it is."
        $PrimaryReplica= $Result | select -ExpandProperty Replica
        Write-Host "Check for prerequisite, if not present deploy it."
        CheckStoredProc -Server $PrimaryReplica
        Write-Host "Get script for new/modifies login(s)."
        Get-Script -Server $PrimaryReplica

        $Query= "SELECT ISNULL(SERVERPROPERTY ('InstanceName'), 'DEFAULT') InstanceName 
                    , name AGName
                    , replica_server_name Replica
                    , role_desc 
                    FROM sys.dm_hadr_availability_replica_states hars 
                    INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id 
                    INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id
                    WHERE role_desc = 'SECONDARY'
                    ORDER BY role_desc asc"

        $Result=executequery $Query $QueryTimeout $PrimaryReplica
        $SecondaryReplicas= $Result | select -ExpandProperty Replica
        $Query= Get-Content -Path 'C:\temp\Script.txt' | Out-String
        ForEach($SecondaryReplica in $SecondaryReplicas)
            {
                Invoke-Sqlcmd -Query $Query -ServerInstance $SecondaryReplica
                Write-Host "Successfully copied login(s) to $SecondaryReplica"
            }
        Remove-Item C:\temp\Script.txt
    }
5
  • 2
    Thanks Muhammad, can you include some of the link's content? If the link dies, your question will become a lot less useful otherwise! – LowlyDBA - John M Apr 19 '18 at 17:54
  • @LowlyDBA, I have tried to put the script here but unfortunately its too long :( The link will not die as I own this website. – Muhammad Apr 21 '18 at 9:46
  • The script isn't very long. I'll add it for you. And in that case, you need to disclose your affiliation when posting your own links. – LowlyDBA - John M Apr 22 '18 at 14:21
  • Apologies @LowlyDBA, I was not aware about the disclosure. I have tried to put the script here in the comments section but it is limited to certain number of characters. I have added the complete script. Thanks – Muhammad Apr 23 '18 at 19:46
  • It's pretty shameful that server-level sync is not part of AG. Your answer gets an upvote for the article calling MS out on it. – youcantryreachingme Sep 16 '19 at 6:18
-1

You must use Windows Domain logins and create them in every instance, As the SID is manage by active directory you will be able to access in all replicas members of availability group if the login exist in the primary replica. another option you must use a certificate.

Not the answer you're looking for? Browse other questions tagged or ask your own question.