Always Encrypted an Intro
Always Encrypted is a new features introduced in Azure SQL Database and SQL Server 2016. Encryption is not new to SQL Server, it was introduced in SQL Server 2005 but this feature provides by far the best possible security for all those data owners who have outsourced the work to manage their servers to a third party vendor. This feature makes sure the data is encrypted both at rest as well as while in motion, means the data is stored as encrypted and also remains encrypted even in memory. So by doing this not even the SysAdmins of SQL Server can see the data provided you are able to implement it correctly. Always Encrypted makes encryption transparent to applications.
This is quite flexible feature which allows you to select columns to be encrypted. The encryption and decryption takes place within the ADO.NET client app so the data always travels encrypted. Since the encryption\decryption happens at the client end your app must be on the supported version of .Net which is ADO.Net 4.6 essentially you need Dot Net framework 4.6 or higher in order to support Always Encryption at the client side.
MSDN extract: “An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.”
Always Encrypted Basics
In order to configure Always Encryption you must create these set of keys in your database which provide the base of Always Encryption.
Column Master Key: This key helps you encrypt column encryption key. You must have at least 1 column master key. You can however create more than 1 column master keys in order to rotate them. It’s a must to create column master key metadata entry before you can create column encryption key metadata entry in the database and before any column in the database can be encrypted using Always Encrypted.
Column Encryption Key: This is the actual key which helps protect the data, this key is actually encrypted by column master key. Any column encryption key can hold up to two values which allows column master key rotation.
Choosing the right encryption type
Always Encrypted provide two type of encryptions in order to encrypt your precious and super critical data those are Deterministic and Randomized.
Deterministic Encryption: This generates every time the same encrypted value for a plain text, means if you pass ‘sarabpreet’ 5 times you’ll get same encrypted value. This enables SQL Server to be able to use these columns in filters (where clauses), point lookups, equality joins, grouping, indexing etc. But this also leaves a risk of guessing the actual values specially when there are only handful of possible values in this column like Male/Female, yes/no, etc. If you want to use deterministic encryption type make sure you use binary2 type collation of encrypted column for all character data type columns (like char, varchar etc)
Randomized Encryption: This is non predictable since it will always generate a unique value for the same text, means even if you pass the same value “Sarabpreet” 5 times you’ll get a different encrypted value each time which makes it difficult to predict what value was passed\stored. Randomized encryption is more secure but doesn’t lets you query the data basis of these values like point lookup, join etc.
Special Note: Make sure you run key provisioning or data encryption tools in a secure environment, on a computer that is different from the computer hosting your database. Otherwise, sensitive data or the keys could leak to the server environment, which would defeat the purpose of configuring Always Encrypted.
Hope you got answer to your questions! Happy Learning!
feel free to leave a comment. 🙂
Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp
Follow @Sarab_SQLGeek