Re: Facing the same problem

On Aug 11, 5:23 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
 (axn...@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 the15274(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, esq...@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

Wow! That worked like a charm. Thanks a bunch.

.

Relevant Pages

  • Re: Creating a database
    ... Please do yourself a favor, find a local user group, or buy a beginning book on SQL Server or take a class, you will save yourself a great deal of pain in the long run. ... CREATE DATABASE Products ... I can create a stored procedure from within the Server Explorer and from ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Setting up Linked server to MsAccess
    ... I'm running SQL Server\Express in "Windows Authenication Mode" (Do I have to ... they are accessing my MS Access linked server? ... Containing folders with MSAccess Database X.mdb ... SQl Express has linked server X mapped via UNC to Database X.mdb ...
    (microsoft.public.sqlserver.security)
  • Re: Help with SQL 2005 and Sourcesafe 6
    ... I understand where you're coming from but if you open up the .sql script ... stored procedure in the database that it belongs to. ...
    (microsoft.public.sqlserver.tools)
  • Re: Ad Hoc Query in C#
    ... I always like to put as much logic in the database as possible, ... tradeoff is the programmer cannot see what is going on in the database. ... or just parameterize his string sql query. ... > Putting logic like this in a stored procedure isn't alway to my liking ...
    (microsoft.public.dotnet.general)
  • Re: Alternative to Dynamic SQL?
    ... If security is that much of a concern, shutdown your sql ... each database is suppose to have an application ... search querying using stored procedure and found it invalid. ... dynamic SQL, modern database engines actually cache dynamic sql and ...
    (microsoft.public.sqlserver.programming)