question

SaiVijaya avatar image
SaiVijaya asked

How to synchronize logins on AlwaysOn replicas

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.

sqlserver2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Use DBATools and Copy-DbaLogin

Use SSIS and the copy logins task

Use Microsoft’s sp_help_revlogin script and log the outputs to a file which is copied to other servers and then a job is issued to replay the script.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered

As @anthony.green mentioned, you can use one of the method mentioned by him.

But I would also focus on minimizing the need to synchronize logins cross the AOAG instances.

If you are using Windows authentication, then create a "Server Level AD Group" which grants only "Connect" to the instance and add make all your users members of that AD Group (most probably through hierarchy of other AD groups - see the image below).

Then for the server level create a few AD Groups which grants needed roles on the server (for admins etc) and make the needed people of those roles.

On the database level create users for Windows Users/Groups add them to appropriate roles and permissions on database level. Once the users/groups are member of the "Server Level AD Group", there is no need for additional windows logins to be created on the instance levels as the connect is granted through the "Server Level AD Group" and rest of the permissions are anyway handled on the DB Level.

If you are using SQL Logins, then enable partial containment on the Databases and define the users on the database level instead of instance level. With that all the users are synchronized cross the replicas and you do not need to care about scripting and synchronizing logins cross instances in the AOAG.

sqladgroups.png

With that approach you will have a clean SQL environment without a need of non-stop maintenance of users. The image above illustrates single instance scenario (thus the groups are named SG <Instance Name> Login. In your scenario it would not be an Instance Specific Login but a "Cluster Specific Login" which exists on all nodes of that cluster.


sqladgroups.png (241.2 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SaiVijaya avatar image
SaiVijaya answered

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 {

# secondary replica logins

$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


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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.