MSSQL automated security sync on AlwaysOn environment

As all of you probably know AlwaysOn is not only good feature, but also more and complex work for a DBA. In this article I’ll share with you how to automate security (logins) sync between AlwaysOn Availability Group (AG) replicas. Why we need this? Because if you or anyone else will create/alter/edit any user on SQL server primary replica, the user will not be amended on a secondary replica and if you forget to do the same change, sooner or later you may have a big problems due to security. Please note that you don’t need to care about database level security, because this will be synced by AG itself, you need to care about server level security and server roles.

So, to automate security sync I have decided to create a trigger on the SQL server. The trigger is built of few parts: the first is server logins, the second – server roles and the last – prevent any security changes on secondary replica.

SQL server logins

First thing you should know is that all changes performed on every secondary AG replica fired up by the trigger from primary replica will be executed under SQL server service account. To ensure minimal rights to the service account you need to grant view server state and view any definition (many people grant sysadmin role to the service account, but I strongly disagree with it):

GRANT VIEW SERVER STATE TO [Domain\SQL_Service_Account]
GO
GRANT VIEW ANY DEFINITION TO [Domain\SQL_Service_Account]
GO

The next thing trigger will do is to capture all CREATE_LOGIN, ALTER_LOGIN and DROP_LOGIN events, extract other require data such us affected login, command text from the captured event and create T-SQL command which will be executed on every secondary replica.

SQL server roles

Previously I have mentioned that I strongly disagree to grant sysadmin role to SQL server service account. So how we can add login to lets say sysadmin role if the service account does not have such permission? I decided, to keep secure environment is better than to have fully automated changes on server roles. Regarding my decision, in case of ADD_SERVER_ROLE_MEMBER or DROP_SERVER_ROLE_MEMBER event the trigger will send alert email when any server role change, with details like this:

Subject: Server role changed on [ServerName1]
Message: Need to perform the same server role change on [Servername2, ServerName3, etc.] secondary replica as performed on [ServerName1] primary replica: ALTER SERVER ROLE [diskadmin] DROP MEMBER [test]

This will allow you to know if anybody else made any server role change or at least remember you to make the same change on other replicas.

Prevent any security changes on secondary replica

And finally, what if anybody will make a security change on any secondary replica? I prefer to rollback any change like this. So if you will try to amend any user on secondary replica, you will get an error message:

Unable to add/alter/drop users on SECONDARY replica. Please perform the action on PRIMARY replica.

The source code

Here is the source code of the trigger, feel free to use it and amend it to fit your needs. If you have any thoughts, feel three to contact me or write any comment.

IF EXISTS (
       SELECT *
       FROM   sys.server_triggers
       WHERE  NAME = 'Trg_TrackLoginManagement'
   )
    DROP TRIGGER [Trg_TrackLoginManagement] ON ALL SERVER 
GO


CREATE TRIGGER [Trg_TrackLoginManagement]
ON ALL SERVER
FOR DDL_LOGIN_EVENTS, ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER
AS
  SET NOCOUNT ON
  
  DECLARE @recipients VARCHAR(200)
  DECLARE @AGDatabase VARCHAR(200)
  DECLARE @AGGroupName VARCHAR(200)
  DECLARE @SQLServiceAccount VARCHAR(200)
  
  
  SELECT @recipients = '[email protected]'			--Change to email you will receive alert
  SET @AGDatabase = 'DatabaseName'				--Change a database name which belongs to AG
  SET @AGGroupName = 'AGGroupName'				--Change to your AlwaysON AG name
  SET @SQLServiceAccount = 'Domain\SQLServiceAccount'	--Change to SQL service account SQL server is running on
  
  IF (
         SELECT ars.role_desc
         FROM   sys.dm_hadr_availability_replica_states ars
                INNER JOIN sys.availability_databases_cluster ag
                     ON  ars.group_id = ag.group_id
         WHERE  ag.database_name = @AGDatabase
                AND ars.is_local = 1
     ) = 'PRIMARY'
  BEGIN
      DECLARE @data                  XML,
              @EventType             VARCHAR(100),
              @EventTime             DATETIME,
              @ServerName            VARCHAR(100),
              @AffectedLoginName     VARCHAR(100),
              @WhoDidIt              VARCHAR(100),
              @EmailSubject          VARCHAR(500),
              @EmailBody             VARCHAR(800),
              @EmailRecipients       VARCHAR(300),
              @text                  VARCHAR(1000),
              @sql                   VARCHAR(1000),
              @executesql            VARCHAR(1000)
      
      SET @data = EVENTDATA()
      SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
      SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
      SET @text = REPLACE(
              REPLACE(
                  @data.value(
                      '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                      'varchar(max)'
                  ),
                  CHAR(10),
                  ''
              ),
              CHAR(13),
              ''
          )
      
      SELECT @sql = CASE 
                         WHEN @EventType = 'CREATE_LOGIN' THEN (
                                  SELECT 'create login [' + p.name + '] ' 
                                         + CASE 
                                                WHEN p.type IN ('U', 'G') THEN 'from windows '
                                                ELSE ''
                                           END 
                                         + 'with ' 
                                         + CASE 
                                                WHEN p.type = 'S' THEN 'password = ' 
                                                     + MASTER.sys.fn_varbintohexstr(l.password_hash) 
                                                     + ' hashed, ' 
                                                     + 'sid = ' 
                                                     + MASTER.sys.fn_varbintohexstr(l.sid) 
                                                     + ', check_expiration = ' 
                                                     + CASE 
                                                            WHEN l.is_expiration_checked > 0 THEN 'ON, '
                                                            ELSE 'OFF, '
                                                       END 
                                                     + 'check_policy = ' 
                                                     + CASE 
                                                            WHEN l.is_policy_checked > 0 THEN 'ON, '
                                                            ELSE 'OFF, '
                                                       END
                                                ELSE ''
                                           END 
                                         + 'default_database = [' 
                                         + p.default_database_name 
                                         + CASE 
                                                WHEN LEN(p.default_language_name) > 0 THEN '], default_language = [' 
                                                     + p.default_language_name 
                                                     + ']'
                                                ELSE ']'
                                           END
                                  FROM   sys.server_principals p
                                         LEFT JOIN sys.sql_logins l
                                              ON  p.principal_id = l.principal_id
                                  WHERE  p.name = @AffectedLoginName
                              )
                         WHEN @EventType = 'ALTER_LOGIN' THEN @text
                         WHEN @EventType = 'DROP_LOGIN' THEN REPLACE(@text, CHAR(13), '')
                         WHEN @EventType = 'ADD_SERVER_ROLE_MEMBER' THEN @text
                         WHEN @EventType = 'DROP_SERVER_ROLE_MEMBER' THEN @text
                         ELSE ''
                    END
      
      
      SELECT replica_server_name
      INTO   #secondarys
      FROM   sys.dm_hadr_availability_replica_cluster_nodes
      WHERE  group_name = @AGGroupName
             AND replica_server_name <> @@SERVERNAME
      
      WHILE EXISTS (
                SELECT *
                FROM   #secondarys
            )
      BEGIN
          SELECT TOP 1 @ServerName = replica_server_name
          FROM   #secondarys
          
          IF @EventType IN ('CREATE_LOGIN', 'ALTER_LOGIN', 'DROP_LOGIN')
          BEGIN
              SET @executesql = 'sqlcmd -S ' + @ServerName + ' -e -Q "' + @sql + '"'
              EXEC xp_cmdshell @executesql
          END
          
          DELETE 
          FROM   #secondarys
          WHERE  replica_server_name = @ServerName
      END
      
      PRINT @Eventtype + ' activity successfully transferred to secondary server.'
      
      DROP TABLE #secondarys
      
      IF @EventType IN ('ADD_SERVER_ROLE_MEMBER', 'DROP_SERVER_ROLE_MEMBER')
      BEGIN
          
          DECLARE @EmailMain VARCHAR(MAX)
          DECLARE @Secondaries VARCHAR(500)
          
          SELECT @Secondaries = COALESCE(@Secondaries + '; ', '') + replica_server_name
          FROM   sys.dm_hadr_availability_replica_cluster_nodes
          WHERE  group_name = @AGGroupName
                 AND replica_server_name <> @@SERVERNAME
          
          SET @EmailSubject = 'Server role changed on ' + @@SERVERNAME
          SET @EmailMain = 'Need to perform the same server role change on ' + @Secondaries + ' secondary replica '
          SET @EmailMain = @EmailMain + 'as performed on ' + @@SERVERNAME + ' primary replica:<BR>'
          SET @EmailMain = @EmailMain + @sql
          
          EXEC msdb.dbo.sp_send_dbmail 
               @recipients = @recipients,
               @subject = @EmailSubject,
               @body = @EmailMain,
               @body_format = 'HTML'
      END
  END
  ELSE
  BEGIN
      IF (
             SELECT SYSTEM_USER
         ) <> @SQLServiceAccount
         AND @EventType IN ('CREATE_LOGIN', 'ALTER_LOGIN', 'DROP_LOGIN')
      BEGIN
          PRINT 'Unable to add/alter/drop users on SECONDARY replica. Please perform the action on PRIMARY replica.'
          
          ROLLBACK
      END
  END
GO

 

Leave a Reply

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