Using Entity Framework 6 / EF Core inside SQL CLR

Question

Microsoft Azure Tech support has confirmed that all version of .Net 4.XX including 4.7.2 is supported in SQL Server Managed Instance CLR.

We're transforming some portion of Business layer with 4.7.2 and latest EF 6.2

Use Entity Framework in CLR Stored procedure

https://patrickdesjardins.com/blog/how-to-use-third-party-dll-reference-in-a-sql-clr-function

Therefore in order to leverage existing investment in well tested code we want to move certain business layer into DB.

However we ran into problems, when publishing.

Creating [System.Dynamic]...

Warning: The Microsoft .NET Framework assembly 'system.dynamic, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.'

You are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

(47,1): SQL72014: .Net SqlClient Data Provider:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Dynamic' failed because assembly 'System.Dynamic' 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.Dynamic.ArgBuilder::MarshalToRef][mdToken=0x6000002][offset 0x00000000] Code size is zero. [ : System.Dynamic.ArgBuilder::UnmarshalFromRef][mdToken=0x6000003][offset

P.S. (IMHO, doesn't matter if EF core as well, neverthless we're trying this with EF6.2)

EDIT: Have given UNSAFE permission to all assemblies I'm referring to: Here are all the dependencies:

<ItemGroup>
    <Reference Include="Microsoft.CSharp">
      <HintPath>..\packages2019\dotnet 4.7.2\Microsoft.CSharp.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="System.Dynamic">
      <HintPath>..\packages2019\dotnet 4.7.2\System.Dynamic.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="System.Runtime.Serialization">
      <HintPath>..\packages2019\dotnet 4.7.2\System.Runtime.Serialization.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="SMDiagnostics">
      <HintPath>..\packages2019\v4.0.30319\SMDiagnostics.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="System.ServiceModel.Internals">
      <HintPath>..\packages2019\v4.0.30319\System.ServiceModel.Internals.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
      <Reference Include="EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.6.2.0\lib\net45\EntityFramework.dll</HintPath>
            <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <SpecificVersion>True</SpecificVersion>
      <IsModelAware>True</IsModelAware>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
    </Reference>
    <Reference Include="EntityFramework.SqlServer, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.6.2.0\lib\net45\EntityFramework.SqlServer.dll</HintPath>
            <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <SpecificVersion>True</SpecificVersion>
      <IsModelAware>True</IsModelAware>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
    </Reference>

EDIT 3: Unsafe code in Azure SQL Server MI

EDIT 4:

• The show-stopper is: System.RunTime.Serialization,

CREATE ASSEMBLY [System.Runtime.Serialization]
    AUTHORIZATION [dbo]
    FROM 0x4D5...
    WITH PERMISSION_SET = UNSAFE;

which prevents me to create Entity Framework UNSAFE assemblies into DB. Can we get past system.Runtime.Serialization?

GO
CREATE ASSEMBLY [EntityFramework]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90...
    WITH PERMISSION_SET = UNSAFE;


GO
PRINT N'Creating [EntityFramework.SqlServer]...';


GO
CREATE ASSEMBLY [EntityFramework.SqlServer]
    AUTHORIZATION [dbo]
    FROM 0x4...
    WITH PERMISSION_SET = UNSAFE;

Warning: The Microsoft .NET Framework assembly 'system.runtime.serialization, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details. Msg 6218, Level 16, State 2, Line 11 CREATE ASSEMBLY for assembly 'System.Runtime.Serialization' failed because assembly 'System.Runtime.Serialization' 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.AppContextDefaultValues::PopulateDefaultValues][mdToken=0x6000001] [offset 0x00000000] Code size is zero.

Answer 1

SQL Server's CLR host will use the highest version of the .NET Framework that is installed on the system, for the CLR version that it is linked to. SQL Server 2005 - 2008 R2 is linked to CLR version 2.0, hence those will use .NET Framework versions 2.0, 3.0, and 3.5. SQL Server 2012 and newer are all linked to CLR version 4.0, and hence will use .NET Framework versions 4.x.

That being said, some Framework libraries are built-in and do not need to be manually added. Those are listed here:

Supported .NET Framework Libraries

If you need a library that is not in that list, you can add it yourself, but that doesn't mean that you can add just any library. SQL Server only allows pure MSIL libraries, not mixed mode (mixed = contains both managed and unmanaged code). If you require a library that is mixed mode, then there is nothing you can do to load that into SQL Server. ALSO, please keep in mind that even if a library is pure MSIL today, that doesn't mean that it can't be converted to mixed mode in a future Framework update (and yes, this has happened).

With all of that in mind, the following item in the error message:

Code size is zero.

probably indicates that you are trying to load a reference library. You need to load the actual library, not the reference version of it.

I tried the following on SQL Server 2017 and was able to load them all, though not all needed to be loaded explicitly. Some automatically loaded others since they were in the same folder:

CREATE ASSEMBLY [Microsoft.CSharp]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.CSharp.dll'
WITH PERMISSION_SET = UNSAFE;
-- includes System.Dynamic


CREATE ASSEMBLY [System.Runtime.Serialization]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;
-- includes SMDiagnostics, System.ServiceModel.Internals

Then check with:

SELECT * FROM sys.assemblies;
/*
Microsoft.CSharp
System.Dynamic
System.Runtime.Serialization
System.ServiceModel.Internals
SMDiagnostics
*/

Please note that, for the moment, I set the database to TRUSTWORTHY ON. This is not something that I generally recommend, and might not be necessary, but until I complete my post dealing with how to handle this properly and easily, TRUSTWORTHY will suffice.

I did not have the EntityFramework libraries to load, but the error you are getting is on the first one and the statements I posted above do not get that error. If you are able to execute those 2 statements in order to get all 5 libraries loaded, and then get an error on EntityFramework, then we will take a look at the specific error message.

HOWEVER, even if you are able to load all of these DLLs, including the two for EntityFramework, that is not a guarantee that you will actually be able to use EF. It is possible that EF is prevented from running in SQLCLR. I can't remember about EF specifically, but I do know that SMO, for example, includes code to detect if it is running within SQL Server, and if it is, then it will raise an exception stating that it is not allowed to run within SQL Server.

Answer 2

Microsoft Azure Tech support has confirmed that all version of .Net 4.XX including 4.7.2 is supported in SQL Server Managed Instance CLR

Managed Instance uses the latest version of .NET Framework for hosting SQL CLR assemblies. That doesn't mean it supports loading untested .NET Framework assemblies. See the support statement here.

Furthermore Managed Instance does not allow UNSAFE CLR assemblies, as these would enable you to run arbitrary code and access server resources directly.

Even if you could get all these .NET Framework assemblies loaded into your database, it would not be a supportable solution in Managed Instance. As the support statement above makes clear, you must keep the copies of the .NET framework assemblies loaded in your database in sync with the .NET framework version on the server. The .NET Framework on the server is updated in Windows updates. When you are managing the server keeping your assemblies in-sync with the version in Windows is hard enough. But when Microsoft is patching the server, you have no way to know that you need to update your assemblies.

When running on an Azure VM you will be able to install all of the assemblies that EF requires. The only restriction in a VM is that you can't load mixed-mode assemblies, but I don't think EF (currently) relies on any of those. If you go to production with a solution involving .NET Framework assemblies loaded into your database, you should probably implement a startup stored procedure or scheduled task that refreshes your database assemblies from the Windows .NET Framework folder every time SQL Server starts.

Anyway here's a powershell script I was able to get EF6 and its dependencies loaded into a SQL Server database. But remember, just because you can load an assembly, doesn't mean it's going to work correctly. You'll have to test extensively to determine if your EF code will actually work.

But running your .NET code on your SQL Server is unusual, and is usually a bad idea. It is very close to your data, but so is a separate VM on the same VNet. It makes your SQL Server harder to manage, and there are typically much easier ways to accomplish whatever you're hoping to gain by running your code on the SQL Server,

And, moreover, if you are running your code locally on the SQL Server, there's no good reason why it has to be SQL CLR, probably. You can just run your code in a Console App and trigger it with xp_cmdshell or a SQL Agent job.

Anyway here's the powershell for registering the assemblies:

$constr = "server=localhost;database=clrtest;integrated security=true"
$folder = "C:\Users\dbrowne\Source\Repos\SqlClrTest\ClassLibrary1\bin\Debug"
$netfx = "C:\Windows\Microsoft.NET\Framework64\v4.0.30319"

$dlls = @(
    "system.dynamic",
    "microsoft.csharp",
    "system.componentmodel.dataannotations",
    "smdiagnostics",
    "system.servicemodel.internals",
    "system.runtime.serialization", 
    "entityframework",
    "entityframework.sqlserver",
    "YourClassLibrary",
    "YourSqlClrProject"
    )


[System.Data.SqlClient.SqlConnection]$con = New-Object System.Data.SqlClient.SqlConnection $constr
$con.Open()

[System.Data.SqlClient.SqlCommand] $cmd = $con.CreateCommand()
$cmd.CommandText = @"

    if cast(serverproperty('ProductMajorVersion') as int) >= 14
    begin


        DECLARE @hash varbinary(64);

        SELECT @hash = HASHBYTES('SHA2_512', @assemblyFile);

        declare @description nvarchar(4000) = @name

        if not exists (select * from sys.trusted_assemblies where hash = @hash)
        begin
          EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                           @description = @description;
          print 'trusted assembly added'
        end

    end

   declare @sql nvarchar(max) 

   if exists (select * from sys.assemblies where name = @name)
   begin

        set @sql =  concat('
        alter assembly ',quotename(@name),'
        FROM @assemblyFile
        WITH PERMISSION_SET = UNSAFE;  
        ')
       EXECUTE sp_executesql @sql, N'@assemblyFile varbinary(max)', @assemblyFile = @assemblyFile;
       print 'updated assembly ' + @name
   end
   else
   begin

        set @sql =  concat('
        create assembly ',quotename(@name),'
        AUTHORIZATION [dbo]
        FROM @assemblyFile
        WITH PERMISSION_SET = UNSAFE;  
        ')

       EXECUTE sp_executesql @sql, N'@assemblyFile varbinary(max)', @assemblyFile = @assemblyFile;
       print 'added assembly ' + @name

   end


"@

$pName = $cmd.Parameters.Add("@name", [System.Data.SqlDbType]::NVarChar, 1000)
$pAssemblyFile = $cmd.Parameters.Add("@assemblyFile", [System.Data.SqlDbType]::VarBinary, -1)



foreach ($targetDll in $dlls)
{
    try
    {
       $pName.Value = $targetDll
       if ([System.IO.File]::Exists("$folder\$targetDll.dll"))
       {
          $pAssemblyFile.Value = [System.IO.File]::ReadAllBytes("$folder\$targetDll.dll")
       }
       else
       {
          $pAssemblyFile.Value = [System.IO.File]::ReadAllBytes("$netfx\$targetDll.dll")

       }

       $result = $cmd.ExecuteNonQuery()

    }
    catch [System.Data.SqlClient.SqlException]
    {
       [System.Data.SqlClient.SqlException] $ex = $_.Exception

       write-host "$($ex.Class) $($ex.Number) $($ex.Message) "

       write-host ""

       continue;
    }
}
$con.Close()