I have an activated procedure for a service broker queue that requests a linked server. I signed the procedure using the method found here . However, I constantly see the following message in sql server logs:
Activated proc '[dbo]. [TestProc] 'runs in the queue' DBName.dbo.TestReceiveQueue 'displays 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, which are also associated with server requests and work fine. For some reason, this procedure refuses.
Here, some code (mostly) reproduces the problem. I have already created a certificate and associated login.
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;
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 to debug this further to find out why I am getting this error? I tried ssbdiagnose in a conversation and there were no configuration errors. I also tried registering CURRENT_USERinside the activated sproc, which returned as dbo.
When I mark a database as trustworthy, it works, of course (but that's what I'm trying to avoid).