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 2005 Forums
 SQL Server Administration (2005)
 SSIS Job failed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Oliver wang
Yak Posting Veteran

China
50 Posts

Posted - 11/07/2012 :  11:03:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/07/2012 :  11:42:18  Show Profile  Reply with Quote
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

China
50 Posts

Posted - 11/07/2012 :  11:48:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/07/2012 :  11:55:51  Show Profile  Reply with Quote
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

China
50 Posts

Posted - 11/07/2012 :  12:13:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/07/2012 :  13:41:38  Show Profile  Reply with Quote
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
  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.07 seconds. Powered By: Snitz Forums 2000