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:
- 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
- Set old service account for the SQL Service account
- Backup the current (“vanilla”) master database
- Restore old master
- 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.
- You can harvest any old logins and permissions etc at this point too.
- Attach old SSISDB
- Don’t create an integration Services catalog at this point. We will do that later.
- Restart the machine to enable the Local Machine Key or Windows Data Protection API to re-encrypt the Service Master Key
- Once the machine is back up, regenerate the SSISDB Database Master Key with a new password
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’
- Detach SSISDB (store MDF and LDF in safe location)
- Restore the new master database. This is the “vanilla” master
- Create NEW integration services catalog
- This allows all the relevant objects to be created, clr to be enabled, and all .NET assemblies to be registered correctly
- Backup the SSISDB… Just in case
- Shut down the SQL Server service
- Copy the old SSISDB .mdf and .ldf files on top of the newly created ones
- Bring SQL service online
- Encrypt the SSISDB Database Master Key with the Service Master Key
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘Pa$$w0rd’; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
- SSISDB should now be back online and usable
- 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!