Access to the remote server is denied because the current security context is not trusted
I have a service broker which calls a stores procedure sp_dequeue queue --> ACTIVATION ( STATUS = ON, MAX_QUEUE_READERS = 1, PROCEDURE_NAME = sp_dequeue, EXECUTE AS 'Username' ); In my sp_dequeue i am inserting record in to Linked server... So when i am calling sp_dequeue from the magmnt studio it works fine. But when its called automatically via service broker i get this error msg : "Access to the remote server is denied because the current security context is not trusted.".
In my linked server property i have checked "Be made using this security constraint" and provided oracle userid and pwd also i have added 'Username' mapping to oracle server.(Both username and pwd of oracle/SQL are different) I googled for this error tried mostly all combination. Pls do help me in sloving this problem. using Sql server 2005 and oracle 10g
Answer
The explanation is given at Why does feature … not work under activation?. One solution is to enable TRUSTWORTHY, as you did. Another one is to use code signing, see Signing an activated procedure. Code signing is more complicated to deploy, but is required in very strict security environments because the TRUSTWORTHY flags opens the path to privilege escalation from dbo to sysadmin.