Microsoft SQL Server 2016 – Always On Encryption helps protect your data
Microsoft has clearly placed data privacy and security front and centre in their development of SQL Server 2016. Due to the countless reports of data hacking seen over the last few years, every organisation is examining their data security shortfalls and the new release of SQL Server goes a long way to resolving some of these issues.
In my previous post I reviewed the Dynamic Data Masking feature allowing queried data to be obfuscated at the database layer as opposed to the application layer. Another major security feature is Always On Encryption. Here are sum of the pros and cons of this security feature.
Data is encrypted at all times
This might seem obvious but let’s look at what this really means. Data for one or more columns of a table are stored in a permanent 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 transfer 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 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
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 aggregation or pattern matching features such as LIKE, SUM or SUBSTRING etc.
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.
In conculsion, combining DDM, TDE and Always On encryption is an excellent starting point of securing your client and employee data in this age of security breaches and active data hacking. This should not been seen as a complete solution but a basis for your organisation's security protocols.