Encrypting data in SQL Server or C#

Question

I'm looking into on how to encrypt selected user data fields on a web server, and would like suggestions on how to achieve the best results. For an example, say that a user inputs his/her first name, last name, email and social security number; and that:

1. Administrators need to be able to view this information.
2. Processes on the server need to use the information

...but in case that the database (or a backup) is stolen then the information is encrypted and useless.

One way, I guess, is to generate a private+public key, store that into the Windows Certificate Store, mark it as Not Exportable (keeping a backup of course), and then use that in the application layer to encrypt/decrypt fields as they are needed.

I'm using SQL Server 2016 and .NET 4.6 for this, but also have other dependencies, such as Reporting Services that I'd like to support. - Is perhaps "SQL Server 2016 Always Encrypted" the way to go? Do you have any practical experience to share about using it?

I guess that if an intruder gains administrative control of the server (a Windows 2016 IIS + SQL Server combo) then there isn't much that can be done. Or is it?

Edit: I decided to go with Always Encrypted built into SQL Server 2016. This is how I ended up generating the master key, that I then install in the Local Machine certificate store, and then setup as a Column Master Key in SQL Server (with a simple SSMS right click on DB > Security > Always Encrypted Keys > Column Master Keys).

@echo off
echo creating certificate (you might want to set a password on this)...
makecert.exe -n "CN=MyCompany Always Encrypted Master Key 2017" -r -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -sky exchange -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 -len 2048 -a sha256 -e 01/01/2040 -sv MyCompanyAlwaysEncryptedMasterKey2017.pvk MyCompanyAlwaysEncryptedMasterKey2017.cer

echo merging to pfx (please check to export private key and set a password)...
pvk2pfx -pvk MyCompanyAlwaysEncryptedMasterKey2017.pvk -spc MyCompanyAlwaysEncryptedMasterKey2017.cer

echo done! install your new .pfx into the localmachine certificate store and create a reference to it in sql server!
echo note, backup the .pfx - the certificate private key is marked as non-exportable once installed into the certificate store.
pause

I borrowed some makecert.exe parameters from Increase validity of Always Encrypted Certificate

Edit 2: After a couple of days working with this, we decided to NOT go to production with Always Encrypted, since after enabling it in SSMS (and Always Encrypted Parameterization), some queries completely hang, and we see lots of other issues coming up: For instance that every client (ASP.NET, SQL Server Jobs, Scheduled Tasks, Reporting Services tasks -- and for every query in there that does a JOIN or a WHERE on an Always Encrypted column), that query needs to be parameterized... Even though we do that a lot - we have more creative queries (non-school book queries) that are not possible to parameterize in such a way. And then what happens with LIKE '%xxx%' etc. or any type of advanced TSQL I don't even want to think about. Perhaps I would reconsider Always Encrypted for a new solution, or then again no. This isn't it.

Answer 1

There are two ways you can achieve this,

1. Using Transparent Data Encryption feature in SQL server, which will encrypt the data at rest. This means that data will be encrypted on disk by SQL server, the data is visible in plain text on the network between client and SQL server and it is visible in plain text in the memory of SQL server. You can read more about this feature here.

2. Using Always Encrypted feature in SQL server, which is a client side encryption technology, meaning it will encrypt the data in the driver of your client application. This means the data will be encrypted on the network between the client and SQL server, it is encrypted in the memory of SQL server and it is encrypted on disk. If implemented correctly, it can also protect the data from an intruder that gains admin privileges on SQL server (basically you will have to ensure that the encryption key is not stored on SQL Server and the intruder cannot get access to the encryption keys in any manner). I explain how to do this and the security guaranty provided by Always Encrypted here, in some detail. However, since SQL server cannot decrypt the data, there are some limitations on what operations you can perform on encrypted data. You can read more about those limitations here, https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine. You can learn more about always encrypted here.