SQL Server 2016 - Always Encrypted

Background

Database Security is one of the most important aspects of any application. Traditionally, the security and access to the data has always been restricted to end users via the application interface, i.e., the application has in-built logic to validate the end user and provide access to the data.

But the content of the database which resides on the server has traditionally been accessible to IT Administrator, Developer, and Database Administrator, etc. SQL Server or any database could restrict the access to the database with the help of user and role management and keep log about the database operations. But once the access is granted to the user, the content of the table, etc. is always accessible to anyone who has access to the database. This also includes access to all the sensitive and non-sensitive data.

Following are some of the Database Security Approaches implemented:

Database User/Role Management – The database access is restricted by creating the DB users and restricting the operation performed by the user. Additionally, the log of all the tasks could be monitored.

Transparent Data Encryption (TDE) – also called as Encryption at Rest. As the name suggests, this approach helps secure the physical media (database file, backup file, etc.) if it is stolen, copied by any unauthorized person. TDE performs real-time encryption/decryption on the database and log files. The data stored in database physical file is encrypted and stored. Thus any un-authorized individual would not be able to restore the database until they have the key/certificate.

SQL Server 2016 - New Database Security Features

With the release of SQL Server 2016 – Microsoft introduced two new features for database security, viz.:

• Always Encrypted
• Dynamic Data Masking

Always Encrypted

Always encrypted is the new feature introduced to protect the sensitive data while at Rest (stored physically on the database table) and In Motion (transfer from DB Server to the application). Always Encrypted allows to encrypt the values of certain column.

In a given table, most of the data could be non-sensitive or known to public like First Name, Last Name, etc. But some information within a given table like SSN, Salary, and Date of Birth, etc. is very sensitive data. Always Encrypted allows the application to encrypt the column and store it in the database. Thus even though the IT Administrator or SQL Administrator or Developer have access to the database, they cannot view content in its true form as it is encrypted.

How Always Encryption Works?

Define the Encrypted Column

While defining the database table structure, we define the Encrypted Column by specifying the encryption algorithm and cryptographic key.

Database Engine does not store the encryption key.

Accessing the Database and Table

• To access the encrypted column value, the application needs to access the database via Always Encrypted database driver (ADO.NET) and specifying the decryption key.
• The encrypted column cannot be decrypted on the database side.
• This restricts the DB Administrator to view the columns value. They can query the database but as the values are encrypted, it is safe.

Benefits of Always Encryption

Data Encryption on client side – The data is encrypted and decrypted only on the client side and thus this helps prevent the sensitive data being viewed by DBA, IT Administrator or anyone having access to the database.

Limitations of Always Encryptions

• Cannot execute range-like operations on the encrypted column:
  • Greater/Less than cannot be used
  • Pattern matching ‘like’ cannot be used
• Limitation on Indexing the column
• Currently on .NET 4.6 supports Always Encryption, thus requires migration to .NET 4.6

Moving To Always Encryption

• Database Side
  • Need to create a new separate database with definition of the encrypted column and Master Key creation
• Data Migration
  • Require to migrate the entire database from existing database (non encrypted database) to the new Always Encrypted Database
• Modify the Application to use the .NET 4.6 ADO for Data Encryption
  • Modify the exiting application process and logic to use database driver for Always Encrypted
  • Modify the Stored Procedure which stores the encrypted column value and make the necessary changes

Conclusion

Overall, Always Encrypted is a good step ahead by SQL Server to encrypt and store the data on the server and safe-guard the sensitive information from IT Team, DBA, DB User. At some level now, the business users could be confident that the data is not getting compromised or not viewed by the persons who are not supposed to view it.

But from the Developer or Development team perspective, it might be difficult to test and verify the data being stored correctly at the database level. Currently, a developer would easily verify the data stored and confirm the values stored. With the implementation of Always Encrypted, we would not be able to view the data from the front end.

Once the project starts utilizing the feature, we will surely know the acceptability of the development team and business user. Till then, wait and watch... how this new feature is perceived by the development fraternity.....

Study Reference and Credits

• Always Encrypted
• 'Always Encrypted' Leads New SQL Server 2016 Features
• New Features in SQL Server 2016 – Always encrypted

History

• 20^th August, 2016: Initial version