Encrypt / Decrypt String

Question

I've been approached with a requirement. One of our devs wants to be able to store information in the database, but once there it needs to be encrypted, so that if anyone ever gets a copy of the database or access to the raw data within it they will not be able to read it. You should only be able to read the data once you have authenticated through the app via SSO.

He seems to think there is a simple function for this, that he can call from the application and decrypt the encryption...

I'm telling him that something like TDE is going to be required here which will take a little while to set up. I have no experience of this, so it will be a learning curve....

In short, the requirement is to protect sensitive information if the database is ever hacked, stolen or mislaid.

Any ideas? Or is TDE the way to go?

Answer 1

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.