Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
helpme
Posting Yak Master
141 Posts |
 Posted - 2009-04-19 : 18:43:29
|
| 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-19 : 20:38:45
|
| Is SQL Agent in both Servers running on domain account? |
 |
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2009-04-20 : 14:12:30
|
| 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. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-20 : 14:24:50
|
| My question is:The account running under SQL agent can access each other(Servers). |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2009-04-20 : 17:21:18
|
| 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. |
|
|
|
|
|
|
|
|
Linked server in procedure