Cryptography Support

In previous releases, if you were to encrypt and decrypt data, you had to either implement your own custom solution or rely on third-party vendor solutions that integrated with SQL Server to provide cryptography support. Listening to the feedback from SQL Server developers, Microsoft decided to add native support for encryption capabilities, including the key management infrastructure in SQL Server 2005. Functions such as EncryptByKey, EncryptByAsmKey, EncryptByCert, and EncryptByPassPhrase, as well as their decryption equivalents can be used to encrypt and decrypt data. The following sections show you how to encrypt/decrypt data and how to manage keys and certificates, and then it discusses signed modules, which are an alternative to the module execution context feature described earlier. Let's begin with the data encryption feature introduced in SQL Server 2005.

Data Encryption

Encryption is a technique for securing data by converting clear or plain text into scrambled text or ciphertext. Encryption algorithms require an entity that they can use to encrypt or decrypt the text. This entity is known as the key. If the same key is used for encryption and decryption, such a key is termed a symmetric key. If one key is used for encryption and a different key is used for decryption, this key is termed an asymmetric key. An asymmetric key is generally a pair of public and private keys. SQL Server 2005 supports both symmetric and asymmetric keys. Asymmetric keys offer more robust security than symmetric keys, but encryption and decryption using asymmetric keys can be a very costly operation and should be restricted to small datasets. In addition to using symmetric and asymmetric keys, you can use a certificate or a passphrase to encrypt and decrypt data. You can think of a certificate as an entity that wraps the public key and is associated with a private key. A symmetric key can be created by using the CREATE SYMMETRIC KEY DDL statement, an asymmetric key can be created by using the CREATE ASYMMETRIC KEY DDL statement, and a certificate can created by using the CREATE CERTIFICATE statement, which can load a certificate from a file into SQL Server or have SQL Server generate a certificate.

Encrypting and decrypting using keys and certificates is discussed later in this chapter. To see cryptography in action, here's a simple example of encrypting and decrypting data by using a passphrase:

USE AdventureWorks; GO CREATE TABLE dbo.tblAccount   (RecordID int IDENTITY(1,1) NOT NULL PRIMARY KEY,    AccountNo VARBINARY(100) NOT NULL,    BusinessName VARBINARY(512) NOT NULL,    Balance MONEY NOT NULL); GO INSERT INTO dbo.tblAccount VALUES   (EncryptByPassPhrase('WjkKWjkF_1', '001'),    EncryptByPassPhrase('WjkKWjkF_2', 'Progressive Sports'),    1000); INSERT INTO dbo.tblAccount VALUES   (EncryptByPassPhrase('WjkKWjkF_1', '002'),    EncryptByPassPhrase('WjkKWjkF_2', 'Metropolitan Sports Supply'),    5000); SELECT * FROM dbo.tblAccount; GO SELECT RecordID,        CONVERT(VARCHAR(15),              DecryptByPassPhrase('WjkKWjkF_1', AccountNo)) AS AccountNo,        CONVERT(VARCHAR(255),              DecryptByPassPhrase('WjkKWjkF_2', BusinessName)) AS BusinessName,        Balance FROM dbo.tblAccount; GO DROP TABLE dbo.tblAccount; GO

This script creates a table to store some financial data. The requirement is to encrypt two fields: the account number and the business name. The script uses the EncryptByPassPhrase function during the INSERT operation and the DecryptByPassPhrase function while querying the data from this table. After you run the INSERT statements, the account number and business name columns appear as varbinary encrypted data in the table. The DecryptByPassPhrase function, along with the same passphrase used for encryption, is used to turn the ciphertext into plain text.

Note

The EncryptByPassPhase and DecryptByPassPhrase functions use the 3DES encryption algorithm in Cipher Block Chaining (CBC) mode.

Let's now look at what it takes to implement encryption by using keys and certificates.

Key Management

As mentioned earlier, you need a key or a certificate to encrypt and decrypt data. The SQL Server 2005 team realized that it is critical to secure the keys and certificates used for encryption. If someone gets access to a key/certificate, he or she can easily decrypt the data, and you lose the purpose behind encrypting the data. Therefore, how keys are managed and secured is very important.

SQL Server stores keys in the database. Symmetric keys (where the same key is used for encrypting and decrypting the data) and the private key (the secret part of an asymmetric key) are always stored encrypted in a database. Also, when symmetric keys and private keys are being used and are in memory, they are encrypted in memory, too.

There are three ways to secure symmetric keys and private keys. The first method allows you to specify a password that only you know. SQL Server uses this password to encrypt the key. With this method, it is your responsibility to secure the password. The second method for securing a key is to encrypt it by using a certificate. The third method is to encrypt a key by using another key, which is secured by a password, certificate, or yet another key.

As mentioned earlier, a certificate is nothing but an entity that wraps the public key and is associated with the private key. If a key is secured by using a certificate, it is important that the private key associated with the certificate be secured. The private key associated with the certificate can be secured by using a password or by using a database master key. You can create a single database master key per database by using the CREATE MASTER KEY DDL statement. The database master key itself is secured by using a DPAPI (Windows Data Protection API)-based instance-level secure key called a service master key. The service master key is generated per instance during the installation process and is the root of the SQL Server encryption hierarchy.

Figure 7.3 shows the key management concepts discussed here.

Figure 7.3. SQL Server 2005 provides a robust and secure mechanism to protect the keys and certificates used for encryption.

Let's now look at an example of encrypting data by using a symmetric key. In this example, the key itself will be secured by using a certificate. The certificate will be secured by using a database master key, which will be secured by using a password.

The following code creates a sample database and a table to store encrypted data:

USE master; GO CREATE DATABASE BankDB; GO USE BankDB; GO CREATE TABLE dbo.tblAccount   (RecordID int IDENTITY(1,1) NOT NULL PRIMARY KEY,    AccountNo VARBINARY(100) NOT NULL,    BusinessName VARBINARY(512) NOT NULL,    Balance MONEY NOT NULL); GO

Note

You can use the following formula to determine the size of a column that will store the encrypted data:

Cipher_Col_Size (in bytes) = (FLOOR((8+PT)/BLOCK)+1) *                                              BLOCK + 16 + BLOCK

In this formula, BLOCK is 8 for 8-bit ciphers, such as RC2, DES, TRIPLE_DES, DESX; it is 16 for 16-bit ciphers such as AES_128, AES_192, and AES_256; and PT is the size of plain text, in bytes.

Now you should create a symmetric key to be used for encrypting the data. The symmetric key will be secured by using a certificate, which in turn will be secured by using a database master key. As shown here, you need to create a database master key, and then you need to create a certificate and a symmetric master key:

--Step 1 CREATE MASTER KEY    ENCRYPTION BY PASSWORD = 'WjkKWjKF'; GO --Step 2 CREATE CERTIFICATE certAccount    WITH SUBJECT  = 'certAccount'; GO --Step 3 CREATE SYMMETRIC KEY keyAccount    WITH ALGORITHM = TRIPLE_DES    ENCRYPTION BY CERTIFICATE certAccount; GO SELECT * FROM sys.symmetric_keys; SELECT * FROM sys.certificates; GO

The sys.symmetric_keys and sys.certificates catalog views can be used to view the keys and certificates metadata. The sys.key_encryptions catalog view information related to encryptions of symmetric keys. For instance, the following query could be used to get a list of symmetric keys encrypted using the certificate named certAccount:

SELECT [sk].[name] FROM    sys.symmetric_keys AS [sk] JOIN    sys.key_encryptions AS [ke]         ON [ke].key_id = [sk].symmetric_key_id    JOIN sys.certificates AS [c]         ON [ke].thumbprint = [c].thumbprint WHERE [c].[name] = 'certAccount';

After the symmetric key is created, the data can be encrypted by using the EncryptByKey function. But before you use the key, you need to open the key by using the OPEN SYMMETRIC KEY statement, which prepares the key to be used for encryption or decryption. Once the key is open, it can be used until it is closed or until the session is active. You can execute the following statements to open the symmetric key and use it for encrypting the data.

OPEN SYMMETRIC KEY keyAccount DECRYPTION BY CERTIFICATE certAccount; GO INSERT INTO dbo.tblAccount VALUES   (EncryptByKey(KEY_GUID('keyAccount'), '001'),    EncryptByKey(KEY_GUID('keyAccount'), 'Progressive Sports'),    1000); INSERT INTO dbo.tblAccount VALUES   (EncryptByKey(KEY_GUID('keyAccount'), '002'),    EncryptByKey(KEY_GUID('keyAccount'), 'Metropolitan Sports Supply'),    5000); --Close Symmetric Key CLOSE ALL SYMMETRIC KEYS; GO

If you now look at the rows in the table, you see that the account number and business name columns appear as encrypted data:

SELECT * FROM dbo.tblAccount; GO

To decrypt the data, the key needs to be opened again:

OPEN SYMMETRIC KEY keyAccount DECRYPTION BY CERTIFICATE certAccount; GO SELECT RecordID,        CONVERT(VARCHAR(15), DecryptByKey(AccountNo)) AS AccountNo,        CONVERT(VARCHAR(255), DecryptByKey(BusinessName)) AS BusinessName,        Balance FROM dbo.tblAccount; GO --Close Symmetric Key CLOSE ALL SYMMETRIC KEYS; GO --Cleanup USE master; GO DROP DATABASE BankDB; GO

This time you see the account number and business name columns appear decrypted as plain text.

Note

Note that the EncryptByKey method requires that you specify the key to be used for encryption. However, DecryptByKey does not need the key to be used for decryption. The encrypted block stores the key used for encryption, and then SQL Server looks at all the open keys and compares with the one stored in the encryption block. If that key is open, SQL Server uses it for decrypting the data.

When a database master key is created by using the CREATE MASTER KEY statement, SQL Server encrypts it by using the specified password and stores it in a system table in the user database, which can be viewed by using the sys.symmetric_keys catalog view. At the same time, SQL Server encrypts the database master key by using the instance-level service master key and stores it in the master database. SQL Server uses this encrypted user database master key in the master database for key management, without relying on the user password used to encrypt the database master key. You can change the password used for encrypting the database master key by using the ALTER MASTER KEY DDL statement and specifying the REGENERATE WITH ENCRYPTION BY PASSWORD option. Regenerating the database key leads to decrypting all the keys generated using this database master key and re-encrypting them by using the new database master key. This process can be resource intensive and should be run during off-peak hours.

Tip

It is recommended that you back up the database master key in addition to backing up the data in the database. You can use the BACKUP MASTER KEY T-SQL statement to export the database master key to a disk file. You can restore the database master key by using RESTORE MASTER KEY. Using this operation is similar to regenerating the database master key and hence should be run during off-peak hours.

During the installation, the service master key is generated by using DPAPI, which derives the key from the service account under which the SQL Server service is running. You can regenerate the service master key by using ALTER SERVICE MASTER KEY, which results in decrypting all keys generated using the current service master key and re-encrypting them by using the new service master key. This is a resource-intensive operation and should only be performed during off-peak hours. You can back up and restore the service master key by using the BACKUP SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY statements.

In this section, you have learned how to encrypt data by using passphrases and keys and how key management and security work in SQL Server 2005. The next section illustrates use of certificates to sign a module, which is an alternative technique to implement the module execution context feature described earlier.

Signed Modules

Earlier in this chapter, you learned about the EXECUTE AS clause, which can be used to change the security execution context. This clause, when specified with the modules, fixes the issues with ownership chaining, allowing you to explicitly specify the execution context under which the module should run. This greatly simplifies permission management because you don't have to individually grant and manage permission on the objects referenced in the module. However, this approach has one limitation. Because the module execution context changes to the one specified by using the EXECUTE AS clause, there is no way you can find out who from the list of users having the EXECUTE permission on the module actually executed the procedure. The example provided in the section "The EXECUTE AS Clause," earlier in this chapter, illustrates this by outputting the value returned by USER_NAME().

In summary, if you want to grant access to a resource but only when it is accessed through a particular module, the problem with EXECUTE AS is that it changes the module execution context, which may interfere with your auditing process. If you want to preserve the execution context, you can use a signed module.

To implement signed modules, you have to create a certificate, create a "logical" user associated with the certificate, grant permissions on referenced objects to this logical user, and then use the ADD SIGNATURE T-SQL construct to associate the certificate with a stored procedure, a function, a trigger, or an event notification.

As long as the certificate is associated with the module and the logical user associated with the certificate has permissions on the referenced objects, the users having EXECUTE permissions should be able to successfully execute the module and access the referenced objects through the module. When SQL Server 2005 executes the module and it finds out that a certificate is associated with the module, it adds the certificate to the security token, and by virtue of the presence of the certificate in the token, access to referenced objects is granted for the duration of module.

Let's now look at a signed module in action. The goal is to restrict direct access to a table but allow access to the table via a stored procedure.

You can begin working with a signed module by creating a login and a user named tableOwner, who will have permission to create schemas and tables in the AdventureWorks sample database. The EXECUTE AS statement is then used in the script to change the security context to the tableOwner login, and in this context, a schema and table are created, and a few rows are added to the table. The REVERT statement at the end changes the execution context back to the original login used to connect to SQL Server. You can execute the following script by using Management Studio or SQLCMD:

USE AdventureWorks; GO CREATE LOGIN tableOwner WITH PASSWORD = 'pwd'; GO CREATE USER tableOwner; GO GRANT CREATE SCHEMA TO tableOwner; GRANT CREATE TABLE TO tableOwner; GO EXECUTE AS LOGIN = 'tableOwner'; GO CREATE SCHEMA tableOwner; GO CREATE TABLE tableOwner.tblTest    (col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,     col2 DATETIME DEFAULT GETDATE(),     col3 UNIQUEIDENTIFIER DEFAULT NEWID()); GO INSERT INTO tableOwner.tblTest DEFAULT VALUES; INSERT INTO tableOwner.tblTest DEFAULT VALUES; INSERT INTO tableOwner.tblTest DEFAULT VALUES; GO SELECT * FROM tableOwner.tblTest; GO REVERT; GO

In this example, a user named tableOwner owns a table on which the DBA wants to give access to a user, but only if this table is accessed via a stored procedure. The DBA creates a stored procedure and signs it with a certificate:

CREATE PROCEDURE dbo.sproc_Test AS BEGIN    SELECT USER_NAME();    SELECT SYSTEM_USER;    SELECT * FROM tableOwner.tblTest; END; GO CREATE CERTIFICATE crt_Demo   ENCRYPTION BY PASSWORD = 'JbSSSA'   WITH SUBJECT = 'Certificate to use for signing modules'; GO ADD SIGNATURE TO dbo.sproc_Test   BY CERTIFICATE crt_Demo   WITH PASSWORD = 'JbSSSA'; GO

Now that the module is signed, you can create a logical user associated with the certificate and grant SELECT permission on the table to this user:

CREATE USER userCert FOR CERTIFICATE crt_Demo; GO GRANT SELECT ON tableOwner.tblTest TO userCert; GO

You can now create the user to whom the DBA does not want to give direct table accessonly access through a stored procedure:

CREATE LOGIN demoLogin WITH PASSWORD = 'WjkKWjkF'; GO CREATE USER demoUser FOR LOGIN demoLogin GO GRANT EXECUTE ON dbo.sproc_Test TO demoUser; GO

The user is granted EXECUTE permission on the signed stored procedure that accesses the table. Now you can change the security context to the demoLogin principal and then try to access the table directly and by running the stored procedure:

EXECUTE AS LOGIN = 'demoLogin'; GO SELECT * FROM tableOwner.tblTest; GO EXECUTE dbo.sproc_Test; GO REVERT;

Here the SELECT statement fails, but the user can see the data through the stored procedure. Now you can revoke the permission from the certificate (that is, the certificatemapped user), and you should notice that demoLogin now can't see the data, even through the stored procedure:

REVOKE SELECT ON tableOwner.tblTest FROM userCert; GO EXECUTE AS LOGIN = 'demoLogin'; GO SELECT * FROM tableOwner.tblTest; GO EXECUTE dbo.sproc_Test; GO REVERT; GO DROP TABLE tableOwner.tblTest; DROP PROCEDURE dbo.sproc_Test; DROP USER demoUser; DROP LOGIN demoLogin; DROP SCHEMA tableOwner; DROP USER tableOwner; DROP LOGIN tableOwner; DROP USER userCert; DROP CERTIFICATE crt_Demo; GO

This time, even the stored procedure fails because the certificate-mapped user does not have permission to use SELECT on the table. The DROP statement toward the end performs the cleanup by removing the users, logins, schemas, and table.

An important thing to note in this script is the result of the USER_NAME() function in the stored procedure. Unlike as with the EXECUTE AS clause, the security execution context remains unchanged while using signed modules, and USER_NAME() returns demoUser as the user trying to run the stored procedure. This way, you can avoid changing security execution context and still overcome the ownership chaining limitations.