Synchronizing SQL Server Logins in an Always On Availability Group
Always On Availability Group is not a new feature, released in SQL Server 2012. Always On Availability Group “claims” to provide a high availability and disaster recovery solution. That is not 100% correct, as it does not provide HADR to many system objects e.g. SQL Agent jobs, SQL Server logins, Linked Servers etc.
If you forget to create SQL Server logins or update passwords/permissions on secondary replicas and a failover occurs users/apps will not have access to the “new” primary replica.
One of our clients had the same issue. I have implemented a solution using the power of PowerShell to resolve it.
In this solution, a SQL Agent job executes a PowerShell script on the Primary replica that takes care of new logins and password/permission change. Although, the job exists on all nodes but it is smart enough to know which node it should execute on.
As soon as the new login creation or password/permission change event occurs, the script replicates it on all secondary nodes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
########################################################### # 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 } |
You can download complete script here
Limitations: The script will not copy existing logins unless you make a change to them e.g. password change, grant/revoke privileges etc.
Prerequisites:
“sp_hexadecimal” should be deployed on all cluster nodes.
Remember to remove/comment out “Write-Host” statements if running as SQL Agent Job
Script will not do anything if you did not modify or created a login
References: TSQL script based upon “sp_hexadecimal” by Microsoft and TSQL to get the “create login” script based upon Claire Hsu
Leave a Reply
7 Comments on "Synchronizing SQL Server Logins in an Always On Availability Group"
Hello Mr. Qadeer – The resolution you’ve provided meets the exact requirement I have. Thanks for your effort putting this online which helps others. As I’m new to PS and trying to understand what “Script Body” represents? Is this a Agent Job PS Script that calls your AutoClone.PS script? Please update. Thanks again in advance!
Hi Mubeen,
You can use it either by putting the complete script in the agent job or save the file “AutoClone.ps1” and run it from the job. Hope this helps.
Hi Qadeer, This is FANTASTIC!!!! Thank you so much for the wonderful blog and the scripts. Is this script copy the logins and user from Primary server to replica server or do we need to manually cocopy and run the outputs.
I do have one more question though, can you please help me understand how you are able to sync the jobs between primary and relplica server? Really interested in learning that please.
Thanks Sandy.
You can copy jobs across but not the maintenance plans. I don’t have a finished script. I will try to publish it soon.
This is really good Muhammad.
A problem I have is that in order to share resources the availability groups (many) are shared between replicas so I have primary and secondary replicas on each instance. because of this I cannot copy ALL logins from one node to another. They have to be selective.
Does your script discriminate which is a primary replica amongst others?
Thanks Greg.
I have not tested it against a criss-cross configuration, as yours. I will check it and get back to you.