SQL Server 2016 now supports a driver based encryption called Always Encrypted. In essence, columns in your database tables may be encrypted, while working with them as the original un-encrypted data type from your application. The encryption works during the transport of the data from the application to SQL Server. This makes the database behave as fully encrypted when using tools like SQL Server Management Studio, or even a custom ADO.Net application that is not enabled with the keys for enctyption and decryption. Only your application has the encryption key, and may see the data in its original form. To all other clients, the data is encrypted. Here is a paragraph from Microsoft:
Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). By ensuring on-premises database administrators, cloud database operators, or other high-privileged, but unauthorized users, cannot access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.
What makes this nice is that if you are already using parameterized queries, there is little you need to do to your application to enable this feature. Working with encrypted data does not work with inline text. You must use ADO.Net parameters. It also requires the use of ADO.net to enable the encryption. I don’t have any input on Entity Framework support at this time. For most of us who are concerned about SQL Injection, using parameters is not an issue.
There are some issues with comparison of encrypted values. You can find them at https://msdn.microsoft.com/library/mt163865(v=sql.130).aspx under the section labeled Feature Constraints. To summarize, there are limits to working with the encrypted data. You can only test for equality. Other comparison operators are not supported. It looks as if indexing is also not enabled if you are using randomized encryption, which provides more security.
Having worked in the credit card industry, we had to implement this capability ourselves. Data was stored internally as a VARBINARY data type, which allowed for only equality comparisons. Sure, you could do > or <, but it was only evaluating the HEX encrypted data, not the actual contents you would wish to compare. A Couple of notes: 1) The encryption capability is only supported in ADO.Net using the SqlClient in .Net Framework 4.6.
2) There are some deployoment issues regarding access to the encryption keys.
Without taking it out for a spin, I’d say this is a pretty good idea. You aren’t losing any functionality from most implementations you might do yourself, and this appears to be quite painless. Are you testing this capability in the CTP version of SQL Server 2016? Drop a comment with your experience with Application level SQL Encryption.
Cheers,
Ben