Enterprise TechSQL

SQL Server 2016 : Security Features

SQL Server 2016 Is Faster Than Ever

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:

Note: If you buy something from our links, we might earn a commission. See our disclosure statement.

Always Encrypted


  • 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.

SQL Server 2016 Always Security Always Encrypted

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.
SQL Server 2016 Always Security

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

Creating and registering a custom Column Master Key Store Provider

Row Level Security


  • 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.

SQL 2016 Row Level Security RLS

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.)

Dynamic Data Masking for SQL Server 2016

For a complete list of enhancements, visit the Microsoft Site.

Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.

What's your reaction?

In Love
Not Sure

You may also like

Comments are closed.