Feb 08 2017 01:48 PM - edited Feb 08 2017 02:23 PM
I am using Service Broker (SQL Server 2012 SP3) with activation.
In the procedure used for SB Activation, a second stored procedure is called that attempts to retrieve data from a Linked Server.
At a high-level, this is the flow:
ServerA sends SB Message to ServerB
ServerB runs Procedure1 when the message arrives in the queue (Activation)
Procedure1 calls Procedure2, which has a Select * FROM [ServerA].[db].[schema].[table] statement
The following error is returned:
"Access to the remote server is denied because the current security context is not trusted."
I have verified that the error accurs at the Linked Server select statement, and that the two instances can otherwise send and receive messages back and forth successfully.
I have tried several combinations of the EXECUTE AS clause (Owner, Self... even my 'domain\username' which is sysadmin on both instances).
If I turn Activation Off, then send a message, then manually run Procedure1, it all works fine. So clearly i have a permissions thing here, but I can't figure it out.
What am I missing?