Tuesday, December 1, 2015

SSIS - SQL Server Stops Loading Assembly

We have created some SSIS package in SQL Server 2014 within Visual Studio 2013 for Business Intelligence. Everything was working fine after deployment them on the Azure Server. Due to some business requirements, our DBA took the back-up of the SSISDB and other data components and restore them another server having the same configurations.

Everything is working fine but SSISDB under Integration Services Catalogs was throwing the following error-
 

Msg 10314, Level 16, State 11, Line 4

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 'myAssembly, Version=2.0.0.490, Culture=neutral, PublicKeyToken=5963130873dd3a75' or one of its dependencies. Exception from HRESULT: 0x80FC0E21 System.IO.FileLoadException:at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)at System.Reflection.Assembly.Load(String assemblyString)

If we access the CLR object from a different database, and that database has a mismatching DBO SID, the same problem can occur.   
Root Cause of the problem - After doing a lot of research, we have found out that this problem occurs because the login that we use to create the database on Server A is not in the instance of SQL Server on Server B. This login could be either the Microsoft Windows login or the SQL Server login.
Solution- To work around this problem, we need to do two setting for the SSISDB database which are given below-
1) Need to set the Trustworthy=True database property. By default, this property is off for that database and we need to set this property on as given below-
USE [MASTER]
GO

---database owner should have the corresponding permissions
ALTER AUTHORIZATION ON DATABASE::[SSISDB] TO sa;

---- Enabled TRUSTWORTHY property
ALTER DATABASE [SSISDB] SET TRUSTWORTHY ON;

2) Use the sp_changedbowner stored procedure to change the database owner to sa or to an available login on Server B. We may use the following statement to change the database owner to sa or window authentication:
USE [SSISDB]
GO

--- Set the ownership for the user
EXEC sp_changedbowner 'sa'

--- Set the ownership for the window Windows Authentication
EXEC sp_changedbowner 'domain\username'

NOTE: The changed database owner should have the corresponding permissions to perform a certain task.
After doing the above settings, we are able to create new folders in SSISDB under Integration Services Catalogs as well as able to deploy other packages there without any issue.

No comments:

Post a Comment