Post reply

The SQL connection password is not saved after deployed to the server

  • May 19, 2021 at 4:41 pm

    Go to Answer

    #3885396

    I developed an SSIS package in Visual Studio 2019 and deployed it to SQL Server 2017.  The package contains a SQL DB connection using Microsoft OLE DB Driver for SQL Server.  It worked fine until today when I updated the package with an email address change, then deployed it back to the server.  When executed from SQL Server Integration Services Catalogs, it returned an error:

    An OLE DB error has occurred. Error code: 0x80040E4D.

    An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'username'.".

    After manually updating the password in Connection Managers before execution, everything worked as expected.  So what might cause this issue, i.e. password is no longer saved in a deployed package, and what I can do to make sure that the password will be saved after deployed to the server, without the manual update?

    Thanks,

    Jay

  • May 19, 2021 at 8:06 pm

    Answer

    #3885498

    Sensitive data like passwords are usually not stored inside packages, for security reasons.

    Instead, they can be stored in SSISDB environments and marked as 'sensitive'. Once you have the password stored there, simply 'Configure' your deployed SSIS package and map the SSISDB variable to your deployed package's 'password' parameter.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 19, 2021 at 10:02 pm

    #3885542

    Thanks Phil.  I'm going to update my packages to use the environment variables.

    Still curious, what might caused this change since everything worked fine before.

    Jay

     

  • May 20, 2021 at 8:26 am

    #3885718

    jay-125866 wrote:

    Thanks Phil.  I'm going to update my packages to use the environment variables.

    Still curious, what might caused this change since everything worked fine before.

    Jay

    Possibly something to do with ProtectionLevel, but that's little more than a guess.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 20, 2021 at 1:33 pm

    #3885818

    Thanks Phil.

    Appreciate your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply