Service Broker: Setup Email Notifications On Disabled Queues

It took me some time to figure our this part even though it should be trivial. So hopefully this guide will help someone in the future. I think the hardest part was to understand the security context under which internal stored procedures run when activated by service broker.

First thing you need to do is to create a DisabledQueueNotificationQueue. I know I know, it’s a little redundant name but what it means is that you have a queue that receives notifications when other queues get disabled, therefore redundant name.

CREATE QUEUE dbo.DisabledQueueNotificationQueue

Create service endpoint with PostEventNotification message type

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

Next is we need to setup a notification procedure which will execute msdb.dbo.sp_send_mail

CREATE PROCEDURE [dbo].[spServiceBroker_SendDisabledQueueEmailNotification]
WITH EXECUTE AS OWNER
AS
	DECLARE @ch UNIQUEIDENTIFIER
	DECLARE @messagetypename NVARCHAR(256)
	DECLARE	@messagebody XML
	DECLARE @queueName varchar(100)
	DECLARE @emailTo VARCHAR(500)


	SET @emailTo = 'someaddress@yahoo.com'

	WHILE (1=1)
	BEGIN
		BEGIN TRY
			BEGIN TRANSACTION

			WAITFOR (
				RECEIVE TOP(1)
					@ch = conversation_handle,
					@messagetypename = message_type_name,
					@messagebody = CAST(message_body AS XML)
				FROM DisabledQueueNotificationQueue
			), TIMEOUT 60000

			IF (@@ROWCOUNT = 0)
			BEGIN
				ROLLBACK TRANSACTION
				BREAK
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
			BEGIN
				SET @queueName = 'Disabled queue: ' + @messagebody.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'VARCHAR(100)');

				EXEC msdb.dbo.sp_send_dbmail
					@profile_name = 'DBA_Notifications',
					@recipients = @emailTo,
					@body = @queueName,
					@subject = @queueName;

			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
			BEGIN
					DECLARE @errorcode INT
					DECLARE @errormessage NVARCHAR(3000)
					-- Extract the error information from the sent message
					SET @errorcode = (SELECT @messagebody.value(
						N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
						(/brokerns:Error/brokerns:Code)[1]', 'int'));
					SET @errormessage = (SELECT @messagebody.value(
						'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
						(/brokerns:Error/brokerns:Description)[1]', 'nvarchar(3000)'));

					-- Log the error

					-- End the conversation on the initiator's side
					END CONVERSATION @ch;
			END


			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				-- End the conversation
				END CONVERSATION @ch;
			END

			COMMIT TRANSACTION
		END TRY
		BEGIN CATCH
			ROLLBACK TRANSACTION
			DECLARE @ErrorNum INT
			DECLARE @ErrorMsg NVARCHAR(3000)
			SELECT @ErrorNum = ERROR_NUMBER(), @ErrorMsg = ERROR_MESSAGE()
			-- log the error
			BREAK
		END CATCH
	END

The important piece is that this procedure will execute as owner which will be a dbo schema when service broker activates the internal procedure.

Next we add the queue internal activation

ALTER QUEUE DisabledQueueNotificationQueue
WITH ACTIVATION
(
	PROCEDURE_NAME = dbo.spServiceBroker_SendDisabledQueueEmailNotification,
	STATUS = ON,
	MAX_QUEUE_READERS = 1,
	EXECUTE AS OWNER
)

Finally we create an event notification to send messages when queue gets disabled into the DisabledQueueNotifiactionQueue

CREATE EVENT NOTIFICATION DisabledTargetQueueNotification
	ON QUEUE dbo.TargetQueue
	FOR BROKER_QUEUE_DISABLED
	TO SERVICE 'DisabledQueueNotificationService', 'current database';

If we try to simulate a poison message and rollback receive 5 times the procedure won’t execute and will log an error:

The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’, schema ‘dbo’.

This is expected since the activated stored procedure is calling an msdb database and has no permissions since it is in the public context. Since I trust other database on my sql server I thought that enabling trustworthy would fix this problem: ALTER DATABASE [msdb] SET TRUSTWORTHY ON. But apparently the setup of my sql server would still not allow a public process to access system database. So the only one thing that was left to do is to create a certificate and sign the procedure so it would be trusted.

    CREATE CERTIFICATE spServiceBroker_SendDisabledQueueEmailNotificationCertificate
    	ENCRYPTION BY PASSWORD = 'SommePass91'
    	WITH SUBJECT = 'spServiceBroker_SendDisabledQueueEmailNotification signing certificate'
    GO

    ADD SIGNATURE TO OBJECT::[spServiceBroker_SendDisabledQueueEmailNotification]
    	BY CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
    	WITH PASSWORD = 'SommePass91'
    GO


    --------------------------------------------------------------------------------
    -- We leave the private key so we can resign the procedure later if it changes.
    -- If we remove the certificate the whole thing will need to be recreated
    --------------------------------------------------------------------------------
    --ALTER CERTIFICATE spServiceBroker_SendDisabledQueueEmailNotificationCertificate
    --	REMOVE PRIVATE KEY
    --GO

    BACKUP CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
    	TO FILE = 'c:\spServiceBroker_SendDisabledQueueEmailNotificationCertificate.cert'
    GO

    USE msdb
    GO

    CREATE CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
    	FROM FILE = 'c:\spServiceBroker_SendDisabledQueueEmailNotificationCertificate.cert'
    GO


    CREATE USER [spServiceBroker_SendDisabledQueueEmailNotificationUser]
      FROM CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
    GO

    GRANT AUTHENTICATE TO [spServiceBroker_SendDisabledQueueEmailNotificationUser]

    GRANT EXECUTE ON [sp_send_dbmail] TO [spServiceBroker_SendDisabledQueueEmailNotificationUser];

Now that the procedure has access to the sp_send_dbmail we have no issues and if you simulate poison message, you should receive an email notification with the name of the queue.