The Problem
Recently, we suddenly started having issues with deploying SSIS-packages to our SSISDB catalog on SQL Server 2016 Standard Edition. I am writing this blog to share the solution we have found, which I have not been able to find anywhere online. Also, I want to warn against the other quick fix that works but causes security headaches.
The error message we started having was:
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. 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=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
The error could be reproduced by starting SQL Server Management Studio (SSMS) and trying to create a new folder inside the SSISDB-catalog.
The Quick, But Wrong, Fix
At first, the quick fix appeared to be changing a database setting of the SSISDB-database. Right here on this website, the same problem is described and the suggestion that "did the trick" is to mark the SSISDB-database as trustworthy. Indeed, changing this database setting 'fixes' the problem!
However, this causes security issues. For example, read this or this blog posts to find out why setting TRUSTWORTHY to ON is BAD. What's more, we had proof that the SSISDB-catalog should be able to work with TRUSTWORTHY set to OFF. In another (development) environment the SSISDB-catalog was still working great without the SSISDB-database having this trustworthy setting.
Finding the Right Way to Solve This
So, how should we solve this problem? Actually reading the error message helps. You can find out what exactly is assembly id 65536:
use [SSISDB] select * from sys.assemblies where assembly_id=65536
The query output indeed shows that the assembly is using the UNSAFE_ACCESS setting. The assembly is named ISSERVER and can also be found in SSMS (in the SSISDB-database go to Programmability > Assemblies):
The assembly is physically located inside the C:\Windows\Assembly folder on the Windows server that is running the SQL Server instance hosting the SSISDB-catalog:
As you can see, the version number matches the error message and contents of the clr_name column of the sys.assemblies DMV. We have verified with our IT colleagues that this file indeed was still there.
At this point, I started thinking it should be a security setting/something problem. Restoring a backup of the SSISDB-database maybe could have solved our problem, so I had a look at these instructions from Microsoft. I had completed the steps to back-up the SSISDB-database and began reading the required steps to do the restore. There I found out about the MS_SQLEnableSystemAssemblyLoadingUser. According to the instructions, this SQL user should be granted the Unsafe assembly privilege. So I checked this for our SQL Server instance that has the SSISDB-catalog and -database:
Bingo! For some unknown reason, the MS_SQLEnableSystemAssemblyLoadingUser no longer had the Unsafe assembly permission:
After I had ticked the Grant box for the Unsafe assembly permission of MS_SQLEnableSystemAssemblyLoadingUser our issues with deploying SSIS-packages to the SSISDB-catalog were solved. The TRUSTWORTHY setting of the SSISDB-database was still set to OFF.
