title: Encrypting data in SQL Server or C#
body: <p>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 <em>first name</em>, <em>last name</em>, <em>email</em> and <em>social security number</em>; and that:</p>
  
  <ol>
  <li>Administrators need to be able to view this information.</li>
  <li>Processes on the server need to use the information</li>
  </ol>
  
  <p>...but in case that the database (or a backup) is stolen then the information is encrypted and useless.</p>
  
  <p><em>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.</em> </p>
  
  <p>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?</p>
  
  <p>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?</p>
  
  <p><strong>Edit</strong>: 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).</p>
  
  <pre><code>@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
  </code></pre>
  
  <p>I borrowed some makecert.exe parameters from <a href="https://stackoverflow.com/questions/34047484/increase-validity-of-always-encrypted-certificate">Increase validity of Always Encrypted Certificate</a></p>
  
  <p><strong>Edit 2</strong>: 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.</p>