MsSQL Server – Migrate Database with CLE (Cell Level Encryption)

Data security is one of the critical aspects of database management. When we are encrypting sensitive data, there are many ways to achieve this in MS SQL Server like CLE, TDE, and Always Encrypted. When you are encrypting data within the cell using a certificate or keys, this is known as CLE (Cell Level Encryption).  In this blog, I have discussed the necessary steps for restoring a database and retrieving encrypted data when CLE (Cell Level Encryption) is implemented.

 Problem: 

1. ERROR: One of the issues you may see is an application or some of the TSQL code which is decrypting the data starts giving the following error.

2. DecryptByKey function is not able to decrypt the data. This will not give you any error however functionality will be impacted due to missing encryption keys.  

Solutions

As we did not open the database master key decryption of data is not possible, also DMK is not encrypted by the current instance service master key hence you see such an issue, we have many solutions to resolve this, so let’s talk about them one by one

Case 1: When database master key encryption password is known.

Step 1:  Open the database master key using a password and decrypt the data.

Step 2: One of the questions that comes to mind is the possibility of opening the key with a password in every session or if you can hardcode this. The answer is no, in such cases, you will re-encrypt the database master key with the current instance service master key and run the following commands as shown in the snapshot.

Step 3: Check if you can decrypt the data without opening the key with a password and the answer is yes because the key is now also encrypted by the current service master key.

Step 4: If you want to change the database master key encryption password you need to complete the following step

Case 2: When the database master key encryption password is unknown.

In case you are not aware the password and source are accessible. Just use step 4 from case 1 to change the password and follow the procedure in step 1. Else use the following steps as an alternative. This is only possible when the instance where the database is getting restored is not using any other key as the step involved here is restoring the instance service master key.

Example: How to take a backup of instance service master key. 

-- Take the backup of the service master key 
BACKUP SERVICE MASTER KEY TO FILE = 'C:\Shared\service_master_key.key
ENCRYPTION BY PASSWORD = 'key_P@ssw0rdGqw0956565

Note: Key backup files are very sensitive from a security perspective hence add an SQL service account in the security tab to ensure the SQL server has access to the file, or else you may get the following error during the restoring process.

`Msg 15317, Level 16, State 2, Line 53`
`The master key file does not exist or has an invalid format`

Step 1: Restore the service master key before restoring the database.

Step 2:  Restore the database.

Step 3:  Check if you can decrypt the data without opening the key with the password and the answer is yes because the key is using the same service master key as we restore both instance service key and database encrypted by it.

Conclusion

Each object in MS SQL Server that is used for encryption is secure by Instance-level Service master keys. For managing encryption, it is important to understand that objects involved in encryption are further encrypted by passwords, Keys or certificates. Every time we are migrating a database with cell-level data encryption, we need to either decrypt the key and re-encrypt it with the destination instance service master key or restore the source instance master key.