Checklist: Encrypting Sensitive Data

This checklist helps you confirm that encryption is used appropriately in your environment. Use this checklist to periodically audit your use of encryption with the SQL Server Database Engine.

Database Level

...	Description
	Have you evaluated encrypting data at rest using transparent data encryption? (TDE is available beginning with SQL Server 2008.) Tip For more information, see Understanding Transparent Data Encryption (TDE).
	Do you use symmetric keys to encrypt sensitive data, and asymmetric keys or certificates to protect the symmetric keys? Tip For more information, see Encryption Hierarchy.
	Have you password-protected keys and removed master key encryption for the most secure configuration? Tip For more information, see CREATE MASTER KEY (Transact-SQL).
	Do you have backups of certificates? Tip Use key-specific DDL statements to back up the service master key, database master keys, and certificates. Query the pvt_key_last_backup_date column of sys.certificates. For more information, see How to: Back Up the Service Master Key, and How to: Back Up a Database Master Key.
	Have you backed up your database to back up your symmetric and asymmetric keys? Tip For more information, see BACKUP (Transact-SQL).

Table Level

...	Description
	Is high-value and sensitive information (such as credit card numbers) stored using encryption? Tip Data can be encrypted using column-level encryption or by an application function using the encryption functions. For more information, see How to: Encrypt a Column of Data.
	Have you selected the appropriate encryption algorithm for the data? Tip Move away from older encryption algorithms such as RC4. For more information, see Choosing an Encryption Algorithm.

Transparent Data Encryption

If you are using TDE, you must take care to adequately protect and back up the encryption keys.

...	Description
	If the database encryption key is protected by using certificate, is the certificate backed up, and is the backup of the certificate and the private key file adequately protected? Tip If encryption keys are backed up to removable media (CD or flash drive), store the key backups in a secure location such as a safe with controlled access. If backed up to another hard drive, that computer must be adequately protected. For more information, see Moving a TDE Protected Database to Another SQL Server.
	If the database encryption key is protected by using Extensible Key Management (EKM), is the asymmetric key on the Hardware Security Module adequately protected and backed up? Tip Consult your HSM vendor for recommendations.
	Do you retain backups of old certificates and private keys? Tip When a database encryption key is changed, the virtual log files are not re-encrypted with the new key. They remain encrypted with the previous key. If you must restore a log backup of an encrypted database, you must have the encryption key of all the backup files or SQL Server will not be able to read the files.

See Also

Concepts

Checklist: Enhancing the Security of Database Engine Connections

Checklist: Limiting Access to Data

Checklist: Database Engine Security Configuration

SQL Server and Database Encryption Keys (Database Engine)

SQL Server Certificates and Asymmetric Keys

Other Resources

SQL Server and Database Encryption Keys How-to Topics