Home Forums SQL Server 2012 SQL 2012 - General Providing a password to an SSIS package RE: Providing a password to an SSIS package

  • May 25, 2018 at 1:56 pm

    #1992037

    MikeRen - Friday, May 25, 2018 1:11 AM

    DennisR - Thursday, May 24, 2018 11:10 AM

    I never recommend that userids or passwords be placed in SSIS or any program. Both userid and password should be encrypted and passed in to the process or looked up in a protected table. A SSIS Script Task can be used to decrypt the userid and password to be used as a connection expression variable, although preferably not called userid or password. The userid and password would only appear in clear text in memory which reduces the chance of exposure. Passwords should be changed periodically and userids may change as people leave or change position in a company. If the company is a public company placing userids and passwords in a program could create an audit flag.

    I couldn't agree more and this is exactly why I was trying to find a better way to achieve my goal. What you describe is what I was thinking of but I couldn't find a suitable example on the web. Do you know of an example of how to encrypt a password and decrypt it within the package?
    The reason I'm having to use a password at all is because the remote system is not on our domain, but I've since found out that we are AD Federated with that organisation and therefore I might be able to use Integrated security and thus not require a password at all (a much preferable situation). I've had a steer towards using Credentials and Proxies so that I can ensure the Job Step runs as a user which will be authenticated at the other end. I'm investigating this now, but if you have an example of encrypting/decrypting then I'd welcome the opportunity to take a look.

    Just about any C# code can be run in a script task, search the web for C# AES encryption. If I identify anything specific then how you were encrypting/decrypting would be public knowledge. This can also be done with the EncryptByPassPhrase and DecriptByPassPharase -
    DECLARE @VARB1 varbinary(max);
    DECLARE @VARC1 varchar(max);
    select @VARB1 = EncryptByPassPhrase ( 'MyFavoritePassPhrase', '12345mypassword' )
    select    @VARB1 ENC1
        ,CONVERT(varchar(max), @VARB1 ,2) ENC1_C
        ,len(CONVERT(varchar(max), @VARB1 ,2)) ENC1_C_LEN
    SELECT @VARC1 = CONVERT(varchar(max), @VARB1 ,2) + '15'
    SELECT cast(DECRYPTBYPASSPHRASE ('MyFavoritePassPhrase', convert(varbinary(max)
        , '0X'+substring(@VARC1,1,len(@VARC1)-2), 1)) as varchar(800)) myPW

    This  alters the binary value from the Encrypt process by adding a x'15' so when stored it cannot be decrypted directly. More complex alteration may be used on the encryption value. The decrypt code removes the added x'15'. The password has an added prefix of 12345 so even if decrypted it is not valid until the 12345 is removed. The PassPhrase may be dynamic, perhaps including the userid or department.  The encryption is triple DES so is not the current standard of AES. IBM believes all current encryption methods will breakable in seconds with quantum processors and the NSA is considering lattice based encryption which may survive quantum attacks for a few years. Is a C# encryption more secure than a SQL statement? Both can be placed in a SSIS package so it really depends on how secure the SSIS package and how secure the source control repository is. For one application I deleted the source code since, like this application, it was for access security and not data security and was one way encryption. Note that SSIS can be used to run a Windows program and return a value which would be the most secure method since examining the SSIS code would not expose the method used to provide the clear password.