Return to Revisions

protect sensitive information if the database is ever hacked, stolen or mislaid.

This is a very broad requirement.

Encryption comes at the cost of CPU cycles, so better load test your application if the outcome is acceptable to your application performance.

There are areas to explore and combination of them will get you want you want.

• Encrypting data at rest - this is done using TDE (Transparent data encryption). I have written about my experience embracing TDE in our environment here.
• Encrypting all connections to SQL Server using Force Encryption. This is done using loading certificate on the database server, enabling force encryption (will require sql server service restart !) and granting read access to sql server service account to the certificate. You can verify using sys.dm_exec_connections --> column encrypt_option will be TRUE.
• Column level encryption - protecting sensitive data - see this article.
• If you are going to upgrade to SQL Server 2016, you can explore Always Encrypted option.

On the other hand, make sure you follow the security best practices (warning: its word doc !) with regards to sql server service accounts, logins & users, port allocation, etc.