June 5, 2014 at 8:07 am
Hi,
SSIS 10.50.2500
I have created a package that runs fine on ServerA (where I developed the package). I then took the same package and did som changes (it compiles fine on ServerA)and then uploaded it to ServerB.
I now get:
Error: 0xC0202009 at Package, Connection manager "myConMan.sa": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
How can I move the package to a new server and execute it? I guess it has something to do with the password for user SA.
Regards
June 5, 2014 at 8:19 am
Yes. Do you have the option to use Windows authentication to connect instead? It's bad practice to do anything as sa.
John
June 5, 2014 at 9:31 am
Hi,
Yes, that would be possible.
That will fix my issue?
June 6, 2014 at 2:04 am
Well, you won't know until you try, but I would guess that it will.
John
June 6, 2014 at 2:09 am
My guess is that you stored the password for SA in the package, but used EncryptSensitiveWithUserKey as the protection level of the package.
On server B, another user is used to execute the package, so it cannot decrypt the password for SA. Hence, login failed.
Three solutions:
* change the protection level to EncryptSensitiveWithPassword and supply the package password when you execute the package
* change the protection level to DontSaveSensitive and supply the password for SA using configurations
* use windows authentication
Solution #3 is preferred.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply