The upcoming SQL Server 2016 release promises many new features including a “Stretch” feature which allows you to automatically archive older data to the cloud, enhanced in-memory OLTP functionality, and several new enhancements in security. One of the most interesting new security features is Always On Encryption. Here are 6 things you should know about this feature.
- Data is encrypted at all times
Okay, so this might seem obvious but lets look at what this really means. In the diagram above you see that the data for one or more columns of a table is stored in an encrypted state. When SQL Server acts on this data locally it acts only on the encrypted version. It never decrypts it and so it’s encrypted in memory as well as on the wire as it transits the network (or Internet) on the way to the client. SQL Server treats the encrypted data as if it were the raw field. Only at the point where the data reaches the client is it decrypted for use in your applications. This makes the encrypted data nearly impervious to man-in-the-middle attacks or file based decryption on the server. - Encryption keys are not stored on the server
SQL Server does not hold the keys to be able to decrypt the data it stores in Always On Encrypted fields. While you do register on the server where the certificate should be found on the clients, the actual certificate is not accessible on the server. The client can store the encryption keys currently in the local certificate store and in time in Azure Key Vaults or Hardware Security Modules. One factor to consider is that SQL Server DBAs will not be able to view any of the encrypted data during migrations, imports, etc. The natural tendency will be to install the client certificate locally on the server but that would negate much of the security of Always On Security by giving a hacked server access to the encrypted data. - Always on Encrypted columns support equality operators only in version 1
Because the data is encrypted in SQL Server only full column equality is supported. That means you can use equality in WHERE, JOIN, and GROUP BY clauses but you can’t use LIKE or other aggregation or pattern matching features like SUM, SUBSTRING, etc. - You’ll need to upgrade your client software to .NET 4.6
As the encryption and decryption is done at the client, your clients will need to upgrade to the new version of the .NET framework which is 4.6. Version 1 currently only supports SQL Server Client driver but ODBC and JDBC drivers will be coming at a later date. - This is not TDE
TDE (Transparent Data Encryption) is a feature of SQL Server that encrypts the data files themselves on the server. This is basically encrypting your entire database. While this provides security for stored or “at rest” data, once the file is decrypted by SQL Server the data remains decrypted in plain text in memory and when it’s sent across the wires. Always On Encryption will encrypt the data in memory and on the wire but due to the performance hit on the client it’s not intended to secure every field in a table or database as TDE does. So you’ll need to consider this when you decide which security feature to implement. - Encrypted columns take significantly more space
While columns defined with Always On Encryption specify the size of the original decrypted data they actually store the encrypted value which can be much larger. This is something to consider when deciding how many columns to store as well as how big the columns you want to store are.
If you want to know more about SQL Server 2016 Always On Encryption watch this Channel 9 video. There are lots of new features coming in SQL Server 2016 and you’ll be able to learn more about them here at Wintellect DevCenter. So stay tuned!