Always Encrypted Feature in MS SQL Server 2016
The latest release of SQL Server, i.e. version 2016 introduced with it a new method of data security. The measure offers to secure particular columns with encryption-based security that is known as SQL Server Encryption. The option enables data encryption on application layer with the help of ADO.NET. Therefore, you are able to secure your data before sending it over the network to SQL Server using the .NET application. The following article is a descriptive segment explaining the architecture and setup of table storing data with always encrypted.
Architecture of Always Encrypted in MS SQL Server 2016
SQL Server Always Encrypted architecture has the program carrying out the column level encryption before the sending out the confidential columns over to SQL Server. Actual encryption is performed using ADO.NET drivers on a client machine or application.
Before the .NET application proceeds with sending plain text contents to ADO.NET, it ensures that the data is encrypted before sent to SQL Server. Only change that has to be made by the application for keeping encrypted data is; change of connection string for indicating that the column level of encryption has been enabled.
Once the column encryption has been enabled, columns will be encrypted with Always Encrypted before they are sent to the SQL Server. In addition, it will also decrypt Always Encrypted columns when SQL Server is being used for reading them.
Applicable Scenario: Being an MS SQL Server Database user with a client application operating at the same time on-premise, a user would expect some help. However, sensitive data on the server could be a threat to the confidentiality of the storage when hiring an external help for administering the server. Therefore, in such a case the sensitive data can be protected on SQL Server with Always Encrypted. The function ensures that duties are evenly separated between both; the database and application administrators respectively.
The SQL Server Always Encrypted key related plaintext values can be stored within a trusted key store. Only the client application has access to this machine, therefore, ensuring the confidentiality. Server administrators in this case get no access to the keys making it impossible for them to perform decryption of sensitive data stored on the Server.
The Limitations Observed
In case, a column has been encrypted all range-like operations are disallowed, like:
- Greater/less than
- Pattern matching using LIKE, etc.
On top of that, you will no longer be able to pass on values that are encrypted to functions whether they are user-defined or not. This happens because the database is not provided the access to values available in a non-encrypted state. Other than this, the following limitations are to be surfaced:
- The only time equal comparisons can be executed on the columns that are in use of deterministic encryption.
- Deterministically encrypted columns are the only ones to which indexes can be applied.
- Data types that will be unsupported are: XML, image, geography, user defined, ntext, sql_variant, text, geometry, etc.
- The only driver that can successfully support Always Encrypted feature is .NET 4.6
- When performing a join between two different columns it is necessary that the same encryption key be used in the process.
Keeping in mind the entire detailing regarding SQL Server Always Encrypted feature introduced in version 2016 it is important that users make use of it wisely. The listed limitations make it easier for users to choose when to use the feature. Moreover, architecture explained in detail along with a supposed scenario helps moreover in understanding the potential as well as important of Always Encrypted in SQL Server 2016.