May 4, 2016 at 10:05 am
I am trying to troubleshoot an issue with one of the SSIS packages that might have been imported from a different server, but from the server for e.g A that it might have got imported it runs fine through the SQL job, and when it runs on say B it fails with the error msg as described below. As per the client, he explained that the password used for 'sa' is correct, and he even explains that whenever he runs the package manually through SSIS execute package utility tool it succeeds...so basically it is only failing within the SQL job. The package is encrypted with password, and due to security reasons he can't provide me with the password to decrypt, and test it.
Now, from the error it seems that it is a login failure issues, and even whenever I ran the package manually through the SQL job it failed, and logged an event in SQL error log for the same <Login failed for user 'sa'. Reason: Password mismatch>. I am not sure what is wrong here, as he clearly states that the password for the account used is correct. I am not sure if there is any property or something in SSIS or SSDT/BIDS that is causing this package to fail while running through SQL job.
Message
Executed as user: ServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:12:51 PM Error: 2016-05-02 14:12:52.67 Code: 0xC0202009 Source: X_UpdateTables Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2016-05-02 14:12:52.67 Code: 0xC020801C Source: Data Flow Task 1 Source - XHeadcount [97] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2016-05-02 14:12:52.67 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source - XHeadcount failed validation and returned error code 0xC020801C. End Error Error: 2016-05-02 14:12:52.67 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-05-02 14:12:52.67 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:12:51 PM Finished: 2:12:52 PM Elapsed: 1.219 seconds. The package execution failed. The step failed.
May 4, 2016 at 3:47 pm
A couple things here.
First, the message is pretty clear. The SSIS package is failing because the login for SA, inside at least one of your SSIS connection managers is failing. Perhaps the password is correct and SA has been disabled by a DBA who knows better. Either way, someone assuring you that the SA password is correct is not the same as entering the correct password for SA. For troubleshooting reasons I'd try to use another user's credentials in that connection manager.
Second, you should not be using sa for this kind of thing (if at all). Not a good practice.
-- Itzik Ben-Gan 2001
May 5, 2016 at 8:56 pm
Alan.B (5/4/2016)
A couple things here.First, the message is pretty clear. The SSIS package is failing because the login for SA, inside at least one of your SSIS connection managers is failing. Perhaps the password is correct and SA has been disabled by a DBA who knows better. Either way, someone assuring you that the SA password is correct is not the same as entering the correct password for SA. For troubleshooting reasons I'd try to use another user's credentials in that connection manager.
Second, you should not be using sa for this kind of thing (if at all). Not a good practice.
I am also thinking in that direction may be the sa password that is used inside the package for the source connection manager is not correct. I am not sure what the client is doing at this point. It seems that I need to get on a call with him to understand what is going wrong. Is there by any means any possibility, that since the package is password protected it might have a property set "DoNotSaveSensitiveData". Most of the blogs or articles suggest that the property inside the package is not set to "EncryptSensitiveData", and hence it is failing. But, it is strange that he says that it is working perfectly fine outside the SQL job...it only fails inside the SQL job, so now if I think logically it should fail even if it is run outside the SQL job...right?
Thanks
May 5, 2016 at 9:16 pm
ffarouqi (5/5/2016)
Alan.B (5/4/2016)
A couple things here.First, the message is pretty clear. The SSIS package is failing because the login for SA, inside at least one of your SSIS connection managers is failing. Perhaps the password is correct and SA has been disabled by a DBA who knows better. Either way, someone assuring you that the SA password is correct is not the same as entering the correct password for SA. For troubleshooting reasons I'd try to use another user's credentials in that connection manager.
Second, you should not be using sa for this kind of thing (if at all). Not a good practice.
I am also thinking in that direction may be the sa password that is used inside the package for the source connection manager is not correct. I am not sure what the client is doing at this point. It seems that I need to get on a call with him to understand what is going wrong. Is there by any means any possibility, that since the package is password protected it might have a property set "DoNotSaveSensitiveData". Most of the blogs or articles suggest that the property inside the package is not set to "EncryptSensitiveData", and hence it is failing. But, it is strange that he says that it is working perfectly fine outside the SQL job...it only fails inside the SQL job, so now if I think logically it should fail even if it is run outside the SQL job...right?
Thanks
I don't have access to a SSIS box but the sensitive data encryption settings are something you can look at. Yes, it will not authenticate if it's set to "DoNotSaveSensitiveData". Yeah - this is the kind of thing where you need to be in there and test the package and job out with different credentials.
-- Itzik Ben-Gan 2001
May 5, 2016 at 9:46 pm
You need the password that encrypted the package to decrypt any sensitive data, without it the password to any sql connection as well as any other sensitive data is garbage. Should not be using SA and should not be using SQL server accounts for SSIS packages. Change to windows authentication so it can run under the service account or a proxy to control access.
And of course it works fine when running from BIDS since the password is provided, but once the package is saved the password is removed if it's not encrypted. If you set DoNotEncryptSensitive data I believe it will not save passwords since that's considered sensitive data. It doesn't mean than it's going to leave it in clear text, it means it's not going to save it, hence use Windows authenticate.
If you have to use sql server login then you can encrypt with user credential of the user account that will execute the job (i.e. sql agent job service account) then when it runs under that account in the job it's the same user so it will decrypt the passwords correctly.
Edit:
Just wanted to add that the last part about logging in as a service account to encrypt the package under that user credential so it will run in a job is not recommended. It's just a way to do it if you want to use the sql server logins and you're not given the password that encrypted the package if the package is encrypted with a password.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply