Untrusted security context in signed activated procedure

Question

I have an activated procedure for a service broker queue that queries a linked server. I have signed the procedure using the method found here. However, I continuously see the following message in the sql server logs:

The activated proc '[dbo].[TestProc]' running on queue 'DBName.dbo.TestReceiveQueue' output the following: 'Access to the remote server is denied because the current security context is not trusted.'

The strange thing is that I have several different activated procedures in the same database, signed by the same certificate, that also do linked server queries, and work fine. For some reason, this procedure refuses to.

Here's some code to (mostly) reproduce the problem. I've created the certificate and associated login already.

CREATE PROCEDURE TestProc
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @convHandle UNIQUEIDENTIFIER;
DECLARE @msgTypeName SYSNAME;
DECLARE @status TINYINT;
DECLARE @srvName NVARCHAR(512);
DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
DECLARE @msgBody NVARCHAR(256);
DECLARE @cmd AS NVARCHAR(50);


RECEIVE TOP(1)
        @convHandle = conversation_handle,
        @msgTypeName =  message_type_name,
        @status = status,
        @srvName = service_name,
        @srvConName = service_contract_name,
        @msgTypeValidation = validation,
        @msgBody = CAST(message_body AS NVARCHAR(256))
        FROM TestReceiveQueue;

    --SELECT @convHandle, @msgBody

    IF (@@ROWCOUNT != 0)
    BEGIN

        SELECT * FROM openquery(LINKEDSERVERNAME, 'SELECT * FROM LINKEDSERVERDB.SCHEMA.TABLE')

        END CONVERSATION @convHandle
    END

END
GO

CREATE MESSAGE TYPE [TestMessageType] VALIDATION = NONE;

CREATE CONTRACT TestContract (TestMessageType SENT BY INITIATOR)

CREATE QUEUE [dbo].[TestReceiveQueue] With STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = [dbo].[TestProc], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ), POISON_MESSAGE_HANDLING (STATUS = OFF) ON [PRIMARY]
CREATE QUEUE [dbo].[TestSendQueue] WITH STATUS = ON, RETENTION = OFF, POISON_MESSAGE_HANDLING (STATUS = OFF) ON [PRIMARY]

CREATE SERVICE [TestReceiveService] ON QUEUE [dbo].[TestReceiveQueue] (TestContract)

CREATE SERVICE [TestSendService] ON QUEUE [dbo].[TestSendQueue] (TestContract)


Drop Procedure TestProc

ADD SIGNATURE TO OBJECT::[TestProc]
BY CERTIFICATE [ServiceBrokerProcsCert]
WITH PASSWORD = 'PASSWORDHERE'
GO

Is there any way I can debug this further, to figure out why I'm getting this error? I've tried ssbdiagnose on the conversation and there aren't any configuration errors. I also tried logging the CURRENT_USER inside the activated sproc which came back as dbo.

When I mark the database as trustworthy, it works, of course (but that's what I'm trying to avoid).

Answer 1

If database is TRUSTWORTHY OFF procedure will run only in context of signing user, not its OWNER as you expect.

Assign linked server privileges to user assiociated with ServiceBrokerProcsCert, it is right user in which context signed activation procedure runs.