SQL Server 2005 introduces various methods to
protect your data. As discussed throughout this chapter, data
encryption uses a key to create nondeterministic ciphertext (encrypted
data), which can only be decrypted with the appropriate encryption key.
Before we look at key-based data encryption, we will examine the HashBytes() function, which returns a deterministic representation of the input data.
HashBytes()
The HashBytes()
function, new in SQL Server 2005, allows you to transform data passed
into the function, but you cannot recover the original value .
This function is suitable for when you need the identifying nature of
the data, but the actual value does not matter. Consider password
authentication or user and customer validation; these situations
require that a user submit a secret identification value, but the
actual value does not need to be recovered from the database in the
future. In other words, you will not need to "unhash" the data.
The HashBytes() function will support the MD2, MD4, MD5, SHA, and SHA1 algorithms. In most cases, you will use the SHA1 algorithm.
SELECT HashBytes('SHA1', 'ClearText')
GO
NOTE
Hashing
data applies a case-sensitive algorithm to deterministically transform
your data into a new series of characters. It is useful for
authentication, but limited in function, as you cannot later recover
the plaintext value. The standard algorithms in SQL Server 2005 are
theoretically vulnerable to collisions, where two distinct values
passed to the same algorithm will produce the same hash output.
The
beauty of this form of data protection is that it is deterministic,
which is also its shortcoming. When you hash the same text using the
same hash algorithm, the result will always be the same. This is very
important when you want to execute equality searches on the data. You
can add an index to a column that has been hashed and be confident that
the database engine will properly use this index to improve the
performance of queries that filter based on this data.
The
deterministic output of the hash is also the greatest risk to the
function. Bad guys can build a dictionary of hash results for different
hash algorithms and attempt reverse lookups against your data. This
poses a real threat to sensitive data. Typically, a salt is combined
with the plaintext data to strengthen the hash and protect against
these dictionary attacks.
Asymmetric Key and Certificate Encryption
Both
certificate encryption and asymmetric encryption embody a pair of keys.
Data encryption is achieved with a single public key, and decryption is
achieved with a separate, mathematically related private key .
Certificate encryption and asymmetric encryption are very
slow—significantly slower than symmetric key encryption. As a rule,
certificate encryption or asymmetric encryption is only recommended
when you need to encrypt and decrypt one or two records at a time. As
noted in the next section, asymmetric keys and certificates are ideal
for protecting symmetric keys.
A
certificate is typically a CER file that is created by an outside
certification authority and imported into SQL Server. You may generate
self-signed certificates in SQL Server, but these are limited with a
private key length of 1024 bits. SQL Server supports private key
lengths between 384 and 3456 bits on certificates that are imported
from external sources. Longer private key lengths will strengthen the
encryption, but will also impact the performance of decrypting and
encrypting data.
An
asymmetric key is also a container for a pair of keys—a public key and
a private key. As with a certificate, you can import an asymmetric key
from an external source. When you create an asymmetric key within SQL
Server 2005, you have the option of specifying a private key length of
512, 1024, or 2048 bits.
NOTE
Both
certificate and asymmetric encryption use the RSA algorithm to protect
the data. Assuming equal key length, certificate encryption and
asymmetric encryption will deliver the same encryption strength.
The
choice between a certificate and an asymmetric key is typically one of
culture. If your environment uses certificates from external
certification authorities, you will probably prefer to use certificates
in your SQL Server encryption implementation. If you are generating
your keys in SQL Server, you may prefer to use asymmetric keys when you
would like to use longer private key lengths to strengthen your
encryption.
The
private keys of asymmetric and certificate key pairs are protected with
either the database master key or a password. By default, the private
key is protected by the database master key—if you have created one. If
the database master key was not created, a password is required when
you create or import a certificate or asymmetric key.
If your
principal concern is to protect your database and backup files in the
event they are stolen, the default nature that protects the private key
with the database master key will suffice. If you are comfortable that
the individuals in the db_owner
database role have access to your database master key and all dependent
private keys, this default private key protection is sufficient.
In some extreme implementations, you may want to secure your data from the db_owner.
If so, you will protect your private key with a password. All of the
same aforementioned cautions apply where you must take care in how you
are supplying the password from the application to prevent the db_owner from viewing the passwords from Profiler or directly in stored procedures.
The following example will create a self-signed certificate and an asymmetric key in SQL Server 2005:
CREATE CERTIFICATE MyCert
WITH Subject = 'My Person Cert'
GO
CREATE ASYMMETRIC KEY AsymKeyPerson
WITH Algorithm = RSA_1024
GO
Permissions
defined on the certificate and asymmetric key will determine whether
the user has access to the private key. If you would like to grant a
user permission to encrypt data, but not decrypt data, the user is
granted VIEW DEFINITION permission on
the securable and will only have access to the public key. If the user
is allowed to decrypt and encrypt data, the user is granted CONTROL
permission on the securable and has access to both the public and the
private key. The current permissions assume that any user who has
access to decrypt data may also encrypt data. Permissions may not be
set to allow a user to only decrypt data, not encrypt data.
In the following example, Pam may access the public key to encrypt data using the AsymKeyPerson asymmetric key. Tom is able to access the private key and decrypt data that was encrypted with the AsymKeyPerson key.
GRANT VIEW DEFINITION ON ASYMMETRIC KEY AsymKeyPerson TO Pam
GO
GRANT CONTROL ON ASYMMETRIC KEY AsymKeyPerson TO Tom
GO
SQL Server 2005 has introduced the EncryptByAsymKey() and EncryptByCert() functions to encrypt your data. The following example will encrypt the SocialSecurityNumber data from a table named Source using an asymmetric key named MyAsymKey and insert the encrypted data into the Person table:
INSERT INTO Person
(
SocialSecurityNumber
)
SELECT
EncryptByAsymKey(AsymKey_ID('MyAsymKey'), SocialSecurityNumber)
FROM Source
GO
The DecryptByAsymKey() and DecryptByCert()
functions will return the varbinary representation of your decrypted
data. You may need to convert the output to a meaningful format for
your application. When decrypting the data, the DecryptByAsymKey() function is applied to the example as follows:
SELECT
CONVERT(
NVARCHAR(9),
DecryptByAsymKey(
AsymKey_ID('MyAsymKey'),
SocialSecurityNumber))
FROM Person
GO
Asymmetric
encryption is stronger than symmetric encryption, but performance will
make it unsuitable for most data stored in tables. Asymmetric
encryption is recommended for protecting symmetric keys and
configuration values stored in tables. Avoid using asymmetric
encryption on any column that requires filter activity on the encrypted
data or decryption of more than one or two records at a time. These
data requirements will perform better when encrypted with a symmetric
key.
Symmetric Key Encryption
The nature of symmetric encryption uses a single key to encrypt and decrypt the data.
Symmetric encryption is significantly faster than asymmetric
encryption, and it is the recommended method for encrypting data stored
in user tables. In fact, it has been my experience that decrypting data
that is encrypted with a symmetric key can be more than a thousand
times faster than asymmetric encryption. Because of the performance
differences, asymmetric keys are typically used to protect the
symmetric keys that are used to encrypt data.
SQL
Server 2005 symmetric encryption supports a number of algorithms. The
recommended algorithms in order of key length and encryption strength
are TRIPLE_DES, AES_128, AES_192, and AES_256. TRIPLE_DES is the strongest algorithm you may choose if you are running SQL Server 2005 on Windows XP. Microsoft does not recommend the RC4 or RC128 stream ciphers, as these are not salted, which results in a much weaker encryption implementation.
Symmetric
keys are protected by one or more of the following methods: asymmetric
keys, certificates, passphrases/passwords, or other symmetric keys.
Encrypting your symmetric key with a password will generate a TRIPLE-DES key to secure your symmetric key. If you are using a stronger encryption algorithm, such as AES_256,
your symmetric key is protected with a weaker algorithm. This may not
be an acceptable level of protection in your business and should be
considered prior to adopting this method of protection.
When you protect your symmetric key using a certificate or asymmetric key, as in Figure 5-4, you are implementing what is commonly known as the hybrid approach
to encryption. This is the recommended approach to blend the strength
of asymmetric encryption with the speed of symmetric encryption. You
are ensured a stronger method of securing your symmetric key, while
benefiting from the enhanced performance of symmetric encryption.
NOTE
While
asymmetric keys and certificates can use only one form of protection,
symmetric keys may use multiple forms of protection. If multiple forms
of protection are applied to a symmetric key, you need to use only one
form to open the key. A single symmetric key may be used between
multiple applications, and each application will have its own password,
certification, or asymmetric key to open the symmetric key.
The following example creates a symmetric key that is protected by a certificate. User Jack must have CONTROL permission on the certificate, so that the symmetric key may be decrypted. The user must also have VIEW DEFINITION
permission on the symmetric key to encrypt and decrypt data using the
symmetric key. Since this is a single key used for both encryption and
decryption, you will not specify a different set of permissions to
control each of these activities.
CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE MyCert
GO
GRANT CONTROL ON CERTIFICATE MyCert TO Jack
GO
GRANT VIEW DEFINITION ON SYMMETRIC KEY MySymKey TO Jack
GO
If you want to encrypt data with a symmetric key, you must first open—or decrypt—the key. Once the key is open, the EncryptByKey()
function is used to encrypt to your data. When you are done using the
symmetric key, it is a good practice to explicitly close the
key—although the key is closed when the session is terminated.
OPEN SYMMETRIC KEY MySymKey
DECRYPTION BY CERTIFICATE MyCert;
GO
INSERT INTO Person
(
SocialSecurityNumber
)
SELECT
EncryptByKey(
Key_GUID('MySymKey'),
SocialSecurityNumber)
FROM Source
GO
CLOSE SYMMETRIC KEY MySymKey;
GO
Depending
on how you protected your symmetric key, you will use one of a few new
functions exposed by SQL Server 2005 to support data decryption. The
first we will discuss is the DecryptByKey() function, which requires that you explicitly open your key. You may then pass in the Key_GUID of your symmetric key and the data to decrypt.
OPEN SYMMETRIC KEY MySymKey
DECRYPTION BY CERTIFICATE MyCert;
GO
SELECT
DecryptByKey(
Key_GUID('MySymKey'),
SocialSecurityNumber)
FROM Person
GO
CLOSE SYMMETRIC KEY MySymKey;
GO
Alternatively, if your symmetric key is protected with a certificate or asymmetric key, you can use the DecryptByKeyAutoCert() or DecryptByKeyAutoAsymKey()
function to decrypt your data in a single operation. When the functions
are executed, SQL Server will automatically open the symmetric key
associated with the encrypted data if the user has the appropriate
permission.
These
functions are useful when you decrypt data from a single column where
the data has been encrypted with multiple different symmetric keys. For
example, we have a single table, PersonID,
to store Social Security numbers and birthdates. We will create a
single certificate and two separate symmetric keys that are protected
with our single certificate.
CREATE TABLE PersonID
(
PersonID INT IDENTITY(1,1),
SocialSecurityNumber VARBINARY(100)
)
GO
CREATE CERTIFICATE CertPerson
ENCRYPTION BY PASSWORD = 'pJBp4bb92548d243Ll12'
WITH Subject = 'Person Cert',
Expiry_Date = '01/01/2009'
GO
CREATE SYMMETRIC KEY SymKeyPerson_1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertPerson
GO
CREATE SYMMETRIC KEY SymKeyPerson_2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertPerson
GO
When
we insert data, we specify a different symmetric key for each row. This
may simulate an environment where a single table is used across
multiple departments, and varying access is controlled at the row level
by symmetric keys.
OPEN SYMMETRIC KEY SymKeyPerson_1
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
GO
INSERT INTO PersonID
SELECT
EncryptByKey(
Key_GUID('SymKeyPerson_1'),
N'111111111')
GO
CLOSE SYMMETRIC KEY SymKeyPerson_1;
GO
OPEN SYMMETRIC KEY SymKeyPerson_2
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
GO
INSERT INTO PersonID
SELECT
EncryptByKey(
Key_GUID('SymKeyPerson_2'),
N'222222222')
GO
CLOSE SYMMETRIC KEY SymKeyPerson_2;
GO
When executed, the DecryptByKeyAutoCert()
function will automatically open the associated symmetric keys, decrypt
the data, and close the symmetric keys. Because each symmetric key is
protected with the same certificate, and we have permission to open all
of the associated keys, we can use the DecryptByKeyAutoCert() function to view all of the data in a single statement.
SELECT
PersonID,
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert(
Cert_ID('CertPerson'),
N'pJBp4bb92548d243Ll12',
SocialSecurityNumber)
) AS SocialSecurityNumber
FROM PersonID;
The results of the preceding example :
The user must have CONTROL permission on the certificate to open the certificate and VIEW DEFINITION on the associated symmetric keys. If the user does not have VIEW DEFINITION on one of the symmetric keys, the data is not returned for those rows that depend on that symmetric key.
This
is also an ideal method if you are interested in building a view to
return decrypted data to the user. A user will be granted SELECT
permission on the view, and must also have the aforementioned
permissions on the certificate and symmetric key to view the encrypted
data. If the user is not granted permissions on the certificates and
symmetric keys, the view will return NULL values.
CREATE VIEW vw_Person
AS
SELECT
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert (
Cert_ID('CertPerson'),
N'pJBp4bb92548d243Ll12',
SocialSecurityNumber)
) AS SocialSecurityNumber
FROM PersonID
GO
You
can also use encryption to control which users can access data at the
column level. The following example implements a solution to secure
data by column for different sets of users.
NOTE
This
example is not limited to symmetric encryption. You can control
security to each column by encrypting the data with a certificate or an
asymmetric key as well. As discussed earlier, certificate encryption
and asymmetric encryption are not recommended methods of encrypting
data in tables due to performance. If your business is willing to
accept the significant performance difference and absolutely requires
that data be encrypted with asymmetric keys or certificates, the
following example may be used to secure data in a single table for
different sets of users.
We will work with our PersonID table, but add another column to store BirthDate. We will use the SymKeyPerson_1 key to encrypt the SocialSecurityNumberSymKeyPerson_2 key to encrypt the new BirthDate column. column and the
IF EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = 'PersonID'
)
DROP TABLE PersonID
GO
CREATE TABLE PersonID
(
PersonID INT IDENTITY(1,1),
SocialSecurityNumber VARBINARY(100),
BirthDate VARBINARY(100)
)
GO
Create two users, CustomerService and HR_User. The SocialSecurityNumber column is encrypted with the SymKeyPerson_1 key, and the BirthDate column is encrypted with the SymKeyPerson_2 key. HR_User has access to all of the data in the table and is granted VIEW DEFINITION on both keys. CustomerServiceBirthDate column and is granted VIEW DEFINITION on the SymKeyPeron_2 symmetric key. Grant these users CONTROLSELECT permission on the table. will only have access to the permission on the certificate and
CREATE USER HR_User WITHOUT LOGIN
CREATE USER CustomerService WITHOUT LOGIN
GO
GRANT CONTROL ON CERTIFICATE::CertPerson TO CustomerService, HR_User
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKeyPerson_1 TO HR_User
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKeyPerson_2
TO CustomerService, HR_User
GRANT SELECT ON PersonID TO CustomerService, HR_User
GO
Insert two rows, encrypting each column with a different symmetric key:
OPEN SYMMETRIC KEY SymKeyPerson_1
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
OPEN SYMMETRIC KEY SymKeyPerson_2
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
GO
INSERT INTO PersonID
SELECT
EncryptByKey(Key_GUID('SymKeyPerson_1'), N'111111111'),
EncryptByKey(Key_GUID('SymKeyPerson_2'), N'02/02/1977')
GO
INSERT INTO PersonID
SELECT
EncryptByKey(Key_GUID('SymKeyPerson_1'), N'222222222'),
EncryptByKey(Key_GUID('SymKeyPerson_2'), N'01/01/1967')
GO
CLOSE SYMMETRIC KEY SymKeyPerson_1;
CLOSE SYMMETRIC KEY SymKeyPerson_2;
GO
Using the new CustomerService login, execute a SELECT statement to decrypt our two columns. CustomerService has access to the SymKeyPerson_2 symmetric key and is able to see the data in the BirthDate column. The data in the SocialSecurityNumber column returns NULL values when CustomerService executes the statement. Data is available from both columns when executed as the HR_User. Notice that although the columns are using different symmetric keys, the DecryptByKeyAutoCert()
is used to decrypt each column. A single set of code may be deployed to
your application, and access to the data is controlled through your
encryption keys.
EXECUTE AS USER = 'CustomerService'
GO
SELECT
PersonID,
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert (
Cert_ID('CertPerson') ,
N'pJBp4bb92548d243Ll12',
SocialSecurityNumber)
) AS SocialSecurityNumber,
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert (
Cert_ID('CertPerson') ,
N'pJBp4bb92548d243Ll12',
BirthDate)
) AS BirthDate
FROM PersonID;
GO
REVERT
GO
Symmetric
encryption and decryption is much better performing than asymmetric key
or certificate encryption. Even with this better-performing type of
encryption, searching a large set of encrypted data is still an issue.
The results of encrypting data with a symmetric key are
nondeterministic, which renders indexes less useful when searching the
data. Applying filters on your encrypted data will require decrypting
all data prior to applying the filter. This will result in an index or
table scan, which can negatively affect server resources while
seriously impacting the scalability and availability of your data for
concurrent user activity.
EncryptByPassphrase
If ease of use is the only factor in determining which method of encryption to use, EncryptByPassphrase() is worth considering. It may not be the best method based on most other criteria, as I will discuss in this section.
Encrypting
data with a passphrase is incredibly easy to implement. This form of
encryption does not require creating or securing certificates or keys,
as a symmetric key is generated at the time of encryption and stored
with the encrypted data. In fact, the Triple-DES symmetric key derived by the passphrase is not stored in any system table; it is not directly accessible by anyone.
To encrypt data using a passphrase, pass the clear text followed by a passphrase into the EncryptByPassphrase() function:
EncryptByPassPhrase(@PassphraseEnteredByUser, 'ClearText')
Data is decrypted by passing the passphrase and the encrypted text into the DecryptByPassphrase()
function. As with asymmetric and symmetric encryption, the decrypted
data is returned in varbinary and may require that you convert it to a
useful format for your application.
DecryptByPassphrase(@PassphraseEnteredByUser, 'EncryptedText')
Now
I will address the drawbacks to using this form of encryption. First,
the encrypted data can be decrypted only when passed the correct
passphrase—there are no other options for decrypting the data, which
introduces a level of risk. If the passphrase is lost, the data is
lost. Securely storing the passphrase is vitally important to
protecting your ability to decrypt the data.
EncryptByPassphrase() does not provide an option for using stronger keys, as with other forms of encryption. The EncryptByPassphrase() function will only use the Triple-DES symmetric key algorithm. SQL Server 2005 symmetric encryption provides stronger encryption keys, like AES 128, AES 192, and AES 256.
Your business requirements and service-level agreements may require
that the data be protected with stronger encryption algorithms with
larger keys.
The EncryptByPassphrase()
function in SQL Server 2005 will not enforce passphrase complexity. The
SQL Server 2005 symmetric key and asymmetric key/certificate encryption
may be protected by a password, and the CREATE
process will check for password complexity if required on your server.
If your business requires or may someday require password complexity,
and you will be encrypting data using a passphrase, you will need to
develop a passphrase validation routine that will check the passphrase
prior to using it to encrypt the data.
Finally, while this form of encryption appears to protect the data from individuals in the sysadmin fixed server role and db_owner
database role, your implementation may prove that this may not be the
case without significant changes to the application, as I will discuss
in the next section.
