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

目录

介绍

背景

设置数据库所有者

禁用严格安全性

将程序集显式添加到受信任的程序集

显式信任整个数据库

结论

---

介绍

作为管理员，工作的一部分是为第三方应用程序进行生命周期管理。很多时候，这归结为以下方案的一个版本：

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

如果原始供应商不再营业、没有支持合同、十年前的应用程序不受支持迁移，或者......

我写这篇文章的情况是，数据库使用一个未签名的程序集进行clr集成，由于各种原因，该程序集不再处于开发阶段。

背景

SQL Server支持加载外部dotNET程序集，以便将默认的T-SQL功能与可在此类程序集中编程的所有内容复合在一起。这是一个非常强大的功能，因为您可以使用自定义计算和算法作为常规查询的一部分。

过去，DBA可以导入程序集，只需将其标记为“安全”。在这种情况下，“安全”意味着“只允许内部计算和本地数据访问。SAFE是限制性最强的权限集。由具有SAFE权限的程序集执行的代码无法访问外部系统资源，例如文件、网络、环境变量或注册表。

因此，它的缺点是可以毫无问题地执行安全程序集。这对于扩展SQL功能（例如将UTC转换为存储时间戳的本地时间）特别方便，您只需进行数据转换。但是，从SQL 2017开始，Microsoft已决定忽略在程序集属性中设置权限，并且默认情况下，无论该设置如何，所有程序集都是不安全的。

这称为 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可以选择将程序集添加到受信任程序集的列表中。这实际上是绕过了该特定程序集的“严格安全”。对于新项目来说，这不是一个好的做法，但如果你正在处理组织已经信任的旧代码，这是一个可以接受的权衡。

可以使用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包含二进制文件数据。请注意，在sys.assembly_files表的name字段上执行where子句非常重要。

就我而言，数据库中不仅部署了DatabaseFunctions.dll文件，还部署了 DatabaseFunctions.pdb 文件。因此，如果我们在join操作中选择sys.assemblies表的名称字段，则将对两个文件进行哈希处理，并且生成的哈希值将不正确，因为SQL Server只需要DLL文件的哈希值。

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

显式信任整个数据库

仅信任特定程序集的替代方法是信任整个数据库及其中的所有内容。一般来说，这不是一个好主意，因为它可以继续部署未签名的程序集，这会将系统的安全性降低到原来的水平以下。

ALTER DATABASE ClrTest SET TRUSTWORTHY ON; GO

此方法的优点是不必单独配置程序集的信任。如果数据库托管了无数个程序集，或者它只是一个沙盒系统，这可能是最方便的开始方式。

结论

我演示了三种方法，用于处理从旧版SQL迁移到2017或更高版本的clr集成数据库中的未签名程序集。按照安全性的降序/“不要这样做”的升序，这些是：

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

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

https://www.codeproject.com/Articles/5360061/Migrating-a-SQL-Database-with-CLR-Integration-from