5

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).

Mansfield
  • 14,445
  • 18
  • 76
  • 112
  • Turn TRUSTWORTHY OFF again, comment out the `SELECT * FROM openquery` and run again. Most likely the issue is that impersonation cannot be transfered beyond the local DB. Setting TRUSTWORTHY to ON allows that to happen, though. – Solomon Rutzky Nov 25 '14 at 16:08
  • @srutzky I have other procs that also query linked servers, yet they're working. I need to be able to query the linked server... – Mansfield Nov 25 '14 at 17:58
  • But do the other procs have `EXECUTE AS OWNER`? This proc is already doing impersonation. I am not suggesting to not use a LinkedServer. I am just trying to identify the source of the error and suspect it is the LinkedServer due to the impersonation. Commenting it out and running again will narrow it down because if no error then I am on the right track. From there we can find a way to fix. – Solomon Rutzky Nov 25 '14 at 18:02
  • @srutzky yes, the other procs follow exactly the same procedure. If I take out the linked server and remove trustworthy, yes, it works. – Mansfield Nov 25 '14 at 18:08
  • So the other procs also have `EXECUTE AS OWNER`? Is the owner `dbo` for all of these procs? And who is running the procs? Are the other procs being run by a "dbo" user while Service Broker is executing as a non-"dbo" user? – Solomon Rutzky Nov 25 '14 at 18:43
  • @srutzky Yes, other procs all have Execute As. Owner is dbo for all. Procs are all activated by SB - conversations started from a trigger on a table inserted to by a low-privilege login. – Mansfield Nov 25 '14 at 18:59
  • You are saying that there is no difference in these procs? All owned by "dbo". All executed by the same user. All have `EXECUTE AS OWNER`. All use that same LinkedServer. All select from that same remote table? If yes, then has this proc changed since it was signed? Any changes, including to the `EXECUTE AS` option, require a re-sign. – Solomon Rutzky Nov 25 '14 at 19:06
  • Yes, Yes, Yes, Yes, No, No, Yes. I've re-signed the procedure after every change, still no dice. The only difference between the procs is which remote table they query. EDIT: Actually, the problem proc is inserting data to the remote table rather than just selecting or executing a remote sproc. But my test problem proc is only reading, same as the others that work. – Mansfield Nov 25 '14 at 19:16
  • If they don't all use the same LinkedServer, but some do that do not get an error, then not likely the config there (though LinkedServers do need to be configured to allow for modifications and not just reads). If the "problem proc" and "test problem proc" both hit the same table, then sounds like permissions there. Test by granting ALL permissions to `[Public]` for that table and see what happens. – Solomon Rutzky Nov 25 '14 at 19:26
  • @srutzky They all use the same linked server, just different tables. Complicating matters is the fact that this linked server is a DB2 database, not SQL. Impersonation is turned off for it though, it's accessed with a hardcoded login. – Mansfield Nov 25 '14 at 19:43
  • Is the linked server in a different domain than what you are accessing it from. – Wes Palmer Nov 30 '15 at 20:58

1 Answers1

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.

Patrik Třeštík
  • 160
  • 7