The last couple of SQL Server releases were about Cloud Integration and Hybrid deployments. The current SQL Server release has closed the gap between the features offered on the cloud vs. on premise. Both (Azure SQL and On Premise) now share a common code base, and most of the new Azure SQL features like Row Level Security, Always Encrypted, Dynamic Data Masking etc are now available on SQL Server 2016 On Premises. I have already spoken about the Server level performance features in a different post. So let’s see what we have in security features:
Always Encrypted
Benefits:
- Prevents Data Disclosure: This is a Client-side encryption of sensitive data using keys that are never given to the “DBA” who is managing the database system or anyone else who have direct access to the Server.
- Queries on Encrypted Data: More T-SQL surface area with support for equality comparison, incl. join, group by and distinct operators.
- Application Transparency: You would need .NET 4.5 or above and with Minimal application changes via server and client library enhancements.
Data remains protected from high-privileged, yet unauthorized users.
Source: https://msdn.microsoft.com/en-us/library/mt163865(v=sql.130).aspx
There are two types of Encryption available:
- Randomized encryption uses a method that encrypts data in a less predictable manner.
- Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value.
Source: https://msdn.microsoft.com/en-us/library/mt163865(v=sql.130).aspx
How to Create and register a custom Column Master Key Store Provider
Row Level Security
Benefits:
- RLS enables you to implement Fine-grained access control over specific rows in a database table.
- It can help you prevent unauthorized access when multiple users share the same tables, or to implement connection filtering in multi-tenant environment which is quite common these days.
- Fully integrated for you to Administer using SQL Server Management Studio or SQL Server Data Tools (SSDT).
- The access restriction logic is located in the database tier and enforced inside the database and schema bound to the table.
No Application Changes!
Dynamic Data Masking
- Dynamic data masking limits sensitive data exposure by masking it to non-privileged users based on “Policy-driven” at the table and column level, for a set of defined users.
- This is applied in real time based on policy. You can designate how much sensitive data you want to reveal with minimal impact on the application code that might else have to be re-written.
- There are multiple masking functions available (e.g. full, partial) for various sensitive data categories (e.g. Credit Card Numbers, Employee Salary, SSN Numbers for US etc.)
For a complete list of enhancements, visit the Microsoft Site.