SQL Server has had Transparent Data Encryption since 2008 to ensure that the database’s data at rest was encrypted, but the data was still available in plain text if you were able to connect to the SQL Server instance and query the database. It was possible for developers to add code to their application to encrypt the data and then store that encrypted data in the database and then write code to decrypt the data from the database when they read. But this is error prone and developers could forget to encrypt and decrypt the data in their code.
Always Encrypted takes the encryption and decryption tasks away from the developers and puts them into the ADO.NET connection where they are applied automatically to the appropriate columns in the database. Always Encrypted also takes away the responsibility of the key management from the DBAs because they only receive the encrypted column encryption key from the security team eliminating the possibility of the DBAs being able to see data they shouldn’t be able to see.
There are two different types of encryption available in SQL Server 2016:
- Deterministic Encryption: if you encrypt the same value multiple times you will get the same encrypted value. While this is less secure than the random option, it will allow for the encrypted columns to be used for joins between tables, equality operators in the where clause, GROUP BYs, and DISTINCTs.
- Randomized Encryption: it is more secure than the deterministic option because if you encrypt the same value multiple times you will get different encrypted values stored in the database. But this security comes at the price of not being able to do things with the data other than reading it. Things like GROUP BYs and equality operations are not supported.
While the Deterministic Encryption does allow for some operators to be applied, there are a number of common operations that are not supported such as range searches, pattern operations, arithmetic operations, etc. The operations that are not supported will cause the query to fail.
These different types of encryptions do force you to understand your system’s workload and how applications query your data before you implement it. If there are data analysts that have access to query the database outside of your application, they will be querying encrypted VARBINARY columns instead of the unencrypted correct datatype that the application sees.
If you have an existing database that you want to apply Always Encrypted to, you are going to have to take an outage. The encryption happens in the ADO.NET connection, so the data will have to be exported out of the database and then imported back into the database through the ADO.NET connection.
Row Level Security
Row Level Security has been available in Azure SQL Database as of V12 and is now making its way into on premise SQL Server. Instead of creating your own views on tables and making users and applications query those views, the users and applications can now query the table and SQL Server will apply the row level filtering by applying the security predicate function to all queries against that table. If your application connects to the database using the user’s login the implementation of row level security will be seamless to the application. This also works great in situations where users can connect directly to the database with tools like Excel and have their results filtered correctly.
Unfortunately, there is no integration with SQL Server Analysis Services, so if you are using row level security in your database, you will have to duplicate the security logic in the cube to achieve the same filtering in SSAS that you will have in SQL Server.
Dynamic Data Masking
Dynamic Data Masking applies masking rules for columns and when the data is returned to the user the masking rule is applied. For example, you can use Dynamic Data Masking to show only the last four digits of social security numbers or credit card numbers to end users. The masking rules are applied based on the individual logged into the database, so you can mask data for some users and not others depending on their role within the organization.
Because the data is masked on the fly, it doesn’t impact your ability to query data. For example, if your business doesn’t want to display account numbers, but the first four digits of the account number identify the type of account, you could still query your data for accounts that start with 1234 and the rows returned will be accounts that start with 1234 and the account number will be masked appropriately to the end user.
Because Dynamic Data Masking works on the fly, it is ideal for situations like restoring a copy of production for your developers or for a demo environment. The developers or users of the demo environment get a usable environment but are not able to see any PII and you no longer have to worry about a developer forgetting to run the old masking script against the restored data or the old masking script getting out of date as new data is added to production.
Unfortunately, with dynamic data masking being applied at the database and Always Encrypted being applied at the ADO.NET connection, the two security features cannot be combined.
These new security features in SQL Server 2016 will make it easier for me to help my clients address their security concerns. These features empower me to more securely and completely address the concerns of my clients.