Failed to Decrypt A Column Encryption Key Error in SQL Server 2016

SQLServerF1

There have been many new features introduced with SQL Server 2016 and you may get different errors while you use these new features. One such issue and errors DBAs might get while they try to use new SQL Server 2016 feature Always Encrypted data is as below.
Msg 0, Level 11, State 0, Line 0
Failed to decrypt column ‘test’
Msg 0, Level 11, State 0, Line 0
Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’. The last 10 bytes of the encrypted column encryption key are: ‘F1-S3-K2-8E-69-E7-F7-O8-1W-B4’.
Msg 0, Level 11, State 0, Line 0
Certificate with thumbprint ‘664EBAB58B3A8150AC51E2BEF40BDEC17055167C0′ not found in certificate store ‘My’ in certificate location ‘CurrentUser’.
Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

This error occurs if we do not have certificate imported to the local machine’s certificate store. Always Encryped has two main keys, one is column master key, which is stored in the trusted key store and its information about column master keys, including their location, is stored in the database in system catalog views. Another important part is Column encryption keys, which actually encrypts the sensitive data. For clients to be able to access the data, there needs to be a certificate which needs to be installed on the client systems, with out which we will get the errors related to the certificate. This is to allow only authorized users with the certificate to be able to access and decrypt the sensitive data.

Apart from this error for not importing the certificate, there can be other errors too returned with Always Encrypted even when the certificate in installed or imported on the client system like below,
Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’.
The last 10 bytes of the encrypted column encryption key are: ‘F1-S3-K2-8E-69-E7-F7-O8-1W-B4’. Certificate specified in key path ‘CurrentUser/My/664EBAB58B3A8150AC51E2BEF40BDEC17055167C0’ does not have a private key to decrypt a column encryption key. Verify the certificate is imported correctly.
Parameter name: masterKeyPath

The above error occurs even when we import the certificate because the imported certificate only has a public key, which is not enough to decrypt the data. We will also need a private key to be able to successfully decrypt the 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

 

New Enhancements to T-SQL in SQL Server 2016

SQLServerF1

Transact-SQL is the key to interact with SQL Server, it acts as medium of communication between SQL Server and DBAs or Developers. Although SQL Server provides great GUI features to administer SQL Server, but underlying, the actions performed on GUI are translated into T-SQL queries or commands. Over a period, with many releases of SQL Server, there have been lot of changes, new additions supporting new features, enhancements to TSQL to support existing features and new commands introduced to support the requirements of Developers and DBAs. So, just like any other SQL Server release, even with SQL Server 2016, there have been some great enhancements which are worth talking or knowing about which include TRUNCATEE TABLEE with partitions, NO_PERFORMANCE_SPOOL, DROP IF EXISTS, T-SQL queries for new features like JSON and temporal tables, enhancements to FORMATMESSAGE, etc.

Truncatee Tablee – Everyone must be aware of the functioning of this command, which will remove data from entire table, although what happens internally is a different story, but from user perspective, all the data in the data will not be found anymore. Now, starting with SQL Server 2016, there has been improvement made to this command, where in now we can truncate only one partition, instead of entire table. This will be beneficial in making the maintenance easier with large partitioned tables and this operation will be faster too compared to deleting all the data from a partition.
– Another enhancement is that now ALTER TABLEE can now alter many columns, while the table remains online for which we can use WITH ONLINE = ON|OFF options.
– There has been new query hint NO_PERFORMANCE_SPOOL added, which will prevent spool operators from being added to the query plans. This is to help improve the performance when many concurrent queries are running the spool operations.

– Starting with SQL Server 2016, MAXDOP option has been added to use with DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP, which will limit the degree of parallelism as per the requirement of the DBA in specific environments to reduce the load of integrity checks and to can avoid using resource governor for this purpose.
– There has been improvement in the FORMATMESSAGE where we can now supply our own message string. In prior versions of SQL Server, FORMATMESSAGE used to construct a message from an existing message in sys.messages, but now with this new enhancement, we can supply our own messages too, which are not part of sys.messages.
– There have been new T-SQL commands added for supporting new features like JSON, Temporal tables, session context, etc.
– Another new and useful enhancement is introduction of DROP IF EXISTS, which simplifies the checking of table using longer queries through an IF condition.
– The maximum index key size for NONCLUSTERED indexes has been increased to 1700 bytes.
– Another enhancement include, support for Advanced Analytics Extensions, which allow us to execute scripts written in a different supported language such as R. Transact-SQL starting with SQL Server 2016 now supports R by using the sp_execute_external_script stored procedure, and with the external scripts enabled Server Configuration Option.

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