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.

tde7

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

tde8

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:

tde9

tde10

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:

tde11

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:

tde12

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

tde13

Hope this helps!

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s