locked
SSRS 2008 R2 encryption key question RRS feed

  • Question

  • Hi Experts, I have a production instance of SSRS 2008 R2 with many subscriptions and data sources. I am setting up both a test and BCP version of my prod instance. My question is mainly for BCP but applies to test as well.

    Can I backup my encryption key and restore it to the other instances without breaking the original prod instance? Does the key even work in that way? I really don't want to have to enter in all the information again for data sources and the subscriptions would be impossible to recreate. What other details do the keys contain? Ideally my BCP instance would be identical to prod.

    Thursday, March 27, 2014 5:10 PM

All replies

  • This is rather a Reporting Services question

    What exactly is meant by "BCP"-version? Are you trying to extract the data manually from one server to another?

    This is not the way to go about it. You should extend your whole concept of dev - test - prod also to the deployment process. Then it will make sense to you why not.

    Generally you should be able to restore the Reporting Services databases that way, including restoring the encryption key. The original instance will not notice. How should it ;-)


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, March 27, 2014 5:31 PM
  • My apologies. BCP is our disaster recovery server. basically the reporting database is replicated there. Doesnt seem like the subscription and data sources copies over. I figured we can restore a backup encryption key from the prod system to our BCP to at least get the subscription and data source info. I just didnt want to affect our production box.
    Thursday, March 27, 2014 6:14 PM
  • Okay.

    This should wok.

    Backup of Encryption Key never affects the server. Only a Restore would ;-)

    So for the initial sync you can do it


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Thursday, March 27, 2014 8:44 PM
  • Hi cmunkres,

    production environment shouldn't be affected by backing up keys at all. Backing up of your ReportServer database could affect it slightly depending on settings of ReportServer db. You should be able to restore report server database using report server database and keys, however if you are concerned with subsriptions you'll need to backup your msdb database too, because that's the one where jobs are stored and subsruptions runs as jobs in sql server agent

    Jiri


    Jiri Neoral

    Thursday, March 27, 2014 9:04 PM
  • That is what I am trying to determine. Is if the restore would mess anything up if the same key is restored to 3 different servers. Are there settings within the key that would overwrite things like servers names, or URLs or something that would prevent the original server from functioning as normal it it's key is used on another machine.
    Thursday, March 27, 2014 9:06 PM