How to View the Data Encrypted Using Always Encrypted in SQL Server 2016

SQLServerF1

Always Encrypted is a new feature introduced with SQL Server 2016. This has been introduced to secure sensitive data in a better way which can port very well with SQL Azure databases data as well. Although there have been other features available prior to SQL Server 2016 for encryption like TDE, SSL and cell level encryption, but the new Always Encrypted feature ensures critical data is not visible to anyone, either SQL Server DBAs, System Administrators, Network Administrators or hackers listening on the network between client and server. This is achieved by Always Encrypted as it encrypts the data both at rest and and also in motion. Important components of Always Encrypted feature as listed below,
Column Master Key – It is an encryption key that protects the column encryption keys. We must have at least one master key before encrypting any columns.
Column Encryption Key – It is the encryption key that actually protects our encrypted columns data.

Most important thing, which allows us to view or work with the Always Encrypted data is the Connection string.
Connection string – For a client driver to understand that column encryption is in use, the connection string must have the attribute Column Encryption Setting = enabled;
As, we must have seen different connection strings which are used to connect to SQL Server from applications and has different options depending up on the type of authentication, additional parameters in case of any database mirroring features being used. So, for Always Encrypted as well, we need to use this additional parameter in the connection string to work with the Always Encrypted data which is Column Encryption Setting = enabled;
Another useful thing with Always Encrypted is that the application code does not need to changed to access this encrypted data, once we use this parameter in the connection string.
We can find the Column Master Key and Column Encryption Key metadata under DatabaseName -> Security -> Column Master Key Definitions -> Column Encryption Keys.

At this point, this feature is not supported by all client libraries. The only provider that currently works with Always Encrypted data is the ADO.NET 4.6, so we will need to ensure that the .NET Framework 4.6 is installed on any machine that will run the client application that interfaces with Always Encrypted data.
– It is important for many developers to be able to view We can use SQL Server Management Studio (SSMS), so when connecting to SSSMS, we can go to Options which will bring additional tabs, then we need to use additional Connection Parameter where we need to specify “column encryption setting=enabled” and once we make the connection successfully to SSMS, we now will be able to view the data.
– But when we try to read the Always Encrypted data, we may receive erros, where encrypted data could not be decrypted, because on the system we are launching the SSMS may not have the certificate which can decrypt the data.
We need to get the certificate installed on the local system with both public and private keys, after which we will be able to successfully view the encrypted data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Always Encrypted Feature in SQL Server 2016

SQLServerF1

It is important for every organization to protect their important customers or company’s data and ensure that no one should be able to view or update the data. There are many security mechanisms in SQL Server which will ensure only authorized users can see the data or make changes to it, like logins and database users to restrict the permissions on what they can do with the data. However, still there are many ways, where in unauthorized users still try to gain access to the data, like tapping the network between SQL Server and client system, trying to hack someone else account and gaining access, etc. So, only having security at login/database user level is not enough, so there are other mechanisms available like SSL certificates to encrypt the data that gets transmitted over the network, encryption of data at individual cell/column level or table level or at database level. Prior to SQL Server 2016, database/cell level encryption, still lets the database administrators or other users with high permissions to still gain access to the encrypted data, as the encryption keys are mostly stored in the database and are managed by the DBAs. This feature will be helpful for Microsoft to promote the SQL Azure databases more to the customers showcasing that their data will only be accessible to the clients, but no one else.

Starting with SQL Server 2016, new feature Always Encrypted has been introduced to safeguard the sensitive data from high privilege users as well as unauthorized users. Always Encrypted feature has been designed to protect sensitive data, such as credit card numbers or national identification numbers which are mostly stored in SQL Azure Database or on-premise SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine like SQL Database or SQL Server. Due to this, Always Encrypted provides a separation between those who own the data (who can view it) and those who manage the data (DBAs, but should have no access to sensitive data). This ensure that on-premises database administrators, cloud database operators, or other high-privileged, or unauthorized users cannot gain access to the encrypted data. This will help in for comfortable delegation of on-premises database administration tasks to third parties or to reduce security clearance requirements for the DBA staff.

Always Encrypted features mainly uses two types of keys which are, column encryption keys and column master keys.
Column master keys – are to protect the keys used to encrypt column encryption keys. It is important for the Column master keys should be stored in a trusted key store. The information about column master keys, including their location, is stored in the database in system catalog views.
Column encryption keys – are used to encrypt sensitive data stored which is stored in the database columns. All the values which are in a column can be encrypted using a single column encryption key. The encrypted values of column encryption keys are stored in the database in system catalog views. We need to store column encryption keys in a secure/trusted location for backup.

Another important component of the Always Encrypted feature which plays a key role is Always Encrypted enabled driver which ensures the transparency of encryption to client applications. This Always Encrypted enabled driver calls a server i.e., makes a roundtrip for each query with parameters to retrieve information on how to encrypt query parameter and whether they should be encrypted. This driver then calls a key store provider to decrypt the encrypted column encryption key value. The resultant plaintext column encryption key values are cached. Query results containing data from encrypted columns are accompanied by encryption metadata to enable transparent decryption. So, when an Always Encrypted enabled client driver queries encrypted columns, SQL Server sends the information about encryption settings for the queried columns, including encryption type information. The encrypted value of column encryption keys are used to protect the data in the queried columns, and also the location of the corresponding column master keys. The driver uses that information to, contact the key store containing each column master key and decrypt the column encryption keys, encrypted with the given master key. If for some reason, Always Encrypted is not enabled on the client side, the driver returns encrypted values and the values have the varbinary(max) data type.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings