Implementing Transparent Data Encryption Part-2

In this post I will show how the encryption actually works. I have backedup theAdventureworks2008R2 database on which I performed encryption.


Now I will login to another instance of SQLServer and will try to restore the database that I just backed up.


Now we get an error that SQLServer Cannot find server certificate with thumbprint ‘0xE138B309A65069E6286242E8973F75D4ED3B7F11’.

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 33111)”

So, in order to restore an encrypted database we need to first export the certificate we created on the instance on which the encrypted database backup was created and also a new master key for the new instance has to be created. The certificate is imported as shown below:



The certificate and key are now copied to the new instance server. The new master key needs to be created on the new instance and this is as shown below:


After the new master key is created we create a new certificate by importing from the certificate and key we just copied. This is as shown below:


Now if i try to restore the database it works without any errors as shown below:


Hope this helps!

This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s