September 2011 – Page 3

In this Blogpost, Actually I thought of showing few hidden gems in our our Fabulous SSMS standard Reports, but ran into one of the interesting issues when i was trying to open REPORTS from SSMS. You can see the error Message below in the window.

Please note, I’m running on SQL Server 2008 SP2! So it has nothing to do with the Bugs or Service Packs or even any Cumulative updates installed!

How to Fix this Issue in my case?

I remember not Installing complete Client tools on this Server while I was installing SQL Server. You can see the below Screenshot where BIDS and other Client Components(I Installed only SSMS initially) being missing on this Server.

Let’s Install BIDS(Business Intelligence Development Studio) and see what SSMS say when we try to bring up Reports!

As you can see once I Installed BIDS, I was able to open Reports Successfully 🙂 Also, you can verify a folder called “ReportViewer” Being created in Microsoft Visual Studio Folder in “program files” Directory as seen below.

Hope this helps when you get into a similar Issue!

Until now we’ve seen what TDE is, how to enable it on a Database, what are the key considerations which we should never forget as a DBA while dealing with Encryption stuff..bla bla…!! In this post of the Encryption Series, let’s actually see how to Restore a Backup of a database which has TDE enabled on it.

As i mentioned earlier, we can’t restore a backup on a different instance without Certificates. We’ve to restore the Cert from Source Server , then we should be able to restore the Database from the Backup.

Note: Enabling TDE on a Database encrypts every single portion of the database. It includes mdf, ldf and any ndf’s as well. All your Database Backups are encrypted as well once you turn ON TDE!! Heyyy…that’s awesome!! One thing you should be aware of is, If you are using FileStreams, they are not going to be encrypted with this TDE.

My Scenario: I’ve a Encrypted Database “Adventureworks” in my PROD Instance and i took a Full backup of it(Adv_TDE.bak). Let’s try to restore on my DR Instance as a brand new Database. As you can see below it is throwing “certificate related error Messages” when i am trying to restore the database.(You’ll get the Same error message if you try to use detach/attach method or Copy DB Wizard)

To keep it real simple I’m taking all the defaults(which will fail in real case without MOVE) while restoring Database in my T-SQL Restore Syntax. Remember this blog post is not about normal DB Restores Syntax(es) 😀

Soo…..what should we do???

All We’ve to do is restore our Certificate from source(And you have to create a DMK on destination if you don’t have one), Once you are done with that piece of puzzle, the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s)! So..Basically all we need to do is creating the certificates on the destination Instance from the backup of your certificates which we created in earlier post.

On DR Instance:

Create a Master Key(You don’t have to do this if you already have DMK) and Create a certificate from the Cert Backup and providing Private Key file and Password to decrypt it as shown below

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DIFFERENTSTRONGPASSWORD'
GO
CREATE CERTIFICATE TDEDEMOCERT 
    FROM FILE = 'D:\Backs\TDEDEMOCERT.BAK' 
    WITH PRIVATE KEY (FILE = 'D:\Backs\TDEkey.bkey',
    DECRYPTION BY PASSWORD = 'VeryStrongPassword');
GO 

/*the Certificate encryption piece on PROD was
BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
TO FILE = 'D:\Backs\TDEDEMOCERT.BAK'
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )
GO
*/

Now let’s do the actual restore and see how it goes….

Perfect!!! Once we created certificates based on the Cert backup from PROD Instance, we were able to do Restore!! Soo….Key thing to remember is to Backup your certificate and store it in a safe as soon as you’ve Encrypted your database. I want to stress again – If you loose it, you lost everything related to your Database(You are safe as long as you can access the source database)!!!