Database Engine Security Checklist: Encrypt Sensitive Data
Article
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.
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 Install asymmetric keys obtained from a CA and store it securely; use KEY_SOURCE and IDENTITY_VALUE with CREATE SYMMETRIC KEY and store the statement securely for symmetric key recovery. For more information, see BACKUP (Transact-SQL).
Table Column/Cell 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 Use the AES algorithm when possible and Triple-DES for legacy application compatibility; other supported algorithms are less secure. For more information, see Choosing an Encryption Algorithm.
Are you using hashes to store passwords and other secure information that doesn't need to be returned as plaintext? Tip Use SHA-1 hashes for secure applications, see HashBytes function.
Have you included symmetric encryption key recovery in your Disaster Recovery plan? Tip Symmetric keys cannot be exported/backed up from the database; instead using the KEY_SOURCE and IDENTITY_VALUE arguments of CREATE SYMMETRIC KEY allows you to recreate your symmetric keys in a DR situation. See CREATE SYMMETRIC KEY for more information.
Are you using authenticators to add even more security to your encrypted data? Tip Authenticators can be used to prevent wholesale replacement attacks on your data. See ENCRYPTBYKEY for more information.
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.
