SQL Server 2017: Failed To Create Assembly. Check if referenced assemblies are up-to-date and truste-CodePudding

I found this same issue in my research, but no solution was found: Research Link

This .dll is System.net.http, and it is pulled from the 4.6.1 .NET Framework. I am trying to create an assembly out of it in SQL Server 2017.

I've done the following steps:

Create a certificate (THIS TELLS ME WARNING: Certificate is expired)
Create a login
Grant unsafe assembly
Also set the database I'm running this on to TRUSTWORTHY
Set CLR security to 0, tried installing assembly (failed)

This is the code I'm executing:

CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
   'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.6.1\System.net.http.dll';

CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];

GRANT UNSAFE ASSEMBLY TO [MS.NETcer];

create ASSEMBLY [System.Net.Http]  
FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.6.1\System.net.http.dll'
WITH PERMISSION_SET = UNSAFE

This is my issue:

Msg 6218, Level 16, State 2, Line 15
CREATE ASSEMBLY for assembly 'System.Net.Http' failed because assembly 'System.Net.Http' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Net.Http.SR::.ctor][mdToken=0x6000001][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_ResourceManager][mdToken=0x6000002][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_Culture][mdToken=0x6000003][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::set_Culture][mdToken=0x6000004][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_argument_empty_string][mdToken=0x6000005][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_absolute_baseaddress_required][mdToken=0x6000006][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_content_headers][mdToken=0x6000007][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_execution_error][mdToken=0x6000008][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_http_baseaddress_required][mdToken=0x6000009][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_invalid_requesturi][mdToken=0x600000a][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_request_already_sent][mdToken=0x600000b][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_request_headers][mdToken=0x600000c][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_response_headers][mdToken=0x600000d][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_send_canceled][mdToken=0x600000e][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net_http_client_send_completed][mdToken=0x600000f][offset 0x00000000] Code size is zero.
[ : System.Net.Http.SR::get_net...

Edit 1: If I use the .dll from the 4.7.2 framework instead, I get the following error:

Msg 6586, Level 16, State 1, Line 18
Assembly 'System.Net.Http' could not be installed because existing policy would keep it from being used.

Edit 2: If I follow the advice below and pull the .dll (instead of from the reference folders) from C:\Windows\Microsoft.NET\Framework64\v4.0.30319, I get this problem instead.

Msg 6522, Level 16, State 1, Procedure DataRobot.pDataUpload, Line 0 [Batch Start Line 47]
A .NET Framework error occurred during execution of user-defined routine or aggregate "pDataUpload": 
System.IO.FileLoadException: Could not load file or assembly 'System.Net.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException: 
   at DataRobot.DataUpload.Upload(String directory, String proxyAddress, String apiToken)

Edit 3: Following up @Solomon to provide additional context. I build my project on my work computer. The SQL Server is hosted on a different server. The .NET libraries I use are on my local C:\ drive. These are the facts so far:

I compile my project as a .dll to a SQL Server shared drive
I copy and paste the System.net.http.dll library to the same SQL Server shared drive.
I add the System.net.http.dll as a certificate. Database is also set to TRUSTWORTHY.
I create assemblies and stored procedure to run the methods in my compiled project .dll.

CodePudding user response：

You are probably pointing to a reference assembly and not the actual assembly with the actual code in it (hence the "Code size is zero." error message).

If you are using SQL Server 2012 or newer, then the following does work (and does not require setting the DB to TRUSTWORTHY ON, so turn TRUSTWORTHY back to OFF):

USE [master];
CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';

CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];

GRANT UNSAFE ASSEMBLY TO [MS.NETcer];

USE [SomeUserDB];
CREATE ASSEMBLY [System.Net.Http]  
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;

Regarding Edit:

If I use the .dll from the 4.7.2 framework instead, I get the following error:

Do not try to use a specific .NET Framework version of a .NET Framework library. You need to use what is currently on the server running SQL Server since the version that SQL Server is using must match the version that the OS is using. So, for system assemblies it is best to pull from disk (i.e. C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ ).

Regard Edit 2:

If I follow the advice and pull the .dll from C:\Windows\Microsoft.NET\Framework64\v4.0.30319, I get this problem instead.

Assembly in host store has a different signature than assembly in GAC.

Hmm. I would think the GAC would get its version from that same directory. Is it possible that there was a Windows Update that updated the version on disk and the GAC hasn't been refreshed yet? Or something like that? Can you rebooting the server and then try loading the assembly again?

Regarding Edit 3:

I copy and paste the System.net.http.dll library to the same SQL Server shared drive.

No, no, no. Do not copy/paste any .NET Framework DLL. You need to use the version that is on the same server as SQL Server. This is why you need to execute the statements that I provided above (i.e. CREATE ASSEMBLY [System.Net.Http] FROM 'C:\Windows\Microsoft.NET\Framework64\... ). It doesn't matter if you compile your project against .NET 4.5.2 and the server hosting SQL Server has .NET 4.8 installed. Just load the .NET Framework DLL from the server running SQL Server, then load your DLL. The only time a version difference matters between what you compile against and what SQL Server is using (based on the server it's running on) is if you are developing on a machine with a newer version of .NET Framework that has a new feature not found in the version being used by SQL Server. And in that case (which is rare to happen), you simply need to update the version of .NET Framework on the server running SQL Server.