An error occurred in the Microsoft .NET Framework while trying to load assembly id 65675

Question

I have to work on an existing application, comprises of many projects including a database project. In the statup project, which is a windows app., when making a call to a Adapter.Fill(dataTable);a scary error appears saying that:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65675. 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 'xxxxx.yyyy.database, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)
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)
 (.Net SqlClient Data Provider)



   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at XXX.YY.Data.dsLandholdingsTableAdapters.LandholdingsTableAdapter.Fill(LandholdingsDataTable dataTable, String IV_LRNO, String SearchField, String SearchVal, Nullable`1 SearchType) in E:\Projects\PPP\XXX\YYY\Data\dsLandholdings.Designer.cs:line 5678
   at XXX.YYY.Browse.BrowseLandholdings.btnSearch_Click(Object sender, EventArgs e) in E:\Projects\PPPP\XXXX\YYY\Browse\BrowseLandholdings.cs:line 352

After investigating on the web, I found that it may help to change the "Permission level" on the "Database" tab of my "Database project properties" from "safe" to either "Unsafe" or "External".

But the error is still coming up after doing mentioned change!

Does anyone possibly have any idea why this is happening?

Any help appreciated in advance.

Answer 1

For those on SQL Server 16 or higher who want to use trusted assemblies and keep clr strict security enabled, here are the steps that worked for me:

1. Drop your troublesome assemblies that aren't working (If I didn't do this first, the following steps didn't work) DROP ASSEMBLY [YourAssembly]
2. Run configuration changes:

USE [YourDatabase]
EXEC sp_configure 'clr enabled', '1'
EXEC sp_configure 'show advanced options', '1'
EXEC sp_configure 'clr strict security', '1' --we want to use strict security
RECONFIGURE

3. Add your assemblies

USE [YourDatabase]
CREATE ASSEMBLY [YourAssembly] ...

You may get an error here around database owner permissions. We want to set the owner of the master database to be the same as target database.

The database owner SID recorded in the master database differs from the database owner SID recorded in database. 

USE [YourDatabase]
DECLARE @serverOwner VARCHAR(MAX)
SELECT serverOwner  = suser_sname( owner_sid ) FROM sys.databases WHERE name = 'master'
exec sp_changedbowner @serverOwner

Then rerun your create assembly script.

4. Add the trusted assembly

USE [YourDatabase]
declare
     @hash binary(64)
    ,@description nvarchar(4000)

select
      @hash = HASHBYTES('SHA2_512', af.content)
    , @description = a.clr_name
FROM sys.assemblies a
JOIN sys.assembly_files af
    ON a.assembly_id = af.assembly_id
WHERE a.is_user_defined = 1 AND
    af.name = 'YourAssembly'

EXEC sys.sp_add_trusted_assembly
     @hash
    ,@description

Answer 2

This worked for me

EXEC sp_configure 'show advanced options', '1';
GO
RECONFIGURE;
GO

--Enable CLR (.NET Common Language Runtime)
exec sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO
--Enable xp_CmdShell stored procedure to run Command Line programs from within T-SQL
--EXEC sp_configure 'xp_cmdshell', 1
--GO

EXEC sp_configure 'show advanced options', '0';
GO
RECONFIGURE;


 /****this is another one****/
USE master
GO
ALTER DATABASE <DB_NAME>SET TRUSTWORTHY ON

USE <DB_NAME>
GO
EXEC sp_changedbowner 'sa'
/****when error occured relating to m,emory***/ 

Answer 3

This did the trick for me:

USE <DATABASE>;
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

USE <DATABASE>
GO

EXEC sp_changedbowner 'sa'
USE <DATABASE>
GO

ALTER DATABASE <DATABASE> SET TRUSTWORTHY ON;  

Answer 4

Ensure that the assembly you are using the xxxxx.yyyy.database assembly from is not targeting an older version of the .NET Framework.

Answer 5

At long last, I could fix it,

1. Enable the "CLR Integration" in SQL server.
2. Deploy the CLR Object ,which in my case was the Database Project,and I set the Permission level to external as well.

Thanks for all comments/answers.

Answer 6

The stack trace points to an assembly it could not load. If you're using .dll files to build or load anything (data import/export), you may want to start there.

Check to make sure the assembly is the proper version for your application, or that it even exists within the directory.

Also, double check the naming convention in the assembly. The stack trace shows an assembly name or codebase being invalid. Is the language parseable within your application? Is there a typo in the name?

These may be really elementary places to start, so my apologies if you've tried these. I've run into traces like this before, and it my case it usually amounted to a missing .dll or version incompatibility.