问题描述
Microsoft Azure Tech支持已确认在SQL Server托管实例CLR中支持所有版本的.Net 4.XX(包括4.7.2).
我们正在使用4.7.2和最新的EF 6.2转换业务层的某些部分
https ://patrickdesjardins.com/blog/how-to-use-third-party-dll-reference-in-a-sql-clr-function
因此，为了利用经过测试的代码的现有投资，我们希望将某些业务层移至数据库中.
但是在发布时我们遇到了问题.
您正在注册的程序未在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，
这使我无法将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;
受管实例使用最新版本的.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程序集加载到数据库中的解决方案进行生产，则可能应该实现启动存储过程或计划任务，该任务每次从Windows .NET Framework文件夹刷新数据库程序集SQL Server启动的时间.
无论如何，这是一个Powershell脚本，我能够将EF6及其依赖项加载到SQL Server数据库中.但是请记住，仅因为您可以加载程序集，并不意味着它将正常工作.您必须进行广泛的测试，以确定您的EF代码是否会真正起作用.
但是在您的SQL Server上运行.NET代码并不常见，通常是一个坏主意.它非常接近您的数据，但是同一VNet上的单独VM也是如此.它使您的SQL Server变得更难管理，并且通常有许多更简单的方法来完成您希望通过在SQL Server上运行代码来获得的目标，
此外，如果您在SQL Server上本地运行代码，则没有充分的理由可能必须使用SQL CLR.您可以只在控制台应用程序中运行代码，并使用 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.
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,
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;
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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！