Mar 202017
 

When you setup availability groups you will need to manually sync things like SQL Agent Jobs, logins, as well as additional meta data (More notes here: https://msdn.microsoft.com/en-us/library/hh270282.aspx). I was recently tasked with automating the syncing of the logins so that if we add or remove a login on the primary node, it will flow through to other related nodes. Thankfully the company I did the work for was generous enough to let me post the code and the process.

To start, you’ll need a way for the severs to talk. We already had linked servers setup between the nodes but this process could have been easily replicated by using a SSIS package or even something else.

Second, you’ll have to decide if you are going to use a merging process or a 1-way process. I decided to do a 1-way process as we only have one availability group setup on the servers. If you have multiple availability groups setup on the same servers, then you will need to tweak this process so that it works for you.

I built the following SP and then have a SQL Agent Job run this on all of the secondary nodes every day.

Below is a description of what the stored procedure does. You can find the script in its entirety on my websites github repo.

  • Parameters
    • Linked Server Name – The name of your primary server
    • Commit Changes – Bit – Allow you to run the SP without actually changing anything on your server. Very helpful when setting up a new server.
    • Debug Level – This will become clearer as we go through the whole process
      • Level 0 – No debug output
      • Level 1 – Only the work to do table
      • Level 2 – Most verbose level of data
  • Parameter verification
    • Make sure that the linked server exists and the linked server is the primary for the availably group in addition to other checks
  • Get data from the local and linked servers that the stored procedure will use for comparison
    • Login Info
      • SID
      • LoginName
      • LoginType
      • IsDisabled
      • Please review stored procedure for full list
    • Role Membership
    • Login Permissions
  • Determine which logins to ignore
    • Local logins to each server
    • System logins
    • sa
    • Anything in a new table that you create as part of setting this up called dbo.LoginsNotToSync. These can be SQL Logins, Windows Logins, or Windows Groups
    • If you wanted to, you could also add manual checks in here as well
      • Maybe your DBA logins match a certain format
  • Figure out if a SQL Login needs to be recreated due to the SIDs not matching
    • If you create a sql login manually on 2 servers it will create different SIDs and then when the servers flip between different members of the availability group you will have to fix orphaned users manually
    • As part of this process each of these users will need to be dropped from the secondary server and copied from the primary server
  • Drop/Create/Update logins
    • Drop
      • These are logins that only exist on the secondary server or need to be recreated due to SID
      • Saves the DROP LOGIN off into the Work To Do temp table
    • Create
    • Update
      • The code here is a little more complex but it finds and updates logins that need to have any of the following updates applied:
        • All Logins
          • Default Database
          • Disabled
          • Deny Connect
          • Does Connect need to be revoked
        • SQL Logins only
          • Password Hash
          • Checking if the Password Policy is used
          • Checking if the password is expired
      • Saves this work to the Work To Do temp table
  • Verify that the logins that will be dropped will not cause additional problems
    • Throw an error if any of the logins that will be dropped own a database
    • Throw an error if any of the logins that will be dropped own a SQL Agent Job
  • Sync the server level role membership
    • This process does not setup permissions for any server roles or create any new roles
    • It does make sure that the role membership is the same on both servers
    • Database level roles and permissions are handled manually for system databases or databases that are not in the availability group
    • Inside of the availability group database permissions move with the database
    • Saves off changes into the Work To Do temp table
  • Sync server level permissions
    • Does GRANTs, DENYs and REVOKEs for server level permissions
    • It specifically will not work if you are using GRANT WITH GRANT OPTIONs on permissions. We do not use this type of permission on these servers and did not want to write the code to handle it at this time.
    • Saves off to Work To Do temp table
  • Debugging
    • Level 1 – Output just the Work To Do so you can see what will be run if you commit the changes
    • Level 2 – Outputs the Work To Do as well but also includes:
      • Logins that are being skipped
      • SQL Logins where SIDs don’t match
      • Logins to Create
      • Logins to Drop
      • Base Login Information
      • Server Role Membership
      • Server Permissions
  • Commit
    • If you decide to commit the changes it will run through all of the Work To Do as well as log all of the work that is being done to a table
    • It does this part inside of a transaction as well just to be safe
 Posted by at 11:04 pm

  One Response to “Syncing Logins between Always-On Availability Group nodes”

  1. Nice article.

    The same could be achieved using PowerShell script at:

    https://maq.guru/synchronizing-sql-server-logins-in-an-always-on-availability-group/

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)