I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terabyte databases.
Welcome to a quick post that should help you operate your SQL Server environment more consistently and reduce manual, repetitive work.
The Problem
When you are running SQL Server Availability Groups, one of the most cumbersome tasks is to ensure that all logins are synchronised between all replicas. While not exactly rocket science, it is something that quickly means a lot of work if you are managing more than one or two Availability Groups.
Wouldn’t it be nice to have a script that is flexible enough to
- be called by only specifying the Availability Group Listener
- detect all replicas and their roles automatically
- connect to the primary, read all SQL logins and apply them to EVERY secondary automatically?
Well, dbatools to the rescue again.
The solution
With dbatools, such a routine takes only a few lines of code.
The below script connects to the Availability Group Listener, queries it to get the current primary replica, as well as every secondary replica and then synchronizes all logins to each secondary.
In the template code, no changes are actually written due to the -WhatIf switch, so that you can safely test it to see what changes would be committed.
<#
Script : SyncLoginsToReplica.ps1
Author : Andreas Schubert (http://www.linkedin.com/in/schubertandreas)
Purpose: Sync logins between all replicas in an Availability Group automatically.
--------------------------------------------------------------------------------------------
The script will connect to the listener name of the Availability Group
and read all replica instances to determine the current primary replica and all secondaries.
It will then connect directly to the current primary, query all Logins and create them on each
secondary.
Attention:
The script is provided so that no action is actually executed against the secondaries (switch -WhatIf).
Change that line according to your logic, you might want to exclude other logins or decide to not drop
any existing ones.
--------------------------------------------------------------------------------------------
Usage: Save the script in your file system, change the name of the AG Listener (AGListenerName in this template)
and schedule it to run at your prefered schedule. I usually sync logins once per hour, although
on more volatile environments it may run as often as every minute
#>
# define the AG name
$AvailabilityGroupName = 'AGListenerName'
# internal variables
$ClientName = 'AG Login Sync helper'
$primaryInstance = $null
$secondaryInstances = @{}
try {
# connect to the AG listener, get the name of the primary and all secondaries
$replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName
$primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name
$secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name
# create a connection object to the primary
$primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName
# loop through each secondary replica and sync the logins
$secondaryInstances | ForEach-Object {
$secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins -WhatIf
}
}
catch {
$msg = $_.Exception.Message
Write-Error "Error while syncing logins for Availability Group '$($AvailabilityGroupName): $msg'"
}
To make tools reusable, you could easily turn this script into a function by adding the 2 variables as parameters. Then you could call it from any other script like
SyncLoginsToReplica.ps1 -AvailabilityGroupName YourAGListenerName -ClientName "Client"
For simplicity, I created this as a standalone script though.
I hope you find this post useful. For questions and remarks please feel free to message me!
Great one
Thanks! Just what I was looking for!!
Does this copy the passwords for the local sql logins as well, or will they still need to be “reset” once the sync is performed?
It does not detect password changes, but a login copied from source to destination will have the correct password
Thanks, this PS script is very helpful.
Incidentally, it worked flawlessly on two separate listeners, but failed on a third one with the following error (ag_aq01 is the listener name):
Error while syncing logins for Availability Group ‘ag_aq01: 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[]”‘
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException
The only difference is that this server contains more than one AG (the second AG has no listener), could that be the problem?
Yes, that is the reason. You could work around that by adding a for each around the primary collection or add a parameter for the AG Name and pass that to the get-dbaagprimary.
I like this script. You have a -whatif in the copy-dbalogin command. won’t that keep it from actually copying the logins?
Lee, yes. I did that on purpose so that people can try it without accidentally changing their settings
perfect. just wanted to make sure i was understanding the code correctly.
Would this work if login is removed from primary?
No, the copy-dbalogin command does not check if the login exists on a secondary only. but that would be not too hard to add to the script logic. i am currently on the road and can’t do it right now,
but basically you need to do a get-dbalogin for the secondary servers, enumerate on each of the logins from the returned list and if it doesn’t find a login on the primary (by doing a get-dbalogin -SQLinstance $primary -login $NameFromSecondary) then drop the login on the secondary by piping it to remove-dbalogin -sQLinstance $primary
How to skip executing the script if I am executing it on secondary replica?
that’s easy. Create a job step as the first step that checks the current status of the replica by doing master.sys.fn_hadr_is_primary_replica(‘yourDB’). If the status is NULL or 0 then the database is either not part of an AG or not the primary replica. In that case, raise an error and finish the job at step 1. If it is the primary replica, execute the next step (syncing the logins).
Or you can do it in PowerShell as well by checking $primaryInstance against $env:computername to see if you are currently running on a primary replica or not.
Great blog post ! I guess Sync-DbaAvailabilityGroup `https://docs.dbatools.io/#Sync-DbaAvailabilityGroup` will cover a bigger umbrella – logins, login permissions, dbmail, proxy, linkedservers, etc
Thanks Kin,
sync-dbaAvailabilityGroup is really great for an initial sync (when you create the AG and need to set up all replicas). Further down the road I don’t want configuration changes to automatically be applied to any replicas automatically. Logins change frequently and need to be kept in sync, but sp_configure options and Credentials, DatabaseMail, LinkedServers etc. are deployed following certain processes (hopefully), so that I tend to exclude those from automated jobs.
Any chance you might update the script to handle instances with multiple AGs?
Michael,
I don’t think that will happen soon. The reason is that I’d like to test code thoroughly before I put it up for public. I don’t do multi-AG instances and so I won’t be able to test it.
Hi Andreas, any suggestion about this error? I’m struggling with this one.
Login – WindowsUser mydomain\myuser Successful
Exception calling “EnumServerPermissions” with “1” argument(s): “There is
already an open DataReader associated with this Command which must be closed
first.”
At line:114615 char:9
+ $perms = $SourceServer.EnumServerPermissions($userName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
The following exception occurred while trying to enumerate the collection:
“There is already an open DataReader associated with this Command which must
be closed first.”.
At line:114664 char:9
+ $loginCredentials = $SourceServer.Credentials | Where-Object …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemExceptio
n
+ FullyQualifiedErrorId : ExceptionInGetEnumerator
Exception calling “EnumDatabaseMappings” with “0” argument(s): “Enumerate
database mappings failed for Login ‘mydomain\myuser’. ”
At line:114772 char:21
+ foreach ($db in $SourceLogin.EnumDatabaseMappings()) {
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
Hi Pasquale,
I have a hunch that you may be having many Logins on your primary replica with many explicit permissions. I’ve seen that once on another project that had nothing to do with dbatools and what I did back then was changing my code to use a longer timeout when querying the permissions – that did the trick for me. But it won’t be as easy here, since the exception is raised in one of the lower level functions of sync-dbalogin. Can you raise a bug report on dbatools?
My parameterized and support for multiple AGs on a single listener (requires listener and AG name to be provided) and still contains the -whatif:
param (
# define the AG name
[Parameter(Mandatory=$true)][string]$AvailabilityGroupName,
[Parameter(Mandatory=$true)][string]$AvailabilityGroupListener,
[string]$ClientName = ‘AG Login Sync helper’
)
# internal variables
#$ClientName = ‘AG Login Sync helper’
$primaryInstance = $null
$secondaryInstances = @{}
try {
# connect to the AG listener, get the name of the primary and all secondaries
$replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupListener -AvailabilityGroup $AvailabilityGroupName
$primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name
$secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name
# create a connection object to the primary
$primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName
# loop through each secondary replica and sync the logins
$secondaryInstances | ForEach-Object {
$secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins -WhatIf
}
}
catch {
$msg = $_.Exception.Message
Write-Error “Error while syncing logins for Availability Group ‘$($AvailabilityGroupName): $msg'”
}
I have just tried this solution and it works great, however, when the login fails to copy over it doesn’t give me a reason for failure? Is there any way that i can get that information so that I can fix it?
Hi Andreas, nice script. Works well after a little adjustment. I had to put the name of the listener in the code as the sqlinstance. If I did not put in the name of the sqlinstance, I got an error stating that the network path was not found. But now I do get a Windows credentials popup with the name of the availabilitygroupname as the user. If I cancel the popup the script continues perfectly. Any idea why this popup shows up? I do get it on the SQL machines and on a workstation that I normally use.
Hello
Great script when I run in powershell window it is okay but as a SQL jobs result is failed. It is on SQL 2019 HA Developer Edition
Any advise?
Anil
Anil, a bit more detail on what error you are getting would be helpful. My gut feeling: you are running the agent job via an account with not enough permissions
Hello , Many thanks for the reply. Please find below the output of the job. If I copy the syntax in Powershell it works. the syntax is ” C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -Command “Copy-DbaLogin -Source SVWDC2TSQL2 -Destination SVWDC1TSQL2″ ”
Login WindowsUser NGH-AD\XXXXX_Admin Failed
Login – WindowsUser NGH-AD\GuptaA_Admin Failed
Login – WindowsUser NGH-AD\svcCDTSQL2$ Skipped Current
Login – WindowsUser NGH-AD\Backup_Service
Login – SqlLogin RFID_admin Failed
Any help will be much appreciated
Regards
Anil
Hi Andreas,
I am very happy with this script. However, how could I go about logging the results of Copy-DBALogin to a file? I have tried a couple variations of start-transcript to no avail.