Re: Facing the same problem

(axneon@xxxxxxxxxxxxxx) writes:
I am facing the same (well, similar) issue as the OP. Here's the
scenario. My stored procedure is being activated from a Service Broker
queue. Within the stored procedure, it is trying to do some operations
across a Linked server, which is failing with the 15274 (Access to the
remote server is denied because the current security context is not
trusted.) error.

The linked server is a SQL server, but not one where I have any
control. The username & password for connecting to the linked server
were provided to us by another team, so we have to use that only.

What is the solution to this problem (does a solution even exist??!)

Needless to say, the procedure works when executed from an interactive
session.

Have you tried making the database trustworthy?

ALTER DATABASE db SET TRUSTWORTHY

A broker-activation procedure runs in an impersonated context, and when
you impersonate a database user, you get locked into a sandbox if the
database is not marked as trustworthy.

Marking a database as trustworthy where you own all databases is not
a big issue. But if you are not the DBA, but only the database owner,
the DBA may (well he should) be reluctant to make the database trust-
worthy.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages