Encryption in SQL Server
By: Joe Kuemerle / @jkuemerle
www.preemptive.com / @PreEmptive
Background of Joe Kuemerle
Lead Developer at PreEmptive Solutions
Over 15 years of development experience
with a broad range of technologies
Focused on application and data security,
coding best practices and regulatory
compliance
Presenter at community, regional and
national events.
What is encryption
Encryption is the process of mathematically altering
data in a consistent, reversible fashion. It should be
used to store sensitive information that will need to be
retrieved later. Encryption relies on a secret that is the
only viable way to reverse the mathematical process
Encryption can be Symmetric where a single value (the
encryption key) is used to both encrypt and decrypt the
data
Or Encryption can be Asymmetric where there are two
values in that data encrypted with Value 1 can only be
decrypted with Value 2 and vice versa. This is also
know as Public Key Encryption
What is encryption
“Encryption is the process of
substituting a small secret in place of
a large secret”
Symmetric Encryption
Asymmetric Encryption
Certificates
Certificates are used to store asymmetric keys
and consist of a private key and public key.
SQL Server provides built in management of
certificates
Do not use use certificates to encrypt data, it
is slow and can be vulnerable when storing
lengthy values. Use a symmetric key to
encrypt the data and a certificate to encrypt
the symmetric key.
Hashes
Hashes
Hashes are not an encryption method. A hash
takes an input value and transforms it to a
unique value that is infeasible to convert back
to the original value.
Hashes are not for storing data that needs to be
re-read but are for proving knowledge of a
secret without the actual secret being revealed.
Hashes are ideal for storing passwords as the
password is not important but that the user
prove that they know the password.
Hashes
The .NET Framework includes built in
support for a number of hash functions
Best practices call for the use of SHA256,
SHA384 or SHA512 functions
Do not use MD5
or SHA1as there are
proven methods for
undermining the
hash results.
Transparent Data Encryption
Demos
Yay! No more bullet points.
Questions
References
Laurentiu Christofor:
http://blogs.msdn.com/lcris/
Raul Garcia: http://blogs.msdn.com/raulga
Bruce Schneier: http://www.schneier.com/
http://
blogs.msdn.com/b/sqlsecurity/archive/201
0/06/14/database-encryption-key-dek-man
agement.aspx
Demo source:
http://sqlcrypto.codeplex.com
Photo Credits
http://
www.flickr.com/photos/wwworks/461218
8594
http://www.flickr.com/photos/joyosity/33
58614462
http://
www.flickr.com/photos/jmrosenfeld/3158
25815
Feedback
Please fill out your evaluation form. Thanks!
http://speakerrate.com/jkuemerle
@jkuemerle / [email protected]
