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 |
|
Kristen
Test
22859 Posts |
 Posted - 2004-12-07 : 12:52:52
|
| I've got two jobs. One is failing, the other is working.The one that works:Executed as user: MYSERVER\sql_service. The step succeeded.The one that is failing:Executed as user: MYSERVER\sql_service. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452). The step failed.Any ideas?Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-07 : 13:14:43
|
| Does it use a linked server? Who is the owner of the job? It has been recommended to always have sa as the owner of jobs.Tara |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-07 : 14:07:58
|
| That's it, thanks Tara. Job is running SProc that Execs another SProc that has an OPENQUERY to a linked server.If I have SA own the job does that mean that the SA password on the linked server has to be the same?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-07 : 14:13:09
|
| It just depends how you setup the linked server. If you select the third option in the security tab (from EM obviously), then yes. This is the option that I always select. You could also hard code the information in the linked server, but that's not recommended for security reasons.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-07 : 14:25:53
|
| Would it be advisable to run as a special user (e.g. "SQL_ADMIN"), rather than SA, which is set up on all servers to be the same? That way I could just grant permission on the SProcs in question to that specific user.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-07 : 14:30:49
|
| If SQL_ADMIN is a SQL account, then you can set this job to use that account and still use that third option in the linked server setup.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-07 : 15:47:44
|
| I prefer that, so that's what I'll go for. Thanks for your helpKristen |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-12-07 : 18:46:53
|
| I have seen this type of problem come up in alot of forums but have never seen such a direct isolation of the root cause and solution.Nice Job Tara.. |
|
|
|
|
|
|
Login failed for user '(null)'