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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SSIS Job failed

Author  Topic 

Oliver wang
Yak Posting Veteran

50 Posts

Posted - 2012-11-07 : 11:03:37
Hi every,

I got a job failure and I checked the job history. It is showing me an error like this: "Cannot open database "xxx" requested by the login. The login failed." the job owner is a system admin account, so I don't think it is related to access. But I didn't find the database "xxx" in the instance. Do you think the missing database is the culprit? Really appreciate if you can share your ideas. Thanks a lot.

regards,

Oliver

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-07 : 11:42:18
If you are connected as an admin and still can't see the database then yes, that sounds like the culprit.

Be One with the Optimizer
TG
Go to Top of Page

Oliver wang
Yak Posting Veteran

50 Posts

Posted - 2012-11-07 : 11:48:58
Thanks for your response, TG. Sorry I made a mistake. The database is on another cluster node instance which I ignore to check. we can rule out database missing as the root cause.

regards,

Oliver
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-07 : 11:55:51
Has the job ever worked in this deployment? At any rate you'll probably have to dig into the SSIS package to see under what security context the connection to that server is being made. It is not necessarily the job owner.

Be One with the Optimizer
TG
Go to Top of Page

Oliver wang
Yak Posting Veteran

50 Posts

Posted - 2012-11-07 : 12:13:04
Hi TG,

Yes, the job had worked fine in deployment for a period of time until there was an account change. That's the story someone told me. It seems that a login in instance A can use a job(on instance B) to do something on a database on instance B. I'm not sure whether there is anything we need pay attention to for this scenario.

regards,

Oliver
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-07 : 13:41:38
Certainly sounds like permissions were lost. Again, you'll need to identify the login that the SSIS package is using to connect to that server. Once you know that you can add that login as a user in the database. Then you should also check what the job is trying to do in the database and confirm the appropriate permissions are granted.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -