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
(with the role sysadmin)
Any pointers?
All help would be appreciated.
Thanks in anticipation,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
India
Forgot to mention one thing:
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
Thanks,
Vivek Balagangadharan
Software Engineer,
Fast Track Software Services,
India
|||Hi Vivek,
How is security defined on that linked server? Are you using Intergrated
Security (Windows account) or are you using SQL server authentication.
If you are using Intergrated security, then make sure that the account
that runs your SQL Server Agent service has proper rights to the linked
server (is Trusted for delegation).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Hi all,
> 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
> (with the role sysadmin)
> Any pointers?
> All help would be appreciated.
> Thanks in anticipation,
> Vivek Balagangadharan
> Software Engineer,
> Fast Track Software Services,
> India
>
|||Thanks Steen.
I am using SQL authentication and my user is present on both the DB
servers with the role sysadmin.
Now, both these DB servers are on different domains.
But, that still does not explain why it runs from the studio, but not
from the SQL job.
Thanks,
Vivek Balagangadharan
|||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'l ogin1',@.rmtpassword='####'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
2',@.useself=N'False',@.locallogin=N'login1',@.rmtuse r=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'
|||Hi
I can see that your script creates 2 linkedserver logins where one of
them has a locallogin specified. I'd try to create the linked server
without a local login specified and see if that works (or just drop the
login with a local login specified).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> 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'l ogin1',@.rmtpassword='####'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DB Server
> 2',@.useself=N'False',@.locallogin=N'login1',@.rmtuse r=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'
>
|||Thanks Steen.
Yes, 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)
Thanks,
Vivek Balagangadharan
|||hmmmm...that sounds a bit strange.
What does the job/script actually do? Could it be that it is trying to
do something on the local/source server that the job hasn't got access
to and that's the error you see rather than an error on the remote
server. I know the error meesage indicates something else, but you never
know..:-).
From where are you executing the job? If you are starting it from your
local PC, could you then try to log on to the server (with e.g. Remote
Desktop) and try to start it from there?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks Steen.
> Yes, 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)
> Thanks,
> Vivek Balagangadharan
>
|||Thanks for the reply Steen.
Well, all the job has/should do, is a simple "select count(*) from [DB
Server 2].[DB_name].[dbo].[table_name]" query.
In my job step, I have specified to "run as user" login1, which is
present on both the DBs and belongs to the sysadmin role group.
I was initially running the job from my management studio (which is
not the server which has the job)
Then, I moved to the local server which actually had the SQL Server
agent, created the job all over again (the owner being login1). But,
no success.
I hope I have answered your queries. Let me know if there is some
confusion.
Thanks,
Vivek Balagangadharan
|||Have you tried running the job without specifying a proxy account (Run
As account)?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
vivekbg@.fasttrackteam.com wrote:
> Thanks for the reply Steen.
> Well, all the job has/should do, is a simple "select count(*) from [DB
> Server 2].[DB_name].[dbo].[table_name]" query.
> In my job step, I have specified to "run as user" login1, which is
> present on both the DBs and belongs to the sysadmin role group.
> I was initially running the job from my management studio (which is
> not the server which has the job)
> Then, I moved to the local server which actually had the SQL Server
> agent, created the job all over again (the owner being login1). But,
> no success.
> I hope I have answered your queries. Let me know if there is some
> confusion.
> Thanks,
> Vivek Balagangadharan
>
