Access to the remote server is denied because the current security context is not trusted.

Hello,

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.

The remote database is set to Trustworthy.

I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.

I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.