Database Engine Security Checklist: Encrypt Sensitive Data

Database Engine Security Checklist: Encrypt 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




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




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.




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.

Other Security Checklists

Checklist: Enhancing the Security of Database Engine Connections
Checklist: Limiting Access to Data
Database Engine Security Checklist: Database Engine Security Configuration

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Page 2 of 2 (12 items) 12
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
  • Rick Byham edited Original. Comment: Fixing misspelled title.

  • Rick Byham edited Revision 1. Comment: Adjusting tags. (adding commas)

  • Rick Byham edited Revision 2. Comment: Adding more tags.

  • Rick Byham edited Revision 3. Comment: Testing fix of link. First on to TDE.

  • Rick Byham edited Revision 4. Comment: Fixing paragraph breaks. First table only.

  • Rick Byham edited Revision 5. Comment: Adding checkmark graphic. First table only.

  • Rick Byham edited Revision 6. Comment: Added remaining check boxes. Fixed remaining links in body. Links to more checklists not done yet.

  • Rick Byham edited Revision 7. Comment: Added one more missed check box.

  • Rick Byham edited Revision 8. Comment: Changing title. adding encryption tag, fixing a link

  • Rick Byham edited Revision 9. Comment: Updating links and adding H2's.

  • Rick Byham edited Revision 12. Comment: Agreed to remove the item regarding removing master key encryption. Misleading in most situations.

  • Ed Price - MSFT edited Revision 13. Comment: Tags

Page 1 of 1 (12 items)