Sunday, February 19, 2012

Execute Package Task says Login failed for 'sa'

Please help me with this error, I am desperate.

SSIS package "Clear.dtsx" starting.
Error: 0xC0202009 at Clear, Connection manager "10.11.60.30.msdb.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'.".
Error: 0xC00220E4 at Execute Package Task: Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
.
Task failed: Execute Package Task
Warning: 0x80019002 at Clear: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Clear.dtsx" finished: Failure.
Did you run this package in BIDS or the command line? As we're talking about in your other similar post, SSIS doesn't store passwords when promoting them to the server for execution. Either that, or you have the wrong password indicated for "sa."|||Child package is using different username so error should not be for 'sa'. Parent is using sa in Connection which is used to call this child package.|||

Hi Fahad,

If you are absolutely certain the sa password is supplied correctly, the issue may lie with the Package ProtectionLevel property.

First, I would ensure the sa password is correct. Make sure you can connect to the msdb database on the target server using the sa account credentials and that you can execute some simple query: Select 1 As 'One' or Select GetDate() are my favorites. If this succeeds:

Try setting the ProtectionLevel property on the Control Flow properties to either EncryptAllWithPassword or EncryptSensitiveWithPassword. You will need to supply a PackagePassword for this test as well - it's located just above the ProtecionLevel property.
When you're done, rebuild and re-deploy the package. You will need to supply the password when executing the package. If this succeeds, the issue is with the Package ProtectionLevel.

To address this, I recommend using Windows Authentication to connect to the server.

Hope this helps,
Andy

No comments:

Post a Comment