May 12, 2014 at 8:59 pm
Comments posted to this topic are about the item Encrypt a file from SSIS using the Advanced Encryption Standard (AES)
May 12, 2014 at 10:37 pm
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
May 13, 2014 at 8:33 am
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).
May 13, 2014 at 8:41 am
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.
May 13, 2014 at 12:07 pm
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.
May 13, 2014 at 2:13 pm
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!
May 14, 2014 at 4:46 pm
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.
May 15, 2014 at 8:04 am
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