Cross Database Activation Security

I have two databases Basket_ODS and Intelligence_ODS.

I created a user in the Basket_ODS and Intelligence_ODS databases as follows:

USE Basket_ods

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

CREATE USER BasketServiceUser WITHOUT LOGIN

ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser

GRANT CONTROL ON SERVICE::[Order Send]

TO BasketServiceUser

CREATE CERTIFICATE BasketServiceCertPriv

AUTHORIZATION BasketServiceUser

WITH SUBJECT = 'ForBasketService'

BACKUP CERTIFICATE BasketServiceCertPriv

TO FILE = 'BasketServiceCertPub'

In the other database...

I created the following:

USE Intelligence_ODS

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************

USE Intelligence_ODS

GO

CREATE USER BasketServiceUser WITHOUT LOGIN

CREATE CERTIFICATE BasketServiceCertPub

AUTHORIZATION BasketServiceUser

FROM FILE = 'BasketServiceCertPub'

My Queue is in BASKET_ODS and is set up as:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BasketServiceUser'

)

I have performed the following grants in Basket_ODS

grant execute on ODS.ProcessOrderQueue to BasketServiceUser

ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.

grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser

ProcessOrderQueue proc is set as follows:

ALTER procedure [ODS].[ProcessOrderQueue]

WITH EXECUTE AS 'BasketServiceUser'

[ODS].[MoveOrderTotals_Core] is set up as follows:

when I run ProcessOrderQueue I get an error message:

ALTER procedure [ODS].[MoveOrderTotals_Core](@.Orderid uniqueidentifier)

with execute as 'BasketServiceUser'

I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)

The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.

Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?

Something key that I left out...

ProcessOrderQueue calls [Basket_ODS].[ODS].GenericProcessor which is defined as follows:

ALTER Procedure [ODS].[GenericProcessor](@.procName nvarchar(128), @.orderID uniqueidentifier)

WITH EXECUTE AS 'BasketServiceUser'

as

DECLARE @.sql nvarchar(4000)

Select @.sql = 'exec ' + @.procName + ' @.orderID'

print @.sql

EXEC sp_executesql @.sql,

-- PARAMETER DECLARATIONS

N'@.orderID uniqueidentifier',

-- PARAMETER VALUES

@.orderID

This procedure is called based on the message read from the queue by ProcessOrderQueue. It then looks up what stored procedure needs to be called as per the message and then calls GenericProcessor with the name of the stored procedure to call along with the associated OrderID. When I changed this proc from 'Execute as OWNER' to Execute as 'BasketServiceUser' I now get the following error message :

The server principal "S-1-9-3-3317550566-1317506277-4110005931-648375857." is not able to access the database "Intelligence_ODS" under the current security context.

The stored procedure [Intelligence_ODS].[ODS].[MoveOrderTotals_Core] does an Insert into [Intelligence_ODS].... Select from [Basket_ODS]. That is the cross database portion.

|||

In order to get the cross database EXECUTE AS context to work, the key ingredient is the signing of the procedure. I see this is missing from you example. In code signing, is really irelevant who owns the certificate in [Basket_ods]. The important thing is for this certificate to sign the procedure that attempts to call another procedure cross database (I think is the [ODS].[ProcessOrderQueue]):

ADD SIGNATURE TO OBJECT::[ODS].[ProcessOrderQueue] BY CERTIFICATE [BasketServiceCertPriv]

Note that once the signature is added, the procedure should not be altered in any way, or the signature will be lost.

In database Intelligence_ODS you have to create a user derived from the [BasketServiceCertPub] certificate and grant him the appropiate permissions, including AUTHENTICATE:

CREATE USER [BasketServiceCertPubUser] FROM CERTIFICATE [BasketServiceCertPub];

GRANT AUTHENTICATE TO [BasketServiceCertPubUser];

GRANT EXECUTE ON [MoveOrderTotals_Core] TO [BasketServiceCertPubUser];

This should be it. The EXECUTE AS clauses on the queue activation, as well as on the [ProcessOrderQueue] procedure, should be probably OWNER. The procedure [MoveOrderTotals_Core] does not need an EXECUTE AS.

I think you are mixing some concepts related to the process of setting up dialog security (users w/o login, certificate ownership) with the concepts related to EXECUTE AS and code signing procedures. The two concepts are very distinct. The EXECUTE AS relies on signing the procedures and users derived from certificates to achieve it's goals.

HTH,
~ Remus