从旧版 SQL Server 迁移带有 CLR 集成的 SQL 数据库

引言

作为管理员，生活的一部分就是对第三方应用程序进行生命周期管理。很多时候，这会归结为以下场景的一种版本。

1. 公司为业务目的从供应商 A 购买软件。
2. 应用程序安装在一台计算机上，并在中央 SQL 服务器上创建一个数据库。可能存在也可能不存在支持合同。
3. 软件运行良好，用户满意，并且由于软件运行良好，因此被使用了很多年。由于它很可能安装在虚拟机中，因此硬件生命周期管理不是问题，整个系统可以运行十年。
4. 最终，微软停止支持该应用程序的 Windows / SQL 版本，而公司网络安全人员越来越坚持要迁移到新的 Windows / SQL 组合。
5. 您将数据库迁移到一个现代平台，突然之间，由于安全措施的收紧，情况不再奏效。

如果原始供应商已不再经营，没有支持合同，对于已有十年历史的应用程序没有支持的迁移，或者……，可能会出现进一步的复杂情况。

我写这篇文章的案例是，该数据库使用了一个未签名的程序集来进行 CLR 集成，由于各种原因，该程序集已不再开发。

背景

SQL Server 支持加载外部 dotNET 程序集，通过基本上您能在该程序集中编程的所有内容来弥补默认的 T-SQL 功能。这是一个非常强大的功能，因为您可以将自定义计算和算法用作常规查询的一部分。

过去，DBA 可以导入一个程序集，然后简单地将其标记为“安全”。在此上下文中的“安全”意味着“只允许内部计算和本地数据访问。SAFE 是最严格的权限集。具有SAFE 权限的程序集执行的代码无法访问外部系统资源，例如文件、网络、环境变量或注册表。”

所以简而言之，安全程序集可以毫无问题地执行。这对于扩展 SQL 功能特别方便，例如将 UTC 转换为本地时间用于存储的时间戳，您只需进行数据转换。然而，从 SQL 2017 开始，微软决定忽略程序集属性中的权限设置，并且所有程序集默认均为 UNSAFE，无论该设置如何。

这被称为CLR 严格安全。

CLR 使用 .NET Framework 中的代码访问安全 (CAS)，它不再被支持作为安全边界。用 PERMISSION_SET = SAFE 创建的 CLR 程序集可能能够访问外部系统资源、调用非托管代码并获取 sysadmin 权限。从 SQL Server 2017 (14.x) 开始，引入了一个名为 clr strict security 的 sp_configure 选项来增强 CLR 程序集的安全性。clr strict security 默认启用，并将 SAFE 和 EXTERNAL_ACCESS 程序集视为 UNSAFE。

因此，当您尝试运行会导致程序集加载的查询时，会发生类似以下的错误。

Msg 10314, Level 16, State 11, Procedure dbo.sp_GetJournalRecords, 
Line 25 [Batch Start Line 2]
An error occurred in the Microsoft .NET Framework while trying to 
load assembly id 65536. The server may be running out of resources, 
or the assembly may not be trusted. Run the query again, 
or check documentation to see how to solve the assembly trust issues. 
For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'databasefunctions, 
Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. 
An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

为了能够运行代码，程序集必须使用具有与“UNSAFE ASSEMBLY”权限匹配的登录名的证书或非对称密钥进行签名。这可能很麻烦，尤其是在迁移现有的遗留数据库而您又没有原始程序集或签名方法的情况下。

不要误会我的意思。如果您正在设计或设置一个新项目，这应该是正确的方式。我现在讨论的是您必须迁移一个现有运行代码的设置的场景，您知道您可以信任这些代码。

设置数据库所有者

将数据库迁移到新服务器后，首要任务是更正数据库的所有权。默认情况下，这是 sa。但是，原始服务器上 sa 帐户的 GUID 将与新服务器上 sa 帐户的 GUID 不同。

要解决此问题，我们使用以下查询。

EXEC sp_changedbowner 'sa'
GO

在所有情况下，这都是一个好习惯，但在本例中更是如此，因为授予加载不安全程序集的权限需要由具有适当权限的可信帐户执行，例如 sa 帐户。为此，拥有数据库的 sa 帐户需要是实际的 sa 帐户。

禁用严格安全

这是最简单但也是最糟糕的解决方案。您可以禁用 CLR 严格安全，这将使该实例上的所有内容都处于受信任状态。这是最后的手段，不推荐使用。

EXEC sp_configure 'clr strict security', 0
RECONFIGURE
GO

为了完整性，我列出了这一点。除非您没有其他选择，否则请勿这样做。

明确将程序集添加到受信任程序集列表

SQL Server 有一个选项可以将程序集添加到受信任程序集列表。这实际上是为了绕过特定程序集的“CLR 严格安全”。这对于新项目来说不是一个好习惯，但如果您正在处理组织已经信任的遗留代码，这是一个可以接受的权衡。

您可以通过 sp_add_trusted_assembly 来实现这一点，它需要程序集的哈希值和程序集的描述性名称。这种方法确实需要您计算该哈希值。为此，您可以使用此查询。这不是我原创的作品。我将其拼凑自各种公开代码片段。

declare
     @hash binary(64),
     @description nvarchar(4000)

select
    @hash = HASHBYTES('SHA2_512', af.content),
    @description = a.clr_name
FROM sys.assemblies a
JOIN sys.assembly_files af
    ON a.assembly_id = af.assembly_id
WHERE
    af.name = 'databasefunctions'

EXEC sys.sp_add_trusted_assembly  @hash, @description
go

sys.assemblies 包含程序集的名称、CLR 名称和内部 ID。sys.assembly_files 包含二进制文件数据。请注意，重要的是 where 子句是在 sys.assembly_files 表的 name 字段上执行的。

在我的例子中，不仅 DatabaseFunctions.dll 文件部署在数据库中，DatabaseFunctions.pdb 文件也部署在数据库中。因此，如果在 join 操作中选择 sys.assemblies 表的 name 字段，那么两个文件都将被哈希，并且生成的哈希将是不正确的，因为 SQL Server 只需要 DLL 文件的哈希。

执行此操作时，即使启用了严格安全，您也可以为该特定程序集启用 CLR。如果您只有一个或少量已信任的程序集，那么信任特定程序集的方法是一个好方法。这不是一个好习惯，但您已经信任了这些程序集，您并没有降低整体安全性，它确保了该数据库上的新项目在部署和发布之前必须通过签名过程。

明确信任整个数据库

与仅信任特定程序集相比，另一种方法是信任整个数据库及其中的所有内容。总的来说，这不是一个绝佳的主意，因为它允许将来继续部署未签名程序集，这将降低您系统的安全性低于以往。

ALTER DATABASE ClrTest SET TRUSTWORTHY ON; GO

这种方法的优点是您不必单独配置程序集的信任。如果数据库托管了大量的程序集，或者它只是一个沙箱系统，这可能是最便捷的入门方式。

结论

我展示了三种处理 CLR 集成数据库中未签名程序集的方法，这些数据库已从旧 SQL Server 版本迁移到 2017 或更高版本。按安全性递减/“不要这样做”的顺序排列，这些方法是：

1. 为单个程序集配置信任
2. 为整个数据库配置信任
3. 为整个实例禁用严格安全

您选择哪种方法取决于您的具体考虑因素，如果可能，最好的方法当然是获取程序集签名，这样您就可以完全避免这个问题。

历史

• 2023 年 5 月 4 日：第一版