How to synchronize logins on AlwaysOn replicas in sql 2017

Question

How to synchronize all logins on AlwaysOn replicas?

We have 1 primary and 3 secondary nodes in production cluster.
SQL version: SQL server 2017 Enterprise

Please help me with automated script.

Answer 1

Hi,

SQL Server Always On Availability replica is a database level synchronizes solution and LOGIN is an instance level entity. While the USER are synchronized the LOGIN will need one more step.

Please check this tutorial on the topic:
https://www.sqlshack.com/synchronize-logins-between-availability-replicas-in-sql-server-always-on-availability-group/

Here is another discussion on the topic with some more insights (here the original Q was about USER)
https://social.msdn.microsoft.com/Forums/en-US/f32c1ed2-f3f1-4bb4-9764-b4760a3f7422/sync-users-among-databases-in-always-on?forum=sqldisasterrecovery

Answer 2

Hi @Sai ,

The blogs that pituach offered are good start for this question.

SQL server AG is a database level HA. A user created in a database on primary database will be carried over to the secondary database as it resides within the database. But SQL logins are Server-level principal, they will not automatically sync.

Windows/AD logins and groups already come with a SID . This means that you can create a login for a given Windows account on any number of servers, and all those logins will get the same SID. Windows logins on primary and secondary replicas has same SID.

But SQL Server logins generate a new SID when they are created, so if you just create a login on two servers, they’ll end up with different SIDs.

Follow below steps to sync logins in AG.

1. Use below T-SQL generates a CREATE LOGIN script with password hash, SID for a login Cathy2 in primary replica.

   SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+  
       CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+  
       N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';'  
   FROM master.sys.server_principals AS sp  
   INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]  
   WHERE sp.name='Cathy2'  
   

   
2. Then copy this result script and execute it on the secondary . replicas to create the login with SID similar to the primary replica. 3... Use below T-SQL to check the logins and SID.

     select name, sid, type_desc from sys.server_principals  
   

If you want to get more detail information, please reading the blog Synchronize logins between Availability replicas in SQL Server Always On Availability Groups that pituach mentioned.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

I have seen the below error after executing the script:
We have 1 primary and 3 secondary in cluster with 4 Availability groups.
I have tested with one test listener by using the below script:

Script:
$AGLSN = 'AGUsingPowerShell'

$primaryReplica = Get-DbaAgReplica -SqlInstance $AGLSN | Where Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $AGLSN | Where Role -eq Secondary

$LoginsOnPrimary = (Get-DbaLogin -SqlInstance $primaryReplica.Name)

$secondaryReplicas | ForEach-Object {

$LoginsOnSecondary = (Get-DbaLogin -SqlInstance $_.Name)

$diff = $LoginsOnPrimary | Where-Object Name -notin ($LoginsOnSecondary.Name)
if($diff) {
    Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login $diff.Nane
}   

}

Error Message:
Copy-DbaLogin : Cannot process argument transformation on parameter 'Source'. Cannot convert value "System.Object[]" to type
"Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter". Error: "Failed to interpret input as Instance: System.Object[]"
At line:20 char:31

• Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Na ...
• ~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidData: (:) [Copy-DbaLogin], ParameterBindingArgumentTransformationException
• FullyQualifiedErrorId : ParameterArgumentTransformationError,Copy-DbaLogin

Answer 4

the above script worked in my development environment where it has only two nodes cluster and one availability group - one primary and one secondary.

But It is not working in my production environment where it has 4 nodes cluster with 4 availability groups- 1 primary and 3 secondary nodes

Answer 5

Yes, I changed Nane to Name and ran the script. but same error.

Copy-DbaLogin : Cannot process argument transformation on parameter 'Source'. Cannot convert value "System.Object[]" to type
"Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter". Error: "Failed to interpret input as Instance: System.Object[]"
At line:20 char:31

• Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Na ...
• ~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidData: (:) [Copy-DbaLogin], ParameterBindingArgumentTransformationException
• FullyQualifiedErrorId : ParameterArgumentTransformationError,Copy-DbaLogin