Signing an activated procedure

In my previous entry http://rusanu.com/2006/01/12/why-does-feature-not-work-under-activation/ I mentioned that one way to enable an activated procedure to be trusted at the server level is to sign the stored procedure with a certificate and grant appropriate permissions to the signing certificate. In this entry I want to show the details how to do this.

Let’s say that we are implementing a SQL Server management and monitoring system that is based on Service Broker. One useful piece of functionality would be to be able to interrogate a SQL Server instance about existing sessions. A trivial implementation of this would be a service that responds to a request message with a list of current sessions:

Note: For simplicity, these scripts assume you have all necessary permissions. To get the expected result, you would have to run them as sysadmin.

— Create a database for our demo

—

CREATE DATABASE [DemoActivationSigning];

GO

USE [DemoActivationSigning];

GO

— Create the service interface

—

CREATE MESSAGE TYPE [RequestSessions] VALIDATION = EMPTY;

CREATE MESSAGE TYPE [Sessions] VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT [SessionsContract] (

[RequestSessions] SENT BY INITIATOR,

[Sessions] SENT BY TARGET);

GO

— Create the service that exposes the interface

—

CREATE QUEUE [SessionsQueue];

CREATE SERVICE [SessionsService] ON QUEUE [SessionsQueue]

([SessionsContract]);

GO

Next step is to create a procedure that will implement the SessionsService:

— Create a procedure that implements

— the [SessionsService] service

—

CREATE PROCEDURE [SessionsServiceProcedure]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @dh UNIQUEIDENTIFIER;

DECLARE @mt SYSNAME;

BEGIN TRANSACTION;

WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,

@mt = message_type_name

FROM [SessionsQueue]), TIMEOUT 1000;

WHILE (@dh IS NOT NULL)

BEGIN

— If the message is a request message,

— send back a response with the list of sessions

—

IF (@mt = N‘RequestSessions’)

BEGIN

— Get the list of current sessions

— and send it back as a response

—

DECLARE @response XML;

SELECT @response = (

SELECT * FROM sys.dm_exec_sessions

FOR XML PATH (‘session’), TYPE);

SEND ON CONVERSATION @dh

MESSAGE TYPE [Sessions]

(@response);

END

— End the conversation and commit

END CONVERSATION @dh;

COMMIT;

— Try to loop once more if there are message

—

SELECT @dh = NULL;

BEGIN TRANSACTION;

WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,

@mt = message_type_name

FROM [SessionsQueue]), TIMEOUT 1000;

END

COMMIT;

END

GO

Now we can go ahead and test our service. We need to create a service from which we can initiate a dialog with SessionsService and send a request for the current sessions:

USE [DemoActivationSigning];

GO

CREATE QUEUE [RequestsQueue];

CREATE SERVICE [Requests] ON QUEUE [RequestsQueue];

GO

Now initiatiate a dialog and send a request:

DECLARE @dh UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dh

FROM SERVICE [Requests]

TO SERVICE ‘SessionsService’

ON CONTRACT [SessionsContract]

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @dh

MESSAGE TYPE [RequestSessions];

To verify our implementation of the service, we can invoke the execute the procedure:

EXEC SessionsServiceProcedure

This will create the response message and we can look for it in the [RequestsQueue]:

RECEIVE CAST(message_body AS XML) FROM [RequestsQueue]

We can see that all the response contains an XML response with the information for all the current sessions on the server. Since things are working fine, next step is to make the implementation procedure an activated procedure and attach it to the service queue:

USE [DemoActivationSigning];

GO

ALTER QUEUE [SessionsQueue] WITH ACTIVATION (

STATUS = ON,

MAX_QUEUE_READERS = 1,

PROCEDURE_NAME = [SessionsServiceProcedure],

EXECUTE AS OWNER);

Now if we test by sending again a request, we’ll get the response immediately because of the activation:

USE [DemoActivationSigning];

GO

DECLARE @dh UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dh

FROM SERVICE [Requests]

TO SERVICE ‘SessionsService’

ON CONTRACT [SessionsContract]

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @dh

MESSAGE TYPE [RequestSessions];

WAITFOR (RECEIVE CAST(message_body AS XML) FROM [RequestsQueue]);

We can see that we get a response, but when we look at the response XML we’re in for the surprise: it contains only one session, instead of all the current sessions on the server! This is because the VIEW SERVER STATE privilege is required to view all the sessions other than the current one. When activated, the procedure has lost the necessary privileges and it can only see its own sessions. The reason why this happens is explained in my previous article at http://rusanu.com/2006/01/12/why-does-feature-not-work-under-activation/

One possible solution is to simply mark the [DemoActivationSigning] database as trustworthy. But if we need finer grained control, we cannot do this. Marking the database as trustworthy makes the members of the database db_owner role de facto sysadmins. I’ll show here the (complex) way of achieving what we need using code signing. If you are a sysadmin asked to solve this problem, these are the steps needed to solve the problem:

– inspect the procedure code to ensure that you trust it

– change the procedure to have an EXECUTE AS OWNER clause

– create a certificate with a private key in the [DemoActivationSigning] database

– sign the procedure with the private key of the certificate you created

– drop the private key of the certificate (to prevent it from ever being used again)

– copy the certificate into the master database

– create a login from the certificate

– grant AUTHENTICATE SERVER to the certificate derived login

– grant any additional priviledge required by the procedure (e.g. VIEW SERVER STATE) to the certificate derived login

Here is the code to achieve this:

— Alter the procedure to add an EXECUTE AS OWNER clause.

— The procedure must have an EXECUTE AS clause for

— the code signing infrastructure to work properly.

—

ALTER PROCEDURE [SessionsServiceProcedure]

WITH EXECUTE AS OWNER

AS

BEGIN

SET NOCOUNT ON;

DECLARE @dh UNIQUEIDENTIFIER;

DECLARE @mt SYSNAME;

BEGIN TRANSACTION;

WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,

@mt = message_type_name

FROM [SessionsQueue]), TIMEOUT 1000;

WHILE (@dh IS NOT NULL)

BEGIN

— If the message is a request message,

— send back a response with the list of sessions

—

IF (@mt = N‘RequestSessions’)

BEGIN

— Get the list of current sessions

— and send it back as a response

—

DECLARE @response XML;

SELECT @response = (

SELECT * FROM sys.dm_exec_sessions

FOR XML PATH (‘session’), TYPE);

SEND ON CONVERSATION @dh

MESSAGE TYPE [Sessions]

(@response);

END

— End the conversation and commit

END CONVERSATION @dh;

COMMIT;

— Try to loop once more if there are message

—

SELECT @dh = NULL;

BEGIN TRANSACTION;

WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,

@mt = message_type_name

FROM [SessionsQueue]), TIMEOUT 1000;

END

COMMIT;

END

GO

— Create a certificate with a private key

— to sign the procedure with. The password

— used is not important, we’ll drop the

— private key

—

CREATE CERTIFICATE [SessionsServiceProcedure]

ENCRYPTION BY PASSWORD = ‘Password#1234’

WITH SUBJECT = ‘SessionsServiceProcedure Signing certificate’;

GO

— Sign the procedure with the certificate’s private key

—

ADD SIGNATURE TO OBJECT::[SessionsServiceProcedure]

BY CERTIFICATE [SessionsServiceProcedure]

WITH PASSWORD = ‘Password#1234’;

GO

— Drop the private key. This way it cannot be

— used again to sign other procedures.

—

ALTER CERTIFICATE [SessionsServiceProcedure]

REMOVE PRIVATE KEY;

GO

— Copy the certificate in [master]

— We must backup to a file and create

— the certificate in [master] from this file

—

BACKUP CERTIFICATE [SessionsServiceProcedure]

TO FILE = ‘C:\SessionsServiceProcedure.CER’;

GO

USE MASTER

GO

CREATE CERTIFICATE [SessionsServiceProcedure]

FROM FILE = ‘C:\SessionsServiceProcedure.CER’;

GO

— Create a login from the certificate

—

CREATE LOGIN [SessionsServiceProcedure]

FROM CERTIFICATE [SessionsServiceProcedure];

GO

— Grant the reuired priviledges to the certificate login

— The AUTHENTICATE SERVER permission is required for

— any other permission to take effect

—

GRANT AUTHENTICATE SERVER TO [SessionsServiceProcedure];

GRANT VIEW SERVER STATE TO [SessionsServiceProcedure];

GO

We can now go back and try again to send a message:

USE [DemoActivationSigning];

GO

DECLARE @dh UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dh

FROM SERVICE [Requests]

TO SERVICE ‘SessionsService’

ON CONTRACT [SessionsContract]

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @dh

MESSAGE TYPE [RequestSessions];

WAITFOR (RECEIVE CAST(message_body AS XML) FROM [RequestsQueue]);

GO

This time the result is the one expected, the response message contains all the sessions.