locked
Login failed for user RRS feed

  • Question

  • Hello,

    SQL Server 2012, I've imported a project (with .ispac file) from our development env to our internal test env, so I've reconfigured the connection managers (I have 3 connection to the same instance, 3 different databases), I've specified username and password for the internal test environment:

    Data Source=MyInternalTestServer\InstanceName;User ID=MyDwStageUser;Password=************;Initial Catalog=MyStageDatabase;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

    when I try to run the package I get the following error from the execution report:

    BenchmarkReportingPackage:Error: 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 'MyDwStageUser'.".

    And in the ERRORLOG of the instance MyInternalTestServer\InstanceName, I see

    Date 18/07/2017 10:57:49
    Log SQL Server (Current - 18/07/2017 10:57:00)

    Source Logon

    Message
    Login failed for user 'MyDwStageUser'. Reason: Password did not match that for the login provided. [CLIENT: xxxxxxxxxx]

    Any idea?

    Tuesday, July 18, 2017 10:01 AM

All replies

  • Hi, 

    Are you able to log in with the same user/password in SSMS?

    you can open another ssms session and try to log in with the same credentials and see if that works. 

    Let us know what you find out.

    ~Mohsin


    Please click Mark As Answer if my response answered your question or vote as helpful if it helped you in any way

    Tuesday, July 18, 2017 11:29 AM
  • yes, I'm able to logon to the remote server using those credential in SSMS, so the credential are valid.
    Tuesday, July 18, 2017 1:34 PM
  • Strange that it works in SSMS. Could you try the following cases and see if any of them helps:

    1. Remove Persist Security Info=True from your connection string and see if it makes any difference.

    2. Replace Persist Security Info=True with Integrated Security = SSPI and retry. (Note: this might ignore the username/password and connect using the windows authentication)

    3. If 2. doesn't work, try Integrated Security = false


    Please click Mark As Answer if my response answered your question or vote as helpful if it helped you in any way


    Tuesday, July 18, 2017 4:04 PM
  • Hi Andrea Caldarone,

    The cause for this issue is when we create a package on our local machine, by default it encrypts this password so that it can only be decrypted when we are running the packages on the same machine, with the same account.

    To resolve the issue, please try to use following options:

    1. Encrypt the package with "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" and provide a package password each time User wants to edit/manipulate the package.

    2. Create a configuration file to provide the connection information during Package runtime.

    Reference: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred :- Reasons and troubleshooting.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Pirlo Zhang Wednesday, July 19, 2017 7:07 AM
    • Proposed as answer by Pirlo Zhang Tuesday, July 25, 2017 9:25 AM
    Wednesday, July 19, 2017 7:07 AM