Post reply

Saving Password in SSIS Configurations

  • May 19, 2014 at 3:38 am

    #308184

    Hi

    I am using Environment variable and SQL server configuration for storing connecting string of MSSqlserver in the format

    Data Source=xxxxxxxx;User ID=;Password=[PWD];Initial Catalog=[DATABASE];Provider=SQLNCLI11.1;Auto Translate=False;

    and Password Protection Level : EncryptSensitiveWithUserKey

    When I open the package "Connection Not found Error thrown by Connection Collection when specific connection is not found"

    if I open the connection and give password .Its working fine.

    Please let me know how to store password permanently in configurations

  • May 20, 2014 at 1:41 pm

    #1715119

    I actually ran into this issue myself. I never found a resolution to it.

  • May 20, 2014 at 2:45 pm

    #1715151

    What if you configure the password property itself with a configuration?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 20, 2014 at 4:40 pm

    #1715179

    greeshatu (5/19/2014)

    Please let me know how to store password permanently in configurations

    I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 20, 2014 at 11:34 pm

    #1715215

    Jeff Moden (5/20/2014)

    greeshatu (5/19/2014)

    Please let me know how to store password permanently in configurations

    I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.

    And what about Oracle connections or DB2/AS400?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 21, 2014 at 12:19 am

    #1715226

    Koen Verbeeck (5/20/2014)

    Jeff Moden (5/20/2014)

    greeshatu (5/19/2014)

    Please let me know how to store password permanently in configurations

    I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.

    And what about Oracle connections or DB2/AS400?

    Would you store such passwords in a plain text connection? I sure wouldn't. That's a clear violation of every security protocol except the one labeled "no security". I honestly don't know how to secure such connection information in SSIS (I avoid SSIS whenever possible for such things) but would imagine there would be a way. If the connection block obfuscates and encrypts the password, then no problem. Just remember to change the password on the remote system and in the package once every 3 months (or have used something like a double GUID for the PW ;-)) and you're probably golden for audits.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 21, 2014 at 12:23 am

    #1715228

    Jeff Moden (5/21/2014)

    Koen Verbeeck (5/20/2014)

    Jeff Moden (5/20/2014)

    greeshatu (5/19/2014)

    Please let me know how to store password permanently in configurations

    I'll be the first to admit that I don't know much about SSIS but my personal feeling is that you'd never ever want to do that. You should only be using Windows Authentication and only trusted connections based on whomever or whatever is logged in.

    And what about Oracle connections or DB2/AS400?

    Would you store such passwords in a plain text connection? I sure wouldn't. That's a clear violation of every security protocol except the one labeled "no security". I honestly don't know how to secure such connection information in SSIS (I avoid SSIS whenever possible for such things) but would imagine there would be a way. If the connection block obfuscates and encrypts the password, then no problem. Just remember to change the password on the remote system and in the package once every 3 months (or have used something like a double GUID for the PW ;-)) and you're probably golden for audits.

    I would store such passwords in a SQL Server table configuration and make sure that table is properly secured.

    Another option is to type it in in the connection manager and then encrypt the package by setting the protection level to EncryptAllWithPassword.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 21, 2014 at 5:04 am

    #1715279

    don't beat us up too badly... this is the way Microsoft created it..... It may be in plain text, but, you need 'sa' privledges to open up the job and to look at it so it is secure in that way.

Viewing 8 posts - 1 through 7 (of 7 total)

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