March 16, 2016 at 12:00 pm
SSIS 2005 (yeah, I know) to SQL Server 2005 (ditto) OLE DB connection using package config, table config info
Help! I need some ideas on where to look to solve a production problem.
I've got one of those issues where "nothing has changed" but a well seasoned prod process suddenly started failing. Package runs when executed manually--as usual--but fails when executed through the SQL Agent job that's been running it for years. Error is
Description: "Login failed for user 'dwdba'.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "hris" requested by the login. The login failed.".
Unfortunately the connection is to the HR db on which I don't have perms. The admin that does swears that no connection attempt is being made--no evidence of rejected connection in the logs.
Ideas on where I should look or advise the admin of the HR db to look?
Thanks!
March 16, 2016 at 12:39 pm
Fly Girl (3/16/2016)
SSIS 2005 (yeah, I know) to SQL Server 2005 (ditto) OLE DB connection using package config, table config infoHelp! I need some ideas on where to look to solve a production problem.
I've got one of those issues where "nothing has changed" but a well seasoned prod process suddenly started failing. Package runs when executed manually--as usual--but fails when executed through the SQL Agent job that's been running it for years. Error is
Description: "Login failed for user 'dwdba'.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "hris" requested by the login. The login failed.".
Unfortunately the connection is to the HR db on which I don't have perms. The admin that does swears that no connection attempt is being made--no evidence of rejected connection in the logs.
Ideas on where I should look or advise the admin of the HR db to look?
Thanks!
Have them try this. If it matches the criteria it will create a script that they then can copy and past and run to fix the issue. What the script does is check the Server Login and the Database Login to make sure that the SID matches the between the two. If not the sp_change_users_login with the auto_fix can be called to fix the mismatch. This can also be seen by going to he server login section and looking at the User Mapping section. My guess is that you will see that the server login is not mapped to the database.
USE [hris]
GO
SELECT 'USE ' + DB_NAME() + '; EXEC sp_change_users_login [auto_fix], [' + CAST(name AS sysname) COLLATE Latin1_General_CI_AS + '];', su.*
FROM dbo.sysusers su
WHERE su.islogin = 1
and su.issqluser = 1
AND NOT EXISTS (SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid)
AND EXISTS (SELECT 1 FROM master.dbo.syslogins s2 WHERE su.name = s2.loginname)
I hope this helps.
--Carlton
March 16, 2016 at 3:25 pm
Hmmmmmm, thanks for the suggestion. In the end I have no idea what changed.
The SysAdmin reported that at the times when my process would have been attempting to log in, login bounces were found against a different server, the test server for HR. However, no amount of sleuthing could determine any problem with config, environment variable, etc. The package in the bin had not been updated for a year.
However, when I re-ran the job it succeeded.
Has this happened to anyone else?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply