Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Tuesday, March 20, 2012

Restore ReportServer Database

Accidentally the key tables were deleted from our ReportServer SQL Database.

We tried copying the tables over from the ReportServer TempDB, but this then broke the encryption key (which we did not have access to the backup of).

We then ran rsconfig -c to restructure the db connection and generate a new key. But unfortunately our old data was encrypted by the old key.

We are also getting the following error on the http://localhost/Reports page:

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.6.51'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)

The recomended fix for this error is to reinstall Reporting Services from scratch.

We were hoping that there maybe another way around this, preferably away we can just restore from he TempDB.

Thanks for your attention.

The tempdb is not a copy of the main report server database. It contains a different set of data. The main database stores report defintitions, data sources, security settings, schedules, etc. The tempdb stores dynamic data , such as report snapshot that are current being viewed. You can't restore one from the other.

If you don't have a backup of the encryption key, you will need to delete all encrypted content in the database via the config tool. If you have lost your entire report server database (which sounds like it is the case) and don't have a backup, you will need to rebuild it from scratch.

Saturday, February 25, 2012

Restore Master Key

Do we need to know the database master key password inorder to restore the same from a Master Key backup ?

What if the scenario is like, I have Master Key backup file but do not have the password specified at the time of creation ?

Any help is appreciated.

satya

You need the password. If you don't have it, you cannot use the backup.

Thanks
Laurentiu

|||

Thanks

I needed just a clarification. We are actually trying to develop an implementation strategy for encrypting data in SQL 2005.

satya

|||

Quick Follow-up on this. This is what exactly I am trying to test.

1. Create Master Key with password

2. Create certificate (encrypted by master key)

3. Create symmetric key (encrypted by certificate)

4. insert encrypted data using encryptbykey built-in

5. Test decryption using built-in decryptbykeyautocert

Once this all is working fine, do these backups

1. Backup Master Key

2. Backup Certificate as .CER file and private key as .PVK file.

Drop the these following security objects

1. Drop symmetric key

2. Drop certificate

3. Drop master key

Restore keys from backups

1. Restore master key

2. Create certificate from backups

3. Create symmetric key

I receive no errors while performing these T-SQL statements, but I am no longer able to view previously encrypted data. Decrypted values showing as NULL

Do you think, I am trying to do something wrong here ?

thanks

satya

|||

To recreate the same symmetric key that you used for encryption, you need to use the same algorithm, identity_value, and key_source in the CREATE SYMMETRIC KEY statement. Also take a look at: http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx, the last pargraph is relevant to this.

Thanks
Laurentiu

|||

Works like a charm !! Thanks.

Only concern, I have is that we need to remember little more things for recover process to work perfectly :)

Is there any white paper yet on "Recocering Encrypted databases".

thanks

satya

|||

You could store those pieces of information in a separate table encrypted by a certificate.

What exactly would you like covered under the title "recovering encrypted databases"?

Thanks
Laurentiu

|||

Most of it I can find it in your blogs. May be I am looking for a consolidated one.

Key Management - Best Practices in symmetric key encryption, asymmetric encryption (or using certificates) and hybrid approach (Symmetric Key getting encrypted by certificates)|||

For the FORCE option, see http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx. It's not an option to bypass the password protection.

I'll transmit your suggestions to our documentation team, so they can look into adding new articles on these topics or update existing articles and add more information.

Thanks
Laurentiu