Failed to create assembly 'System.ServiceModel.Internals' in SQL

Question

I am trying to create an assembly in SQL but I am not able to load the following DLL.

System.ServiceModel.Internals

create assembly [system.servicemodel.internals]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll'
with permission_set = safe;
go

I get this error:

Msg 6218, Level 16, State 2, Line 2 CREATE ASSEMBLY for assembly 'System.ServiceModel.Internals' failed because assembly 'System.ServiceModel.Internals' 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.Runtime.IOThreadScheduler+ScheduledOverlapped::.ctor][mdToken=0x6000185][offset 0x00000023][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack. [ : System.Runtime.IOThreadScheduler+ScheduledOverlapped::Post][mdToken=0x6000183][offset 0x0000000D][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack. [ : System.Runtime.IOThreadScheduler+ScheduledOverlapped::Cleanup][mdToken=0x6000184][offset 0x00000019][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteMessageEvent][mdToken=0x6000357][offset 0x0000004B][found ref 'System.String'] Expected numeric type on the stack. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteEvent][mdToken=0x6000359][offset 0x0000012B] Instruction cannot be verified. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteEvent][mdToken=0x6000359][offset 0x0000003F] Instruction cannot be verified. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteEvent][mdToken=0x600035a][offset 0x00000061][found ref 'System.String'] Expected numeric type on the stack. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteEvent][mdToken=0x600035b][offset 0x0000001F][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteTransferEvent][mdToken=0x600035c][offset 0x0000007C] Instruction cannot be verified. [ : System.Runtime.Diagnostics.DiagnosticsEventProvider::WriteTransferEvent][mdToken=0x600035c][offset 0x000002F4][found Native Int][expected unmanaged pointer] Unexpected type on the stack. [...

I saw this solved question(almost 100% similar) Failed to CREATE AN ASSEMBLY in SQL so I tried to do the same

The .NET version of the SQL Server and the file are the same v4.0.30319

I ran the statement select * from sys.dm_clr_properties and the result seems to be normal.

directory   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version v4.0.30319
state   CLR is initialized

SQL VERSION: 11.2.5058.0

Any idea how to create this assembly?

I need it because when I try to create a custom assembly I got this error:

Assembly 'DataLoader' references assembly 'system.servicemodel.internals, version=4.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Answer 1

Have you tried installing the assembly with the UNSAFE permission set option?

I have System.ServiceModel.Internals (v4 from GAC) installed on SQL Server 11.0.5058 as UNSAFE, I don't know if you will be having versioning issues as well but I believe the assembly can only be installed as unsafe as it may access unmanaged resources.

From your error message:

[found unmanaged pointer] [expected unmanaged pointer] Unexpected type on the stack.

I understand this as Expected Unmanaged pointer, found unmanaged pointer, unmanaged pointer not allowed.

See https://msdn.microsoft.com/en-us/library/ms189566.aspx for definitions of permission sets.

Answer 2

  [found ref 'System.String'] Expected numeric type on the stack

The stack trace tells the tale, the CLR verifier checked the stack and found an unexpected type, a string instead of a number. That's pretty bad. The relevant method that's executing, the stack trace is not complete so we can't trace it all the way back, is System.Runtime.Diagnostics.DiagnosticsEventProvider.WriteTransferEvent().

This is a .NET 4 addition in the .NET Framework, it supports ETW (Event Tracing for Windows). A disassembler can show what code uses it, through several layers, that for example the System.ServiceModel.Channels.HttpRequestContext.TraceHttpMessageReceived() calls it.

In other words, we are firmly in WCF land, it received a message over HTTP and it generates an ETW event so it can be traced by ETW tooling. The call originated in System.ServiceModel.dll, the core WCF assembly, the ETW tracing code is located in System.ServiceModel.Internals.dll.

So, somehow, and it is getting to be a bit of a foregone conclusion how this could have happened given the nature of the question, the SQL Server machine has two different revisions of these WCF assemblies. They are normally distributed as a pair, part of the basic .NET install. There have been many revisions since .NET 4.0 RTM, versions 4.01, 4.02, 4.03 were slip-streamed through Windows Update for example, these updates in particular affected System.ServiceModel. Not to speak of versions 4.5, 4.5.1, 4.5.2 and 4.6 shipped since then and a few handfuls of KB updates that fixed bugs and patched security problems.

Anticipating the next question: so what's the correct revision of System.ServiceModel.Internals.dll? You can call Microsoft Support and they will tell you: "there isn't one". But you already knew that. So don't do this. If you want to try to make this working anyway then a basic strategy is to first look at the revision number of System.ServiceModel, then try to find a System.ServiceModel.Internals whose revision number is, if not equal, then at least in the ballpark. The one you have now is almost certainly not close, revision 34234 is, roughly, a .NET 4.5.2 revision number.