SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Linked server in procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helpme
Posting Yak Master

141 Posts

Posted - 04/19/2009 :  18:43:29  Show Profile  Reply with Quote
I have a procedure in an sql 2008 database (using a linked server)that pulls data from a sql 2005 database on a different server. If I run the procedure manually in management studio, it works fine. If I set up a job to automatically execute the same procedure, I get the following error:

Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.

From everything I have found on the subject, the usual problem is that the windows account for the sql agent doesn't have the proper rights. I can't see the problem. The same windows account for the sql 2008 agent is an admin for both the sql 2008 and sql 2005 data bases and has access to both servers. I've tried both windows authenication and an sql user in the linked server security with no success.

Any idea what I'm doing wrong?

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/19/2009 :  20:38:45  Show Profile  Reply with Quote
Is SQL Agent in both Servers running on domain account?
Go to Top of Page

helpme
Posting Yak Master

141 Posts

Posted - 04/20/2009 :  14:12:30  Show Profile  Reply with Quote
The sql agents themselves are running off different accounts, but in the same domain. I set up a test against another sql 2008 server running with a different windows account (same domain also), and it works.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/20/2009 :  14:24:50  Show Profile  Reply with Quote
My question is:

The account running under SQL agent can access each other(Servers).
Go to Top of Page

helpme
Posting Yak Master

141 Posts

Posted - 04/20/2009 :  17:21:18  Show Profile  Reply with Quote
The answer to your question is yes. Thanks for replying.

I think it's working now, but I don't really have a good explanation as to why it's working. I was playing around and trying some different things (very similar scenarios) with some test scripts and they were all working. Just for grins, I scripted the problem procedure to a query window, dropped the procedure and re-created it (exact same code), and now it's working. The only other thing I can think of, I had applied sql 2008 service pack 1 Friday afternoon. The procedure was created prior to that. Even if the service pack corrected a problem, I wouldn't have thought I would have needed to drop and recreate the procedure. So, I'm kind of at a loss on it, but it is working now.



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000