Encrypt a file from SSIS using the Advanced Encryption Standard (AES)

  • Comments posted to this topic are about the item Encrypt a file from SSIS using the Advanced Encryption Standard (AES)

  • Listen, I like the idea and most of the execution, I like code re-use but don't like having to deal with the GAC. I probably would have added the code within the script task so the package would be more self-contained. I wouldn't really consider this path unless I used this kind of encryption a lot, or I planned to.

    Either way, good article.

    CEWII

  • Very interesting - note that crypto is hard. This isn't nearly as practical, nor as wise, nor as safe as using gpg* to do the encryption for you, but it is interesting. Any particular reason you're doing this in C# rather than using SQL Server T-SQL functions and/or using an external program like gpg?

    Implementation notes:

    Critical: The IV should be randomly generated with a cryptographic random number generator like RNGCryptoServiceProvider, not static or, in your case, derived from the key. This will allow the code to generate different ciphertext when it uses the same key to encrypt the same file contents, and any adversary can no longer trivially gain that information (though they can, of course, see that the two files are within 15 bytes in size).

    This implementation is not FIPS compliant; for that, you would need to use AesCryptoServiceProvider instead.

    You should set the CipherMode deliberately. And if you choose ECB, the ghosts of encryption past will force you to use Windows ME forever... unpatched.

    You should strongly consider using a much higher number of PBKDF2-HMAC-SHA1 rounds in Rfc2898DeriveBytes.

    *If you use gpg:

    Get the latest GPG4Win or GnuPG

    If you use public keys rather than symmetric cryptography:

    Generate your key with at least

    gpg --gen-key --cert-digest-algo SHA256

    to ensure you're using a modern hash algorithm (SHA384 or SHA512 are also good)

    Use at least a 2048 bit RSA key, higher if you want possible security greater than 10-30 years

    Immediately after key generation, use gpg --edit-key <key> and consider updating cipher preferences:

    showpref

    setpref SHA512 SHA384 SHA256 SHA224 AES256 CAMELLIA256 AES192 CAMELLIA192 AES CAMELLIA128 3DES BZIP2 ZLIB ZIP Uncompressed

    exit

    If you're in the US and subject to FIPS, remove Camellia. If you're in Japan or the EU and subject to those regulations, consider putting Camellia ahead of AES (or remove AES).

  • Nadrek (5/13/2014)


    Very interesting - note that crypto is hard. This isn't nearly as practical, nor as wise, nor as safe as using gpg* to do the encryption for you, but it is interesting. Any particular reason you're doing this in C# rather than using SQL Server T-SQL functions and/or using an external program like gpg?

    Implementation notes:

    Critical: The IV should be randomly generated with a cryptographic random number generator like RNGCryptoServiceProvider, not static or, in your case, derived from the key. This will allow the code to generate different ciphertext when it uses the same key to encrypt the same file contents, and any adversary can no longer trivially gain that information (though they can, of course, see that the two files are within 15 bytes in size).

    This implementation is not FIPS compliant; for that, you would need to use AesCryptoServiceProvider instead.

    You should set the CipherMode deliberately. And if you choose ECB, the ghosts of encryption past will force you to use Windows ME forever... unpatched.

    You should strongly consider using a much higher number of PBKDF2-HMAC-SHA1 rounds in Rfc2898DeriveBytes.

    *If you use gpg:

    Get the latest GPG4Win or GnuPG

    If you use public keys rather than symmetric cryptography:

    Generate your key with at least

    gpg --gen-key --cert-digest-algo SHA256

    to ensure you're using a modern hash algorithm (SHA384 or SHA512 are also good)

    Use at least a 2048 bit RSA key, higher if you want possible security greater than 10-30 years

    Immediately after key generation, use gpg --edit-key <key> and consider updating cipher preferences:

    showpref

    setpref SHA512 SHA384 SHA256 SHA224 AES256 CAMELLIA256 AES192 CAMELLIA192 AES CAMELLIA128 3DES BZIP2 ZLIB ZIP Uncompressed

    exit

    If you're in the US and subject to FIPS, remove Camellia. If you're in Japan or the EU and subject to those regulations, consider putting Camellia ahead of AES (or remove AES).

    I recently wrote an article about some open source SSIS components, one of which does PGP encryption:

    SFTP, encrypt or compress data files in SSIS using custom components[/url]

    I write articles to learn how to do things, and how not to do them.

    I've already learned things from you and Elliot, so mission accomplished!

    Thanks for taking the time to educate me.

  • As another note, it's best to abandon MD5 entirely, as it's so vulnerable to collisions it's considered broken.

    By preference use anything in the SHA-2 family (SHA-224, SHA-256, SHA-384, SHA-512), or if you absolutely must (SQL Server 2005, for instance), use SHA-1.

  • Nadrek (5/13/2014)


    As another note, it's best to abandon MD5 entirely, as it's so vulnerable to collisions it's considered broken.

    By preference use anything in the SHA-2 family (SHA-224, SHA-256, SHA-384, SHA-512), or if you absolutely must (SQL Server 2005, for instance), use SHA-1.

    I was able to change my existing code from MD5 to SHA256 just by using the editor to drill down into the System.Security.Cryptography library.

    string SHA256;

    using (var md5 = System.Security.Cryptography.SHA256.Create())

    {

    using (var stream = System.IO.File.OpenRead(@"C:\AES256\test.txt.enc"))

    {

    SHA256 = BitConverter.ToString(md5.ComputeHash(stream));

    }

    }

    string[] line = { SHA256 };

    System.IO.File.WriteAllLines(@"C:\AES256\SHA256.txt", line);

    Here is the hash that it generated:

    89-BB-31-71-BE-34-E2-23-2C-E3-26-AD-1B-09-B3-8D-70-96-F9-02-B6-DB-D7-23-1E-48-CD-11-3F-63-08-0F

    Thanks!

  • Good article. However, there seems to be duplication of effort with the chosen approach...

    Instead of:

    1. Create a new dll referencing the crypto api

    2. Create a new script task referencing the new dll

    You could:

    . Create a new custom task (i.e. a task that appears in the SSIS toolbox) referencing the crypto api

    or (as someone else suggested)

    . Create a new script task referencing the crypto api

    Also, if you deploy the package to the server, you need to make sure that your custom dlls are registered on the server.

  • Stan Kulp-439977 (5/13/2014)


    I was able to change my existing code from MD5 to SHA256 just by using the editor to drill down into the System.Security.Cryptography library.

    using (var md5 = System.Security.Cryptography.SHA256.Create())

    Here is the hash that it generated:

    89-BB-31-71-BE-34-E2-23-2C-E3-26-AD-1B-09-B3-8D-70-96-F9-02-B6-DB-D7-23-1E-48-CD-11-3F-63-08-0F

    Thanks!

    You're welcome; you ended up with a 256 bit hash, which is the correct hash length.

    I'd suggest renaming "var md5" to "var SHA-256" if dashes or allowed, or "var SHA256" if not, to reduce confusion.

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

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