Job Fails to Linked Server
Anonymous
Server B: SQL 2005
I am running a job on Server A that executes a stored procedure, but I get
this error:
Access to the remote server is denied because the current security context
is not trusted. [SQLSTATE 42000]
I can execute the stored procedure in management studio and it runs fine.
On Server A there is a linked server to Server B using sql login ABC. I am
using security "Be made using this security context:"
On Server A the job owner and run as user is a domain user windows account
with sysadmin rights to Server A. On the General tab of the job I have left
run as blank.
I have tried the following with no luck:
1. On Server B (linked server) give sql login ABC sysadmin rights
2. On Server B give the windows acct that runs the job on Server A sysadmin
rights
3. On Server A assign the run as user to sql login ABC instead of the
windows acct
Erland Sommarskog
> Server A: SQL 2008
> Server B: SQL 2005
>
> I am running a job on Server A that executes a stored procedure, but I get
> this error:
>
> Access to the remote server is denied because the current security context
> is not trusted. [SQLSTATE 42000]
I will have to admit that I'm out a limb here - I'm not using Agent
that heavily myself - but I think the issue is that Agent performs
EXECUTE AS USER on behalf of the job owner. But when you impersonate
a datbase user, you are sandboxed in the current database, and cannot
access resources outside it. A linked server definition is such a
resource.
If this theory is right, it should help to make the database trustworthy.
However, this may not be acceptable. Or be entirely uncontroversial. If
you are in charge of all databases on the server, no problem. But if
there are different database owners which you typically do not trust,
making a database trustworthy opens a security hole.
On my web site there is an article that discusses alternatives,
although you would need some adoption to get it work with Agent.
http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
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
Russell Fields
limited to EXECUTE AS USER. If you EXECUTE AS LOGIN you are still stopped
from using the link and will get an error message such as:
Msg 15274, Level 16, State 1, Line 1
Access to the remote server is denied because the current security context
is not trusted.
RLF
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D40207F...@127.0.0.1...
Erland Sommarskog
> A remote call will not allow impersonation as Erland said. It is not
> limited to EXECUTE AS USER. If you EXECUTE AS LOGIN you are still
> stopped from using the link and will get an error message such as:
>
> Msg 15274, Level 16, State 1, Line 1
> Access to the remote server is denied because the current security context
> is not trusted.
know exactly what.
For what it is worth, I was able to do this at my network at home:
execute as login = 'YAZORMAN\Laban'
go
select * from SHILOH.Northwind.dbo.Customers
go
revert
To get this to I had to set up a login mapping. But this is because there
is no user Laban on the other machine. (And this is a workgroup, not a
domain.)