July 26, 2011 at 2:41 am
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
July 26, 2011 at 4:57 am
I got it resolved
July 27, 2011 at 1:04 pm
Please post the resolution so others can see in case they had the same error or similar issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 10:40 pm
Since we are using sql server 2005 we forgot to enable TRUSTWORTHY bit
ALTER DATABASE [DB_NAME] SET TRUSTWORTHY ON
u can check this blog w.r.t TRUSTWORTHY
July 27, 2011 at 10:52 pm
Thanks for posting back!
ranjeeth.pt (7/27/2011)
ALTER DATABASE [DB_NAME] SET TRUSTWORTHY ON
u can check this blog w.r.t TRUSTWORTHY
From the article you linked to:
Conclusions
The TW bit is used as a mechanism to control the surface area on very powerful features, but it relies on the DBO to be following least privilege principles, and not being a sysadmin. Having the combination of a sysadmin being the DBO on a DB that has been marked as TW bit = ON is a very dangerous one and we recommend to avoid it whenever it is possible.
Setting the TRUSTWORTHY bit can be very dangerous to the security of your system. Who is the dbo of the database? Is it sa, or someone in the sysadmin Fixed Server Role? In any case I would advise you to find a different way to make your application work than to use the TRUSTWORTHY bit.
More from the article's conclusion:
As an alternative to the TW bit, in SQL Server 2005 we also introduced the notion of module signing, and the digital signatures can be used to extend the impersonation context beyond the DBand to establish an explicit trust on signed assemblies
Have you explored signing the stored procedure your Service Broker is calling? Signing your procedure with a certificate should allow you to avoid setting the TRUSTWORTHY bit while still allowing your SB process to access the Linked Server. Signing Stored Procedures with a Certificate
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2020 at 3:44 pm
Msg 15274, Level 16, State 1, Line 6
Access to the remote server is denied because the current security context is not trusted.
I am getting the same error for a linked server, not a database. Any ideas?
April 11, 2023 at 11:56 pm
Msg 15274, Level 16, State 1, Line 6 Access to the remote server is denied because the current security context is not trusted.
I am getting the same error for a linked server, not a database. Any ideas?
If you are using impersonation then you may instead have to supply the remote login name and password.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply