agent job connecting to linked server: security context not trusted 1

[kittyyo]

I'm sorry to bother you with this question - maybe it is really a newbie issue, but I'm new to the SQL Server Agent.

I've written a script that calls a stored procedure on a linked server. If I execute this script on the local server, all is doing fine.

Then I tried to execute this script periodically using the SQL Server Agent.
Now, every time the script is executed and tries to access the remote server, I get the error:
"Access to the remote server is denied because the current security context is not trusted."

I already did this:
* I advised the job to execute the step with the
database user that has assigned the linked server
login.
* I used SQL Server Configuration Manager to change
the account the SQL Server Agent is running under
to a domain account that has database rights on
both the local and the linked server.
* I marked both databases (the local and the linked
one) as trustworthy.

The error keeps coming, what else could I do? Why is this happening anyway? Sorry, I only know little about SQL Server security.

There is no firewall or whatsoever between the two servers, they are both on the local LAN. The versions are the same on both the local and the linked server:
@@version = "Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)"

Thanks a lot for any suggestion,
Anne

 

[mrdenny]

You need to change the job owner to someone who is listed in the linked server's security tab. Or add the account that is listed as the job owner to the linked server's security tab.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kittyyo]

Thank you very much, that was the point!

Anne

 

[kittyyo]

I'm sorry, as it seems I was in an overhast. It appears that the job worked once, but now it does not any more with the same error as before (current security context is not trusted).

This is was I did:
Since I was not able to change the accounts on the linked server's security tab, I created the same account (same name and password) as I use to log in to the linked server on the local server. Then I set this account as the job owner and I also changed the "execute as" user of each job step to this user.

Maybe there is another suggestion?

Thanks a lot,
Anne

 

[mrdenny]

If the job step is a T/SQL command you shouldn't be able to change the execaute as user of the job step as there is no proxy account for T/SQL commands.

What kind of job step are you using?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kittyyo]

The job step is a T/SQL command.
I did not use a proxy account, but on the "Advanced" tab of the "Job step properties" window, I set the "Run as user" field.

I will mention some other details about the linked server I'm using:

I configured the linked server this way:

EXEC dbo.sp_addlinkedserver @server= 'MYLINKEDSERVER', @srvproduct= 'SQL Server'

On the linked server there exists an account 'xyz' with the password 'x$y3z'. (This is only for demonstration, of course. ) It is not a sysadmin account, it has only very limited rights.
Then I created an account on the local server called 'xyz' with the password 'x$y3z' and gave it the sysadmin server role.
In the properties tab of the linked server, I configured that "for a login defined not in the list above, connections will be made using the login's current security context". (However I already tried specifying the 'xyz' remote login for the 'xyz' local login explicitly and it did not work either.)

The T-SQL script I'm using in the agent job executes a local stored procedure, which itself calls another stored procedure on the linked server this way:

EXEC @return_value = MYLINKEDSERVER.database.dbo.sp_Foo

I hope this additional information helps you identify the problem.

Many thanks,
Anne

 

[mrdenny]

First remove the run as user value. You only need this if you want it to be different that the user which is running the job. As you don't want that, why make things harder than they need to be.

Is the server that you are connecting to setup for Windows only authentication or mixed mode?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kittyyo]

Both servers are set up for mixed mode.

Believe it or not: I removed the "run as user" values, and now it works! I would have never thought that! A typical case of overkill...

Denny, thanks a lot for solving this issue!

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com