keeping availability group logins in sync automatically

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!

26 thoughts on “keeping availability group logins in sync automatically

  1. Don Boutwell Reply

    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?

    • Andreas Post authorReply

      It does not detect password changes, but a login copied from source to destination will have the correct password

  2. Anthony Perkins Reply

    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?

    • Andreas Post authorReply

      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.

  3. Lee Reply

    I like this script. You have a -whatif in the copy-dbalogin command. won’t that keep it from actually copying the logins?

    • Andreas Post authorReply

      Lee, yes. I did that on purpose so that people can try it without accidentally changing their settings

      • Lee Reply

        perfect. just wanted to make sure i was understanding the code correctly.

    • Andreas Post authorReply

      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

  4. PS Reply

    How to skip executing the script if I am executing it on secondary replica?

    • Andreas Post authorReply

      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.

  5. Kin Shah Reply

    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

    • Andreas Post authorReply

      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.

  6. Michael Kirkpatrick Reply

    Any chance you might update the script to handle instances with multiple AGs?

    • Andreas Post authorReply

      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.

  7. Pasquale Ceglie Reply

    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

    • Andreas Post authorReply

      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?

      • Dennis Reply

        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'”
        }

  8. Alex Reply

    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?

  9. Marty Reply

    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.

  10. Anil Reply

    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

  11. andreasschubert2019 Reply

    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

    • Anil Reply

      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

  12. Fred Sanders Reply

    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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.