Signing an activated procedure

March 1st, 2006

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.

4 responses to “Signing an activated procedure”

  1. […] my previous post http://rusanu.com/2006/03/01/signing-an-activated-procedure/ I’ve showed how to use code signing to enable a server level privilege (view server state) when […]

  2. […] If you start playing around with this method of invoking procedures asynchronously you will notice that sometimes the asynchrnously executed procedure is misteriously denied access to other databases or to server scoped objects. When the same procedure is run manually from a query window in SSMS, it executes fine. This is caused by the EXECUTE AS context under which activation occurs. the details are explained in MSDN’s Extending Database Impersonation by Using EXECUTE AS and myself I had covered this subject repeatedly in this blog. The best solution is to simply turn the trustworthy bit on on the database where the activated procedure runs. When this is not desired, or not allowed by your hosting environment, the solution is to code sign the activated procedure: Signing an activated procedure. […]

  3. Lynn Pettis says:

    Regarding deleting the private key afte signing the stored proc, do you need to resign the procedure if you alter it in the future? I’m concerned that if you need to make any modifications to the procedure, what happens?

  4. Remus says:

    Altering the procedure drops any existing signature, so you’ll need to sign it again. If you keep the private key around, then the re-signing the procedure is very easy, just add back the signature. If the private key is destroyed, then a new pair has to be issued and the whole procedure must be redone. However, keeping the private key allows anyone with access to the private key to abuse the priviledges granted to the public key derived login, hence the recommendation to drop the private key is security is a concern.