无法在SQL Server 2017中创建tSQLtCLR程序集 [英] Unable to create the tSQLtCLR assembly in SQL Server 2017

问题描述

我最近安装了SQL Server 2017 Express和localdb(通用).在尝试安装tSQLt框架时，我发现了SQL Server 2017中实现的新安全功能:"clr严格安全性"选项.这项新的安全功能似乎阻止了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:

使用SAFE创建或更改装配'tSQLtCLR'的装配或 EXTERNAL_ACCESS选项失败，因为"clr严格安全性"选项 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严格安全性"，默认情况下处于启用状态.此选项要求 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):

1. CREATE ASSEMBLY ... FROM 0x...;
2. CREATE CERTIFICATE ... FROM BINARY = 0x...;

1. CREATE ASSEMBLY ... FROM 0x...;
2. 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:

1. —比第3部分的解决方案2(下)要花更多的步骤，但是非常适合现有项目，因为它几乎不需要更改现有解决方案甚至部署过程(事实上，这实际上是我为 SQL#项目，因为它所做的只是在安装脚本的开头添加了3个简单的步骤)

1. 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)
2. 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.

当然，在完成这四个步骤之前，您将无法加载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部分:受信任的程序集" –令人失望

这篇关于无法在SQL Server 2017中创建tSQLtCLR程序集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！