Question :
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?
Answer :
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.
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.
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:tempScript.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:tempScript.txt' | Out-String
ForEach($SecondaryReplica in $SecondaryReplicas)
{
Invoke-Sqlcmd -Query $Query -ServerInstance $SecondaryReplica
Write-Host "Successfully copied login(s) to $SecondaryReplica"
}
Remove-Item C:tempScript.txt
}
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.