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.

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

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!

21 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

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