在SQL CLR中使用Entity Framework 6/EF Core [英] Using Entity Framework 6 / EF Core inside SQL CLR

问题描述

Microsoft Azure Tech支持已确认在SQL Server托管实例CLR中支持所有版本的.Net 4.XX(包括4.7.2).

我们正在使用4.7.2和最新的EF 6.2转换业务层的某些部分

在CLR存储过程中使用实体框架

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

因此，为了利用经过测试的代码的现有投资，我们希望将某些业务层移至数据库中.

但是在发布时我们遇到了问题.

正在创建[System.Dynamic] ...

警告:Microsoft .NET Framework程序集"system.dynamic， 版本= 4.0.0.0，文化=中性，publickeytoken = b03f5f7f11d50a3a.'

您正在注册的程序未在SQL Server托管环境中经过全面测试，因此不受支持.将来，如果升级或维护该程序集或.NET Framework，则CLR集成例程可能会停止工作.有关更多详细信息，请参考SQL Server联机丛书.

(47,1):SQL72014:.Net SqlClient数据提供程序:

第6层，状态2，第1行，消息6218
为程序集"System.Dynamic"创建装配失败，因为程序集"System.Dynamic"验证失败.检查引用的程序集是否为最新且受信任的程序集(对于external_access或不安全)，以便在程序集中执行.数据库.
CLR验证程序错误消息(如果有)将跟随此消息[:
System.Dynamic.ArgBuilder :: MarshalToRef] [mdToken = 0x6000002] [offset 0x00000000]代码大小为零. [: System.Dynamic.ArgBuilder :: UnmarshalFromRef] [mdToken = 0x6000003] [offset

P.S. (恕我直言，EF核心是否也没关系，不过我们正在使用EF6.2进行尝试)

已向我指的所有程序集授予UNSAFE许可: 这是所有依赖项:

<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>

Azure SQL Server MI中的不安全代码

• 表演的终结者是:System.RunTime.Serialization，

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

这使我无法将Entity Framework UNSAFE程序集创建到DB中.我们可以过去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;

警告:Microsoft .NET Framework程序集 'system.runtime.serialization，版本= 4.0.0.0，区域性=中性， publickeytoken = b77a5c561934e089."您注册不完全 在SQL Server托管环境中进行了测试，不受支持.在 未来，如果您升级或维护此程序集或.NET 框架，您的CLR集成例程可能会停止工作.请参考 有关更多详细信息，请参见SQL Server Online. Msg 6218，第16级，状态2， 第11行为组装'System.Runtime.Serialization'创建组装 失败，因为程序集"System.Runtime.Serialization"失败 确认.检查引用的程序集是否最新以及 受信任(对于external_access或不安全)在数据库中执行. CLR验证程序错误消息(如果有)将跟随此消息[: System.AppContextDefaultValues :: PopulateDefaultValues] [mdToken = 0x6000001] [offset 0x00000000]代码大小为零.

解决方案

Microsoft Azure Tech支持已确认SQL Server托管实例CLR支持.net 4.XX的所有版本，包括4.7.2.

受管实例使用最新版本的.NET Framework托管SQL CLR程序集.这并不意味着它支持加载未经测试的.NET Framework程序集.请参阅支持声明此处.

此外，受管实例不允许使用UNSAFE CLR程序集，因为这些程序集使您可以运行任意代码并直接访问服务器资源.

即使您可以将所有这些.NET Framework程序集都加载到数据库中，在托管实例中也不是可支持的解决方案.正如上面的支持声明所明确指出的，您必须使数据库中加载的.NET Framework程序集副本与服务器上的.NET Framework版本保持同步. Windows更新中更新了服务器上的.NET Framework.在管理服务器时，使程序集与Windows中的版本保持同步非常困难.但是，当Microsoft为服务器打补丁时，您将无法知道需要更新程序集.

在Azure VM上运行时，您将能够安装EF所需的所有程序集. VM中的唯一限制是您不能加载混合模式程序集，但我不认为EF(当前)依赖于任何这些程序集.如果要使用涉及将.NET Framework程序集加载到数据库中的解决方案进行生产，则可能应该实现 xp_cmdshell 或SQL代理作业.

无论如何，这里是用于注册程序集的功能强大的shell:

$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()

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.

解决方案

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()

这篇关于在SQL CLR中使用Entity Framework 6/EF Core的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！