How to Perform database restore with Database Encryption

Database encryption is the process of changing the information inside database into some ciphertext using encryption keys and appropriate algorithm. This process makes sure even if the hackers get through the firewall and bypass the security, they would require encryption keys to decrypt the information. Database encryption guarantees information security while embeddings or recovering information from the database. By setting up this feature, selective encryption is conceivable and it should be possible at different granularities like sections, tables and so forth. Also, the encryption keys can be put away in a restricted table or file, encrypted by the master key, guaranteeing significantly more grounded security.

Thus, for a database framework, we lean toward a Database Encryption. There are various technologies available for database level encryption, one such feature is Transparent Data Encryption.

Problem: How to restore a SQL Server database with Database encryption feature from one SQL server instance to another.

Solution:
In my last article, I wrote about how can we encrypt specific columns in a SQL Server database table using Database encryption. In this article, I am writing about how can we move a database with encrypted content to another SQL Server instance.

You may find detailed information about how to set up encryption in SQL Server and insert encrypted data in a table in the previous article.

For this demonstration, I am assuming that we already have some encrypted data in the database and we need to move it to another. This article will concentrate on moving the database to another SQL Server instance rather than setting up encryption.

As DBAs, there are many situations where we need to restore databases from one server to another. We may have to do it for UAT or during an upgrade or for any other project requirement. For normal databases, this process is very simple. We can just take a backup from the source server and restore it on another server.

But when a database that has encrypted data in it we need to follow a different process than the normal backup and restore. If we follow a normal backup and restore, restore will succeed. But when we try to access any encrypted data, it will show up as NULL.

Let’s get down to business and try to move a database with encrypted data in it.

I have a database named test in which I have already created a table and inserted some data into it. Only one column of this table is encrypted. This column is PAN.

This query will select the data and decrypt it.

open symmetric key PANsmkey decryption by certificate PANCertificate
select empid,
PAN, PANEncrypted,
convert(varchar,DECRYPTBYKEY(PANEncrypted) ) [Decrypted PAN] from employees
close symmetric key PANsmkey

When I run this query in management studio, I get a result like this.

As we can see, I have a PAN column, its encrypted version and then it’s decrypted PAN. In a real-time scenario, we won’t store the actual PAN. We will only store encrypted PAN using Database encryption.

So, now we have the confirmation that we have some encrypted data in the SQL Server database. Let’s just follow normal backup and restore process and copy this database to another database and see what happens.

This is the command to take the backup of the test database on the source server.

backup database test to disk = 'P:\test.bak' with compression

This will complete the backup as shown below.

Now we will copy the backup file on P drive to the destination server on which we need to restore this database and restore it there. This is the command I used for restoring it on my destination server. We may change the database name, file path as per your environment.

restore database test from disk = 'P:\test.bak'
with move 'test' to 'P:\test_1.mdf',
move 'test_log' to 'P:\test_1_log.ldf'

Now, let’s run the same query that we had ran earlier to get the decrypted data from this newly restored database.

open symmetric key PANsmkey decryption by certificate PANCertificate
select empid , PAN , PANEncrypted , 
convert(varchar,DECRYPTBYKEY(PANEncrypted) ) [Decrypted PAN] from employees
close symmetric key PANsmkey

This is what I am getting. As you can see, it hasn’t decrypted the PAN and instead it is showing NULL.

What’s wrong?

Let’s see the messages tab. This is what I see in the messages tab. We can see the error which occured due to Database Encryption setup.

The first statement is trying to open database master key. But I am getting an error that I need to create either database master key or create it.

But since the same command is working on my source database and I have the database master key in my source database. When I restored the database on the new server, the database master key of the database was copied over here too.

So why is it asking me to create it again? For answering this, we need to refer to the encryption hierarchy of SQL server diagram.

As you can see, database master key is encrypted / decrypted by Service master key. Service master key is associated with the SQL Server instance. When we moved the database, database master key is moved along with that, but service master is not copied to the new server.

So the new server’s service master key can’t open the database master key. Since my database master key is not open, any decryption that I tried on the database doesn’t work.

That’s it. That’s the problem.

So to resolve this, we can either copy first server’s service master key to the new server or encrypt the master database key using the new server’s service master key again.

I am going to follow the second approach. So for this, we need to change our database master key in the original database so that it also supports encryption and decryption by a password and not just by service master key.

STEPS TO TAKE ON SOURCE DATABASE:

First, we need to add a password to the existing master key on the source server. This needs to be executed in the source database that we have to move to new server.

alter master key add encryption by password = 'Pass@1234'

This statement will allow you to open the database master key using the password ‘Pass@1234’ after we have restored the database to the new server. This will not affect any existing encryption since database master key allows to be decrypted by multiple passwords.

Now, we need to drop the existing encryption by service master key.

alter master key drop encryption by service master key

Please note that this step will drop the encryption on your original live database. This means that your application may not work till you add the encryption back again. Please consider the implications of this action thoroughly before you implement this.

This statement will drop any encryption by service master key so that encrypted data is not encrypted by service master key anymore. This will allow us to move the database and still not get bothered by the service master key.

Now, we can back up the database and restore it on the destination server again.

Take another backup of the database. We will need to restore it on the destination server.

But we have dropped the encryption on the source database in this process. We need to add Database encryption back to the SQL Server instance otherwise the application may not work correctly.

For setting up the Database encryption, run the following script.

open master key decryption by password = 'Pass@1234'
alter master key add encryption by service master key

This will add the encryption by the Service Master Key to the source database again and the application should work as it was earlier.

STEPS TO TAKE ON DESTINATION DATABASE:

Now that your original database is good again, now restore the latest backup taken to the target SQL Server instance. Note that this database doesn’t contain the encrypted data. It just contains normal data. But now, we will add encryption to this data using Service Master Key of the new database.

After the restore is completed, now we need to add the encryption by service master of the new server so that data is encrypted/decrypted using the new server’s service master key.

But before we can do that, first we need to open the database master key.

open master key decryption by password = 'Pass@1234'

This statement opens the database master key so that we can now encrypt the database master key using the new server’s service master key. The password is the same password that we added in the first step of this process.

Now that database master key is open, now we need to add encryption using the new server’s service master key.

alter master key add encryption by service master key

Now all the data in the database will be encrypted by database master key and database master key itself will be encrypted by service master key of the new server.

After this process is completed, we don’t need the database master key to open with a password. So we need to drop the encryption of database master key with the password that we added in the first step.

alter master key drop encryption by password = 'Pass@1234'

This command ensures that the database master key will not be encrypted by the password ‘Pass@1234’

Now, when I run the same query on the destination database, I will be able to decrypt the data.

open symmetric key PANsmkey decryption by certificate PANCertificate
select empid , PAN , PANEncrypted ,
convert(varchar,DECRYPTBYKEY(PANEncrypted) ) [Decrypted PAN] from employees
close symmetric key PANsmkey

That’s it. We are able to restore the database on the target SQL Server instance using the above approach.

Conclusion:  So in this Database restoration process, we performed below steps.

• Remove encryption of data from source database
• Backup the database that now contains unencrypted data
• Restore the database on new server
• Encrypt the data back again using new server’s Service Master Key
  Refrences: Microsoft SQL Server Database Encryption