Risk of data loss with Service Master Key using FORCE REGENERATE RRS feed

  • Question

  • Hello all. My company has a live production server on SQL Server 2012 that was recently moved to a cloud environment without backing up the Service Master Key. Now, we've had a few issues come up where (after researching online) the only solution is to force regenerate the SMK. I'm new to SSMS and read that there is risk of data loss when doing that. I've ran queries found here and the only databases with keys/certificates are ones created by SSMS (master, msdb). Does this stand to confirm that I will not lose any live production data if I force regenerate the SMK? Is there anything I should take into account with this? I understand the SMK is important, so I appreciate any info you guys have to offer. Thanks!

    Monday, November 7, 2016 7:39 PM

All replies

  • Are you able to provide more detail of what the issues were that suggests regenerating the SMK?  If the SMK has caused issues then I'm not sure that you have found everywhere that the SMK is being used.

    If you can be 100% sure of everywhere that relies on the SMK then you can make a better judgement on whether it is safe.

    Having  a full backup before doing anything will be important as that is the only aspect that will give you a safe restore point. 


    Martin Cairney SQL Server MVP

    Monday, November 7, 2016 9:45 PM
  • Well, this solution was suggested when we first moved to the cloud and then our linked server connections stopped working. Now, we've since solved this through another method but now we are trying to create a new SSIS catalog and it fails with the error

    "An error occurred during Service Master Key decryption

    Changed database context to 'SSISDB'. (Microsoft SQL Server, Error: 33094)"

    The solution is once again to regenerate the SMK. I'm worried about other possible issues that could arise in the future without regenerating the SMK, and wanted to get as much information as I could prior to doing so.

    I doubt we could ever be 100% sure of everything the that relies on the SMK, but we have full backups and daily incremental backups so maybe it won't be that big of a deal. Hmmm...

    Tuesday, November 8, 2016 2:18 PM