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 = 'alert@email.com' --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