Friday, July 16, 2021

SSIS DB not loading

Right now you have tried to update your server and move from one machine to another. You have tried to set trustworthy to on, you have set the ownership to sa.  Nothing has worked. You even have used your super secret password and set master key decryption. 

Here is the way to get this to work. The secret sauce below is to create your SSISDB catalog, empty, and create it with the SAME password that you are going to use when you mount your old version of the database.  This is unintuitive, and I hate it.  But this is what worked for my team.

1.  CREATE THE SSIS CATALOG USING THE WIZARD

     1a.  IN THE WIZARD put the password you had for the original database.
2.  Now that it is made, pull in the back up of the original database into the system.
3.  Now that the backup is pulled in.

Run the following commands:


OPEN master Key decryption by password = '<your password>';
ALTER Master Key ADD encryption by Service Master Key

ALTER AUTHORIZATION ON DATABASE::SSISDB TO sa;
ALTER DATABASE SSISDB SET TRUSTWORTHY ON

The error this fixes is:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=14.0.0.0,