A developer reported an error in the Microsoft .Net Framework when executing a CLR object.
Use the query and CLR views to understand and troubleshoot CLR integration managed database objects that are executing in the SQL Server instance.
An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx System.IO.FileLoadException: Could not load file or assembly 'myapp.database.sqlserver, Version=2.1.40317.0, Culture=neutral, PublicKeyToken=xxxxxxxx' or one of its dependencies. An error relating to security occurred.
These type of errors can be difficult to troubleshoot. One of the reasons they can be difficult is finding the CLR objects and the associated procedures. Use this query to list the CLR objects and associated procedures
SELECT SCHEMA_NAME(O.schema_id) AS [Schema], O.name, A.name AS assembly_name, AM.assembly_class, AM.assembly_method, A.permission_set_desc, O.[type_desc] FROM sys.assembly_modules AM INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id INNER JOIN sys.objects O ON O.object_id = AM.object_id ORDER BY A.name, AM.assembly_class
Once you’ve identified the CLR objects , begin troubleshooting. There can be a number of reasons for the error. The most common reason I’ve seen is the AUTHORIZATION between the CLR and database is misaligned. Part of the troubleshooting process should include understanding how the authorization occurs between the objects.
A quick workaround is to change the AUTHORIZATION level of the database. An example of the code is:
ALTER AUTHORIZATION ON DATABASE::[MYDB] TO sa;
The sys.dm_clr_appdomains view creates a recordset of the application domains on the server.
SELECT * from sys.dm_clr_appdomains
The sys.dm_clr_loaded_assemblies view creates a recordset for each managed user assembly in the server address space.
SELECT * from sys.dm_clr_loaded_assemblies