无法在 SQL Server 2017 中创建 tSQLtCLR 程序集
我最近安装了 SQL Server 2017 Express 和 localdb(普遍可用).在尝试安装 tSQLt 框架时，我发现了 SQL Server 2017 中实现的新安全功能:clr strict security"选项.这个新的安全特性似乎阻止了 tSQLtCLR 程序集的创建.
I recently installed SQL Server 2017 Express and localdb (general availablity). While attempting to install the tSQLt framework I've discovered a new security feature implemented in SQL Server 2017: the "clr strict security" option. This new security feature seems to prevent the creation of the tSQLtCLR assembly.
SQL 错误消息指出:
The SQL error message states:
CREATE 或 ALTER ASSEMBLY 使用 SAFE 或EXTERNAL_ACCESS 选项失败，因为 'clr strict security' 选项sp_configure 设置为 1.Microsoft 建议您在具有相应证书或非对称密钥的程序集使用 UNSAFE ASSEMBLY 权限登录.或者，您可以信任使用 sp_add_trusted_assembly 的程序集.
CREATE or ALTER ASSEMBLY for assembly 'tSQLtCLR' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.
我已阅读 Microsoft 与 sp_add_trusted_assembly 过程相关的技术文档，但似乎假设您能够成功创建程序集.如果一开始就无法创建 tSQLtCLR 程序集，如何将其编码为受信任"?
I've read Microsoft's technical documentation related to the sp_add_trusted_assembly procedure, but it seems to assume that you were able to successfully create the assembly. How would one code the tSQLtCLR assembly to be listed as "trusted" if you can't get it created in the first place?
推荐答案
SQL Server 2017 引入了一个名为CLR strict security"的新服务器级配置选项，默认启用.此选项要求使用证书或强名称密钥对 ALL 程序集，甚至是 SAFE 程序集进行签名，并且将用于执行该签名的证书或非对称密钥加载到[master]，并从中创建了一个登录名，并且该登录名已被授予 UNSAFE ASSEMBLY 权限.
SQL Server 2017 introduces a new server-level configuration option named "CLR strict security", and it is enabled by default. This option requires that ALL Assemblies, even SAFE ones, be signed with a certificate or strong name key, and that the Certificate or Asymmetric Key used to do that signing is loaded into [master], and has a Login created from it, and that Login has been granted the UNSAFE ASSEMBLY permission.
由于 SAFE 程序集现在需要在通过 CREATE ASSEMBLY 加载之前 进行基于签名的登录，因此不再需要可能有一个空的、签名的程序集通过 CREATE ASSEMBLY ... FROM 0x... WITH PERMISSION_SET = SAFE; 加载到 [master] 中.
Due to SAFE Assemblies now needing to have the signature-based Login in place before being loaded via CREATE ASSEMBLY, it is no longer possible to have an empty, signed Assembly that gets loaded into [master] via CREATE ASSEMBLY ... FROM 0x... WITH PERMISSION_SET = SAFE;.
现在，只有两种方法可以从 VARBINARY 文字或变量(即 not 来自外部文件)创建可用于设置 SQLCLR 安全性的对象:
Now, there are only two ways to create objects usable to set up SQLCLR security from a VARBINARY literal or variable (i.e. not from an external file):
CREATE ASSEMBLY ... FROM 0x...;CREATE CERTIFICATE ... FROM BINARY = 0x...;
选项#1 不再是一个选项，至少不是单独的选项.选项 2 很好，但由于证书未完全集成到 Visual Studio/MSBuild 构建过程中，因此从未被首选.
Option #1 is no longer an option, at least not by itself. Option 2 is fine, but was never preferred due Certificates not being fully integrated into the Visual Studio / MSBuild build process.
幸运的是，有两种方法可以解决这个问题，正如我在以下两篇博文中所讨论的那样:
Fortunately, there are two ways to fix this as discussed in the following two blog posts of mine:
- SQLCLR 与 SQL Server 2017，第 2 部分:CLR 严格安全性" - 解决方案 1 —比第 3 部分，解决方案 2(下)更多的步骤，但非常适合现有项目，因为它几乎不需要更改现有解决方案甚至部署过程(事实上，这实际上是我为我的 SQL# 项目，因为它所做的只是在安装脚本的开头添加了 3 个简单的步骤)
- SQLCLR 与 SQL Server 2017，第 3 部分:CLR 严格安全性" - 解决方案 2
- SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" – Solution 1 — more steps than Part 3, Solution 2 (below), but a good fit for existing projects as it requires almost no changes to the existing solution or even deployment process (and in fact, this is effectively the route that I went for my SQL# project as all it did was add 3 simple steps to the beginning of the installation script)
- SQLCLR vs. SQL Server 2017, Part 3: "CLR strict security" – Solution 2
但是，
这只是回答了为什么"您处于当前情况的问题.要解决这种情况，假设您可能不会更新 tSQLt 构建过程以包含证书，那么您可以做一个简单的一次性修复:
HOWEVER,
that just answers the question of "why" you are in the situation that you are currently in. To fix that situation, assuming that you likely aren't going to update the tSQLt build process to include a Certificate, then you can do a simple one-time fix of:
ALTER DATABASE [master] SET TRUSTWORTHY ON;
EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
ALTER DATABASE [master] SET TRUSTWORTHY OFF;
GRANT UNSAFE ASSEMBLY 之所以存在，是因为 tSQLt.InstallExternalAccessKey 存储过程仅将 EXTERNAL ACCESS ASSEMBLY 授予登录，该登录用于没关系，但现在还不够.
The GRANT UNSAFE ASSEMBLY is there due to the tSQLt.InstallExternalAccessKey Stored Procedure only granting EXTERNAL ACCESS ASSEMBLY to the Login, which used to be fine, but now is not enough.
当然，在完成这 4 个步骤之前，您将无法加载 tSQLt 程序集，因此如果该过程是首先加载所有内容并且失败，那么您将需要执行以下操作:
Of course, you won't be able to load the tSQLt Assemblies until those 4 steps are done, so if the process is to load everything first and that is failing, then you will need to do:
EXEC sp_configure 'clr strict security', 0; RECONFIGURE;
-- Install tSQLt ...
EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
EXEC sp_configure 'clr strict security', 1; RECONFIGURE;
我在 tSQLt GitHub 存储库中创建了一个问题，其中包含将理想修复程序合并到源文件中所需的步骤:https://github.com/tSQLt-org/tSQLt/issues/25
I created an issue in the tSQLt GitHub repository with the steps require to incorporate the ideal fix into the source files: https://github.com/tSQLt-org/tSQLt/issues/25
这些可能的解决方案都不包括使用新的受信任的程序集"功能.任何人都不应出于任何原因(纯粹出于好奇和测试之外)使用该功能.避免它的原因在几篇博文(目前有 3 篇或更多篇)中有详细说明:
that none of these possible solutions includes using the new "Trusted Assemblies" feature. That feature should never, ever be used by anyone for any reason (outside of sheer curiosity and testing). The reasons for avoiding it are detailed in several blog posts (currently 3 and more on the way) starting with:
SQLCLR 与 SQL Server 2017，第 4 部分:受信任的程序集"——失望
相关文章