时间戳

首页 > 解决方案 > TSQL CREATE ASSEMBLY FROM varbinary 破坏了 class_name 参数

.net - TSQL CREATE ASSEMBLY FROM varbinary 破坏了 class_name 参数

问题描述

我已将我的本地 SQL Server 2008 R2 数据库迁移到 Azure SQL Server 托管实例 (SQL Server 2017)。一个数据库是存档（只读）数据库，一个是 OLTP 数据库，第三个是实用程序数据库，我在其中保存通用函数、存储过程和维护脚本。除了实用程序数据库中的 CLR 程序集外，所有三个数据库的一切都进行得非常顺利。该程序集在 TSQL 代码中提供正则表达式功能 - 非常有用！我基于此处的 Phil Factor 代码。最初，它是从已编译的 DLL 加载到本地数据库中的。它在那里像冠军一样工作。但在 SQL MI 上，运行使用 CLR 函数之一的 SP 时出现以下错误。

消息 10314，级别 16，状态 11，过程 dbo.globalSearch，第 22 行 [Batch Start Line 2]
Microsoft .NET Framework 在尝试加载程序集 id 65541 时发生错误。服务器可能资源不足，或者程序集可能不受信任。再次运行查询，或查看文档以了解如何解决程序集信任问题。有关此错误的更多信息：
System.IO.FileLoadException：无法加载文件或程序集“regexsqlclr，版本=0.0.0.0，文化=中性，PublicKeyToken=null”或其依赖项之一。发生与安全有关的错误。（来自 HRESULT 的异常：0x8013150A）
System.IO.FileLoadException：
在 System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark & stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblySecurityRef, Evidence assembly , RuntimeAssembly reqAssembly, StackCrawlMark & stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean for Introspection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark & stackMark, IntPtr pPrivHostBinder, Boolean for Introspection)
在 System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark & stackMark, Boolean forIntrospection)
在 System.Reflection.Assembly.Load(String assemblyString)

我尝试使用此MSDN 帖子中的步骤解决程序集信任问题，特别是执行

sys.sp_add_trusted_assembly

成功了，但什么也没改变。然后我想，既然它暗示它不能从文件加载，这似乎是有道理的，因为我无法访问文件系统的 SQL MI 中不存在该文件，我应该尝试从varbinary. 我只是说这似乎是有道理的，因为除了我最初从中加载它的服务器之外，该文件也不存在于我的任何其他本地服务器上，并且它在所有服务器上都能完美运行。但是，我愿意尝试任何事情！因此，我使用 SSMS 将程序集编写为DROPand CREATE，它使用FROM BINARY语法，并同样编写了所有函数的脚本。CREATE ASSEMBLY成功了，所以我认为我在正确的轨道上。然后我尝试创建第一个函数和BAM，另一个错误！这次错误读取

消息 6505，级别 16，状态 2，过程 RegexIndex，第 2
行在程序集“RegexFunctions”中找不到类型“RegexSQLCLR.RegularExpressionFunctions”。

我已经在谷歌上搜索了几个小时，试图找到解决这个问题的方法，但运气却为零。尽管该EXTERNAL NAME子句的类部分的语法对于从文件加载的程序集非常有效。我验证varbinary了 SSMS 编写的脚本与原始 DLL 的二进制文件相同。Microsoft 论坛上的某个人建议我确保使用该Any CPU选项编译 DLL - 确实如此。作为健全性检查，我在其中一台本地服务器上执行了相同的过程，即DROP和CREATE ASSEMBLY FROM BINARY，得到了完全相同的结果：我无法加载任何 CLR 函数！我已经尝试了我能想到的所有可能的类名排列，但无济于事。这是和的CREATE ASSEMBLY代码CREATE FUNCTION

CREATE ASSEMBLY [RegexFunction]
AUTHORIZATION [dbo]
FROM 0x4D5A90000 *truncated_for_brevity*
WITH PERMISSION_SET = SAFE

CREATE FUNCTION RegExIndex
   (
    @Pattern NVARCHAR(4000),
    @Input NVARCHAR(MAX),
    @Options int
   )
RETURNS int
AS EXTERNAL NAME 
   RegexFunction.[RegexSQLCLR.RegularExpressionFunctions].RegExIndex
GO

RegexSQLCLR是原始 DLLRegularExpressionFunctions的名称，是类的名称。RegexSQLCLR也是after usingname列中指定的名称；否则，原始 DLL 的完整路径在列中。sys.assembly_filesCREATE ASSEMBLY FROM BINARYname

标签： .netsql-server.net-assemblysqlclrazure-sql-managed-instance

解决方案

所以，这里有一系列问题：

  1. 第一个问题是，由于潜在的（但可能未经证实的）安全问题，未签名的程序集（或者甚至没有匹配的、基于签名的登录并已被授予UNSAFE ASSEMBLY权限的已签名程序集）不再被视为“安全” 。因此，从 SQL Server 2017 开始，引入了名为“CLR 严格安全”的新服务器级配置设置，并强制所有程序集满足标记为. 的选项和仍然像以前一样工作，这只是加载程序集并从中执行 SQLCLR 对象的问题。您遇到的问题是程序集永远不需要签名（尽管它们本来可以，例如我的所有程序集UNSAFEPERMISSION_SETSAFEEXTERNAL_ACCESSSAFESQL# SQLCLR 库，并且对它们进行签名也有好处，即使它们只包含SAFE代码并且将保持为PERMISSION_SET = SAFE) ，所以大多数都没有签名，但现在他们需要签名。因此，在将数据库从 2017 年之前的版本升级/迁移到 SQL Server 2017 或更高版本时，会引发安全错误。

一种选择是简单地禁用“CLR 严格安全”，或者另一种选择是启用TRUSTWORTHY程序集所在的数据库。两者都是不太安全的选择。我不确定禁用“CLR 严格安全”是多么“不安全”（实际上，这可能是微软的一个巨大的过度反应），但显然首选是保持启用它。启用TRUSTWORTHY绝对是一个非常糟糕的选择：PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining。

Microsoft 为解决这种情况而发布的机制是“受信任的程序集”功能。不幸的是，该功能也是一个糟糕的选择：它几乎从来不需要（嗯，只需要 Azure SQL 数据库，它不再支持 SQLCLR，但“受信任的程序集”代码已经编写好了）并且之所以可见，是因为没有人知道现有的功能已经解决了这种情况，并且以更好的方式。请参阅：SQLCLR 与 SQL Server 2017，第 4 部分：“受信任的程序集” - 失望。那篇文章详细介绍了“受信任的程序集”的问题以及您应该如何修复现有的未签名程序集的问题（即在数据库中使用程序集创建证书，对程序集进行签名，master仅从公钥创建相同的证书，从 master 中的证书创建登录，最后授予该登录UNSAFE ASSEMBLY允许）。

  1. 我尝试使用 ... 解决程序集信任问题，sys.sp_add_trusted_assembly它成功了，但没有任何改变。

我不确定您为哈希使用了什么值，但该存储过程不会验证哈希是否匹配任何内容。它只是将哈希加载到内部表中，以便稍后在引用程序集时从中读取。然后它将查看程序集的散列值是否与存储散列的内部表中的值匹配。所以它几乎总是会成功（只要你给它一个可以是 SHA2_256 哈希的有效二进制值）。

  1. 我认为因为它暗示它无法从文件加载，这似乎是有道理的，因为该文件在我无法访问文件系统的 SQL MI 中不存在

这只是一种误解。Azure SQL 数据库托管实例中没有文件系统访问权限是正确的，但在这种情况下，文件系统是存储通过CREATE ASSEMBLY. 错误是说程序集无法从表中加载到内存中。这是由于上面第 1 项中提到的安全问题。所以删除和重新创建程序集实际上并没有做任何事情。

  1. Microsoft 论坛上的某个人建议我确保使用 Any CPU 选项编译 DLL

我不知道为什么会出现这种情况。同样，这里的任何代码都没有问题。

  1. AS EXTERNAL NAME RegexFunction.[RegexSQLCLR.RegularExpressionFunctions].RegExIndex

不知道为什么RegexSQLCLR放在那里，但格式是（没有命名空间）：

AssemblyName.ClassName.MethodName

或（带有命名空间）：

AssemblyName.[NameSpaceName.ClassName].MethodName

  1. 再次，TRUSTWORTHY ON是一个糟糕的选择，完全没有必要。

推荐阅读