Freigeben über

Recover the SSISDB to a New Machine Without a Database Backup

  • Artikel

It's Not Pretty!

Imagine your SQL Server instance, which contains your very important SSISDB fails.  You have no backups, or worse, you do have backups but you have been hit by a virus rendering all those backups unusable.  Also, you have no backups of the actual SSIS packages!  Not a great situation to be in right?  Although uncommon and unlikely – it can happen!

Let’s take one of the ransomware attacks which happened recently.  This piece of malicious software rendered all SQL backup files unusable.  So how do you recover this instance and get your SSIS packages back working again?

Fortunately, this did not affect the .mdf or .ldf files meaning there is a chance to fully recover.

Here is the process I followed in order to get a customer instance back online again:

 

Pre-Requisites

  • You need your master database .mdf and .ldf files
  • The original service account that was set for the SQL Server service
    • If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key. This does not work with local accounts or the Local System, Local Service, or Network Service accounts

 

Continuing with the ransomware example, it is likely that this took out the Windows machine too so a new one was built.  In this example too, it was also decided to upgrade the SQL 2012 instance to SQL 2014 so we wanted a clean/fresh start.

 

Here's What I Did

  1. Set old service account for the SQL Service account

  2. Backup the current (“vanilla”) master database

  3. Restore old master

    1. You could either attach the old master .mdf and .ldf as master_old, backup this database and restore it over the current master db, or stop the SQL service, make a copy of the current (“vanilla”) master .mdf and .ldf then copy in the old master .mdf and .ldf over the top.
    2. You can harvest any old logins and permissions etc at this point too.

  4. Attach old SSISDB

    1. Don’t create an integration Services catalog at this point.  We will do that later.

  5. Restart the machine to enable the Local Machine Key or Windows Data Protection API to re-encrypt the Service Master Key

  6. Once the machine is back up, regenerate the SSISDB Database Master Key with a new password

    [sql] ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’ [/sql]

  7. Detach SSISDB (store MDF and LDF in safe location)

  8. Restore the new master database.  This is the “vanilla” master

  9. Create NEW integration services catalog

    1. This allows all the relevant objects to be created, clr to be enabled, and all .NET assemblies to be registered correctly

  10. Backup the SSISDB… Just in case

  11. Shut down the SQL Server service

  12. Copy the old SSISDB .mdf and .ldf files on top of the newly created ones

  13. Bring SQL service online

  14. Encrypt the SSISDB Database Master Key with the Service Master Key

    [sql] OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘Pa$$w0rd’;
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; [/sql]

  15. SSISDB should now be back online and usable

  16. Run a CHECKDB on all system and user databases

 

At this point, ensure you have a good backup plan for your instance (which also has verify steps!), a backup of all your packages and configurations (in source control), your windows patches all up to date and a schedule to keep them that way, and your virus definitions up to date too.

You should also consider not only your DR plan for moving to a secondary site, but also how you would recover if you lost everything!

 

Learn More

Encryption Hierarchy Alter Service Master Key SSISDB Catalog

Comments

  • Anonymous
    November 30, 2017
    I want to make sure I understand fully - Does restoring the old master database allow the old SSISDB database to be attached (and become readable) because the SSISDB Database Master Key is encrypted by the "old" master database's Service Master Key? Instead of decrypting the SSISDB DMK with a password (which is assumed lost), you are decrypting using the Service Master Key (which is decrypted by DPAPI, which is why you need the original service account). Then, you reset the DMK password for SSISDB, and once you restore the new "vanilla" master database and set up the SSIS catalog, you copy over SSISDB and open the SSISDB DMK w/ the password you just specified and finally encrypt that DMK using the new "vanilla" Service Master Key. And voila, you now have the old SSISDB with it's DMK encrypted by (a) a new password that you know and (b) the new Service Master Key for the new instance.