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

demo

What I did:
Created a SQL Job that accesses another database on some other server.

I get the error:
Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).

I have the remote server as the linked server to my local server.

The login account used for the job step is present on both the servers.

I tried executing the same query through my management studio with the
same login, and it works great.
It also works when i try using it from my web application.

But, it doesnt work when i use a SQL job

------
And to provide you with more info on the linked server setting, i just
"scripted" the create query for this.

/****** Object:  LinkedServer [DB Server 2]    Script Date: 06/22/2007
13:02:52 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB Server 2',
@srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is
changed with #### */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB Server
2',@useself=N'False',@locallogin=NULL,@rmtuser=N'login1',@rmtpassword='####'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB Server
2',@useself=N'False',@locallogin=N'login1',@rmtuser=N'login1',@rmtpassword='####'

GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2', @optname=N'rpc
out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DB Server 2', @optname=N'use
remote collation', @optvalue=N'true'

initially i had not created any linked server login. But, the job
started failing. That is when i created a linked server login, but
that didn't help either.
I even tried to impersonate a login (since the login was available on
both the servers).
Even forced the connections to use a fixed context (by specifying a
remote server login credential)
This did not work either.

The question that is bothering me is, why is it that the context is
trusted when i use the management studio, but not trusted when i use a
SQL job? (though i use the same login)

----------------------------------------
That doesn't make sense. When your Linked Server is setup to use a SQl
Server account, then this account needs to exists on the remote server.
When you then use the linked server, it doesn't use any Windows
accounts. I'm accesing a number of remote servers from many different
servers and I'm always using SQL Server authentication for this. Then
there are no Windows/AD account involved. That works fine even to
servers in our DMZ where we (of course) have a seperate domain that
knows nothing about any of our "normal" domains.


Try to set up your linked server to only use SQL Server authentication
and then make sure that the account you are using exists (and with the
same password) on the remote server. Then try to run the query from
QA/Management Studio to verify that it works. Then try to create a job
step that just runs that query and with no Run As/proxy account defined.

--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
-----------------------------------------------------------------------
Thank you Steen! It works now

Actually, it was a combination of a couple of things.
1) Remove the run as 'login1' for the step (which i started trying
after your post :) )
2) Force all the connections to use a fixed context (by specifying
login1 and his password) in the linked server properties.

And I get this message: "Executed as user: NT AUTHORITY\SYSTEM. The
step succeeded."
:) :) :)

Also, in my desperate attempt to get the code working, I had
introduced "Execute as user = 'login1' " in my sql query. I had to do
away with this too.

Thanks for the quick and continuous help.

Cheers,

Vivek Balagangadharan
Software Engineer,

http://dbaspot.com/ms-sqlserver/ ... -error-15274-a.html
------------------------------------------------------
Good article!

I tried as all your ways but still no success. then I tried "Execute as user = 'login1' ". As I tried use SSMS with assigned login it works as well. so in the stored procedure I set "Execute as caller" and try again, it did the trick!

So, if you want run a stored procedure inside a job which access the remote database(though linked server). remember add this command before run it.

Execute as caller

done.

John Zhang
Software Developer and Network Administrator