Using Service Broker to replicate logins

Talking about mirroring and its shortcomings got me thinking:  there has to be a reliable way to replicate logins from the primary server to the mirror.  Sure, you could do it manually, that would work just fine for those applications with a limited number of logins where there isn’t much turnaround.  But then again, if you’re not creating logins on this server very often, you’re probably even more likely to forget to create a corresponding login on the mirror server.  And during a failover you’re going to have a lot of things on your plate, so if you already know your logins are synced up, that’s one less thing you have to worry about.

So I decided to come up with an automated system for keeping logins in sync between my primary server and my mirror.  Fortunately, in our environment, the primary and mirror instances are dedicated to this single application, so I don’t have to worry about replicating unnecessary logins, or having conflicting logins.  I’m also not going to worry about replicating logins from the mirror to the primary (in the event of a failover).  What I do want to ensure was that the SIDs are the same on each server to avoid having to sync up the users and logins after a failover.  I also definitely want the passwords to match.

I considered a few different options.  I discarded the idea of DDL triggers, I didn’t want a trigger failure preventing a login from being created.  I have a stored procedure that extracts CREATE LOGIN statements for every login, complete with SID, hashed password, and server level role assignments.  I could use that in a job to export the script to a file.  Definitely a possibility.  Then I happened across an article about Service Broker.  I’ve had a little bit of exposure to Service Broker in the past, dealing with DDL auditing, but it was very basic and local to a single server.  I’d never tried to configure a distributed Service Broker app, but I was pretty sure this was the direction I wanted to go in for this little project.  Thus began my crash course in SQL Server Service Broker.

My first step was to employ the Great Gazoogle to find examples.  Fortunately I came across this fantastic article on SQL Server Central, and I was able to use much of the sample code to get a basic distributed Service Broker application working.  Perfect!  This was going to be a piece of cake.  Then my problems started.  First I tried using this service, as-is, with my event notification.  No dice.  The queue was not compatible with the PostEventNotification schema.  The sample code creates its own Message Types for the contract.  So I tried using the PostEventNotification in lieu of the sample contract, like this:

CREATE SERVICE SyncSenderService
ON QUEUE [SyncSenderQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

Fail.  Then I tried using the sample contract, but changing the message types to PostEventNotification.  That produced an error just trying to create the contract.  SQL Server would allow me to create the contract with EventNotification message types, but that resulted in an error when the Event Notification tried to write to the service.

Closed event notification conversation endpoint with handle ‘{B2ACA311-9E54-E011-9EDF-02215ECA965F}’, due to the following error: ‘<?xml version=”1.0″?><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8408</Code><Description>Target service &apos;SyncSenderService&apos; does not support contract &apos;http://schemas.microsoft.com/SQL/Notifications/PostEventNotification&apos;.</Description></Error>’.

I tried a couple more configurations to no avail.  Gah!  Perhaps I just don’t know enough about XML and Service Broker, but I was beginning to think this just wasn’t possible.  Then I thought, ok, I’ve got a working distributed Service Broker app sending generic messages.  And I’ve got a working Event Notification tracking DDL locally.  Why not just keep both and have the Event Notification stored procedure record the logins that are created, and then use a scheduled job to send messaged via the distributed broker?

So I started with my Event Notification procedure.  It reads from EventQueue and records the information, including the SQL statement in a table I’ve created called syncSQLLogins.  It also logs the XML data to a Sync_Log table, just for backup/safety purposes.

USE Mirror_sync
GO
CREATE TABLE Sync_Log
(    messagename        NVARCHAR(256),
xmldata            XML)
GO 

CREATE TABLE SyncSQLLogins
(    loginname        nvarchar(128),
logintype        char(1),
loginsid        varbinary(85),
passwordhash    varbinary(256),
sqlcommand        varchar(max),
propagated        char(1) default 'N')
GO 

USE Mirror_sync;
GO
ALTER PROCEDURE [dbo].[SyncQueueReceive_usp]
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON; 

DECLARE     @message XML,
@messageName NVARCHAR(256),
@dialogue UNIQUEIDENTIFIER 

BEGIN TRY 

--Continuous loop
WHILE (1 = 1)
BEGIN 

BEGIN TRANSACTION; 

--Retrieve the next message from the queue 

SET @dialogue = NULL; 

WAITFOR (
GET CONVERSATION GROUP @dialogue FROM dbo.EventQueue
), TIMEOUT 2000; 

IF @dialogue IS NULL 

BEGIN
ROLLBACK;
BREAK;
END
;RECEIVE TOP(1)
@messageName=message_type_name,
@message=message_body,
@dialogue = conversation_handle
FROM dbo.EventQueue
WHERE conversation_group_id = @dialogue; 

IF @message.value('(/EVENT_INSTANCE/LoginType)[1]', 'VARCHAR(100)')  LIKE '%SQL Login%'
BEGIN
INSERT INTO syncSQLLogins (
loginname, logintype, loginsid, passwordhash,sqlcommand  )
SELECT name, type, sid, password_hash,@message.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(max)')
from sys.sql_logins
where sid = @message.value('(/EVENT_INSTANCE/SID)[1]', 'VARBINARY(85)');
INSERT INTO Sync_Log (messagename, xmldata)
VALUES (@messageName, @message) 

END 

IF @message.value('(/EVENT_INSTANCE/LoginType)[1]', 'VARCHAR(100)')  NOT LIKE '%SQL Login%' --LIKE '%Windows (NT) Login%'
BEGIN
INSERT INTO SyncSQLLogins (loginname,logintype,loginsid,sqlcommand)
VALUES (
@message.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)'),
'U',
@message.value('(/EVENT_INSTANCE/SID)[1]', 'VARBINARY(85)'),
@message.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(max)'))
INSERT INTO Sync_Log (messagename, xmldata)
VALUES (@messageName, @message) 

END 

COMMIT; 

END 

END TRY
BEGIN CATCH
DECLARE @errorNumber INT,@errorMessage NVARCHAR(MAX),@errorState INT,@errorSeverity INT,@errorLine INT,@errorProcedure NVARCHAR(128)
SET @errorNumber = error_number();
SET @errorMessage = error_message();
SET @errorState =  error_state();
SET @errorSeverity = error_severity();
SET @errorLine = error_line();
SET @errorProcedure = error_procedure();
if not(xact_state() = 0)
ROLLBACK;
RAISERROR('%s:%d %s (%d)',@errorSeverity,@errorState,@errorProcedure,@errorLine,@errorMessage,@errorNumber) WITH log;
END CATCH
END
GO 

So now I have the login name, SID and password hash from sys.sql_logins, login type, and the actual sql command issued to create it, all recorded in a table.  There’s also a propagated column with a default value of ‘N’ to tell my next procedure what logins still need to be replicated to the mirror.  Now I need a script to take the logins from this table and send them to the mirror via the distributed Service Broker service.  This can be left as a script or put into a stored procedure, either way can be run ad-hoc or via a scheduled job.

USE Mirror_sync;
GO
Declare @ConversationHandle uniqueidentifier,
@newmessage XML,
@sqlcommand varchar(max),
@passwordhash varbinary(256),
@loginsid varbinary(85),
@logintype char(1)

DECLARE lc1 CURSOR FOR SELECT loginsid, logintype, passwordhash, sqlcommand
FROM syncSQLlogins where propagated = 'N'
FOR UPDATE OF propagated

OPEN lc1
FETCH lc1 INTO @loginsid, @logintype, @passwordhash, @sqlcommand
WHILE @@FETCH_STATUS = 0
BEGIN

IF @logintype = 'S'
SET @newmessage = replace(@sqlcommand, 'N''******''', master.sys.fn_varbintohexstr(@passwordhash)+' HASHED')
+ ', SID=' + master.sys.fn_varbintohexstr(@loginsid) +';'

IF @logintype = 'U'
SET @newmessage =  @sqlcommand + ';'

Begin Transaction
Begin Dialog @ConversationHandle
From Service SyncSenderService
To Service 'SyncTargetService'
On Contract SampleContract
WITH Encryption=off;
SEND
ON CONVERSATION @ConversationHandle
Message Type SenderMessageType
(@newmessage)
UPDATE syncSQLlogins SET propagated = 'Y' WHERE CURRENT OF lc1

Commit

FETCH lc1 INTO @loginsid, @logintype, @passwordhash, @sqlcommand

END

CLOSE lc1
DEALLOCATE lc1

In a nutshell, what I’m doing here is taking the original SQL command and replacing the obfuscated password with the hashed password I had pulled earlier from sys.sql_logins and adding the SID.  In the case of a Windows login, the sql command is left as-is.  The final command is then sent to the SyncTargetService on the mirror server.  Finally, the row in syncSQLLogins is updated to set propagated = ‘Y’.

The last piece was to create a stored procedure on the mirror/target server to handle the incoming queue there.  This is fairly straightforward, it records the statement to a table for posterity, then executes it to create the login.  The only trouble I had with this one was that the message_body is a varbinary field and converting that directly to an nvarchar left me with some unwanted garbage at the beginning of the text.  I fixed that by first casting it as XML, then as an nvarchar.  This worked to give me nice, clean, syntactically acceptable statements.

USE Mirror_sync
GO

CREATE TABLE SyncSQLLogins
(
sqlcommand        varchar(max),
createdate        datetime default getdate())
GO

CREATE PROCEDURE usp_ProcessTargetQueue
AS
Declare @ConversationHandle as uniqueidentifier
Declare @MessageBody as nvarchar(max)
Declare @MessageType as sysname

Begin Transaction
Print 'Started Receiving ';

RECEIVE top (1)
@MessageType = message_type_name,
@ConversationHandle = conversation_handle,
@MessageBody = cast(cast(message_body as xml) as nvarchar(max))--message_body
FROM syncTargetQueue;

if @MessageType = 'SenderMessageType'
Begin

INSERT INTO Mirror_sync.dbo.SyncSQLLogins (sqlcommand) values (@MessageBody);
exec sp_executesql @MessageBody;
--PRINT @MessageBody;

SEND
ON CONVERSATION @ConversationHandle
Message Type ReceiverMessageType
('Message is received')
END Conversation @ConversationHandle
END

Commit
GO

I then altered the queue to associate it with the new procedure.

ALTER QUEUE SyncTargetQueue WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dbo.usp_ProcessTargetQueue ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF);

And that’s pretty much it.  I’ll have to play around with it to see if it will also process DROP LOGIN and ALTER LOGIN statements, and I could probably have the initial event stored procedure handle sending the messages, thus eliminating the need for a scheduled job, but for now I’m happy with how this turned out.  And fighting through the process has really helped me get more familiar with Service Broker.  Now I’m wondering where else we could be using it…

Also recommended: