Error when calling stored procedure from Service Broker Activation Procedure

%3CLINGO-SUB%20id%3D%22lingo-sub-44935%22%20slang%3D%22en-US%22%3EError%20when%20calling%20stored%20procedure%20from%20Service%20Broker%20Activation%20Procedure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-44935%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Service%20Broker%20(SQL%20Server%202012%20SP3)%20with%20activation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20procedure%20used%20for%20SB%20Activation%2C%20a%20second%20stored%20procedure%20is%20called%20that%20attempts%20to%20retrieve%20data%20from%20a%20Linked%20Server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20a%20high-level%2C%20this%20is%20the%20flow%3A%3C%2FP%3E%3CP%3EServerA%20sends%20SB%20Message%20to%20ServerB%3C%2FP%3E%3CP%3EServerB%20runs%20Procedure1%20when%20the%20message%20arrives%20in%20the%20queue%20(Activation)%3C%2FP%3E%3CP%3EProcedure1%20calls%20Procedure2%2C%20which%20has%20a%20Select%20*%20FROM%20%5BServerA%5D.%5Bdb%5D.%5Bschema%5D.%5Btable%5D%20statement%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20error%20is%20returned%3A%3C%2FP%3E%3CP%3E%22Access%20to%20the%20remote%20server%20is%20denied%20because%20the%20current%20security%20context%20is%20not%20trusted.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20verified%20that%20the%20error%20accurs%20at%20the%20Linked%20Server%20select%20statement%2C%20and%20that%20the%20two%20instances%20can%20otherwise%20send%20and%20receive%20messages%20back%20and%20forth%20successfully.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20several%20combinations%20of%20the%20EXECUTE%20AS%20clause%20(Owner%2C%20Self...%20even%20my%20'domain%5Cusername'%20which%20is%20sysadmin%20on%20both%20instances).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20turn%20Activation%20Off%2C%20then%20send%20a%20message%2C%20then%20manually%20run%20Procedure1%2C%20it%20all%20works%20fine.%26nbsp%3B%20So%20clearly%20i%20have%20a%20permissions%20thing%20here%2C%20but%20I%20can't%20figure%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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?

0 Replies