So, your manager wants you to figure out how to encrypt sensitive Data? Well Microsoft has introduced a fairly easy way to configure feature called Always Encrypted.
What is it?
Always Encrypted was introduced in SQL Server 2016. It is an encryption feature that is intended to protect select sensitive data such as credit card numbers and social security numbers. The data stays encrypted not only at rest, but in memory and in transit.
When should I NOT use it?
Not good for encrypting an entire database. Range scan queries will not work as SQL Server can’t do a string search inside the contents of an encrypted column and full text indexes are not supported. A better option would be TDE (Transparent Data Encryption).
How to set it up:
Utilizing the Always Encrypted Wizard is probably the best way to get started with the process.
Open up SSMS, right click a database and select tasks -> Encrypt Columns
Go past the introduction screen:
On the next screen, expand the tables and select the column(s) that you want to encrypt:
After selecting the column, select the encryption type and the encryption key. (If the encryption key is has not yet been created, just leave it as Auto).
Next choose the options for the master key. Autogenerate in this case, and I'm storing it in the Windows Certificate store (though you can use azure as well).
Choose to run now or save as a powershell script (not a bad idea to hold on to the configuration!).
Watch the progress and wait for completion.
Once it's completed successfully, do a quick select from the table and see that all the social security numbers are now encrypted.