无法在SQL Server 2017中创建tSQLtCLR程序集

Question

无法在SQL Server 2017中创建tSQLtCLR程序集

sql-serverautomated-testssqlclrtsqltsql-server-2017

17

最近我安装了SQL Server 2017 Express和localdb（一般可用性）。在尝试安装tSQLt框架时，我发现了SQL Server 2017中实施的新安全功能：“clr strict security”选项。这个新的安全功能似乎会阻止创建tSQLtCLR程序集。

SQL错误消息显示：

使用“clr strict security”sp_configure的选项设置为1，使用SAFE或EXTERNAL_ACCESS选项为程序集“tSQLtCLR”创建或更改程序集失败。微软建议您使用具有相应UNSAFE ASSEMBLY权限的证书或非对称密钥签署程序集。或者，您可以使用sp_add_trusted_assembly信任程序集。

我已经阅读了与sp_add_trusted_assembly过程相关的Microsoft技术文档，但它似乎假定您能够成功创建程序集。如果您无法首先创建程序集，那么如何将tSQLtCLR程序集编码为“已信任”？

- Brent

我昨天刚刚了解到这个。我发现https://sqlquantumleap.com/2017/08/28/sqlclr-vs-sql-server-2017-part-4-trusted-assemblies-the-disappointment/非常有启发性。简而言之，您需要使用证书对程序集进行签名，然后执行“ADD SIGNATURE”。完全公开，我自己还没有尝试过。 - Ben Thul

这是一篇有帮助的文章。我一直在尝试应用一些建议，但还没有完全掌握...仍在努力中。 - Brent

@EdHarper和Brent（以及所有其他人）：不，绝对不要通过“受信任的程序集”将程序集列入白名单。那是一种完全无用且不太安全的方法。证书并不难使用，我在我的SQL Server 2017中的SQLCLR系列中展示了几种方法，网址为：https://sqlquantumleap.wordpress.com/category/programming/sqlclr-vs-sql-server-2017/ 我将在下面发布一个答案，以解释与第三方程序集相关的问题（这是我博客文章中尚未涉及的领域）。 - Solomon Rutzky

ALTER DATABASE [Your_Database_Name] SET TRUSTWORTHY OFF。使用程序集的哈希值添加它。要获取程序集哈希值，请使用此T-SQL：DECLARE @assemblyBinary VARBINARY(MAX) DECLARE @assemblyHash VARBINARY(64)SELECT @assemblyBinary = AF.content FROM sys.assemblies AS A INNER JOIN sys.assembly_files AS AF ON A.assembly_id = AF.assembly_id WHERE A.name = 'Your_Created_Assembly_Name'SELECT @assemblyHash = HASHBYTES('SHA2_512', @assemblyBinary)EXEC sp_add_trusted_assembly @hash = @assemblyHash, @description = N'Your_Created_Assembly_Name' - Mohammad Afrashteh

@MohammadAfrashteh（和其他人）：在这里使用sp_add_trusted_assembly没有必要，也没有目的和好处（实际上几乎在任何情况下都是如此），但肯定在这种情况下。 tSQLtCLR程序集已经使用强名称/非对称密钥进行了签名，因此最好利用它。我想不出sp_add_trusted_assembly比我在我的[答案]（https://dev59.com/ylYN5IYBdhLWcg3w_sua#46693088）的“然而”部分提出的两个建议更有优势。 - Solomon Rutzky

显示剩余2条评论

4个回答

5

tSQLt程序集已经签名。现在，您可以在master中创建该程序集，从中创建证书，然后再次删除程序集，并采取必要步骤进行验证。

我正在努力使在2017年自动安装tSQLt所需的步骤变得简便化。

- Sebastian Meine

无法在主服务器中创建程序集。程序集创建失败，出现上述消息。 - Brent

在执行上述步骤时，您需要关闭CLR安全严格选项。之后可以重新开启它。（至少我认为应该可以这样做。） - Sebastian Meine

1

我成功地关闭了clr strict security选项，然后创建了tSQLtCLR程序集。但是，我似乎不太了解证书的使用方法。我已经创建了一个证书，但是不知道该怎么做...签名？用它创建登录？抱歉，这对我来说都是新领域。 - Brent

@Brent和Sebastian：我已经发布了一个答案，详细说明了一个临时但立即可行的修复方法（基本上就是你们所做的），并附有解释。明确一点，如果您通过关闭其中一个安全机制然后再打开来实现装载程序集，则无需处理证书。我还包括了两篇关于2017年这些更改的系列文章的链接，它们解释了长期的修复方法，其中没有“受信任的程序集”，这应该尽量避免使用。我将在tSQLt GitHub存储库中发布一个问题，其中包含需要永久修复此问题的步骤。 - Solomon Rutzky

1

@Brent 和 Sebastian：我刚刚更新了我的答案，进行了一些小的更正：您还需要授予 UNSAFE ASSEMBLY 权限给 Login，因为 InstallExternalAccessKey 过程目前没有执行此操作。另外，我最终完成了 GitHub 问题：https://github.com/tSQLt-org/tSQLt/issues/25 - Solomon Rutzky

2

这对我有用：

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 a.is_user_defined = 1
    and a.name = 'tSQLtCLR'

EXEC sys.sp_add_trusted_assembly
     @hash
    ,@description

- Phillip

你收到了什么错误信息，@Amirhossein？ - Phillip

@Philip "过程期望参数'hash'的类型为'binary(64)/varbinary(64)'" - Amirhossein

@Amirhossein 你必须将整个事务作为一个单独的命令运行。我能够重新创建这个错误的唯一方法是在没有参数的情况下运行EXEC sys.sp_add_trusted_assembly。 - undefined

0

您可以在 master 上使用此脚本，然后尝试运行 tSQLt 框架脚本查询：

USE master
GO
IF OBJECT_ID('tempdb..#Private_GetAssemblyKeyBytes') IS NOT NULL DROP PROCEDURE #Private_GetAssemblyKeyBytes;
GO
CREATE PROCEDURE #Private_GetAssemblyKeyBytes
   @AssemblyKeyBytes VARBINARY(MAX) = NULL OUTPUT,
   @AssemblyKeyThumbPrint VARBINARY(MAX) = NULL OUTPUT
AS
  SELECT @AssemblyKeyBytes =
0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062+
0x652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300BB91FD5F0000000000000000E00022200B013000000A0000000600000000000042280000002000000040000000000010002000000002000004000000000000+
0x0004000000000000000080000000020000919C0000030040850000100000100000000010000010000000000000100000000000000000000000F02700004F00000000400000A003000000000000000000000000000000000000006000000C000000B82600+
0x001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000004808000000200000000A0000000200+
0x00000000000000000000000000200000602E72737263000000A00300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000010000000000000000000000000000040000042000000+
0x000000000000000000000000002428000000000000480000000200050058200000E005000009000000000000000000000000000000382600008000000000000000000000000000000000000000000000000000000000000000000000001E02280F00000A+
0x2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000A8010000237E0000140200002C02000023537472696E67730000000040040000040000002355530044040000100000002347554944000000540400008C0100+
0x0023426C6F620000000000000002000001471400000900000000FA013300160000010000001000000002000000010000000F0000000E0000000100000001000000000078010100000000000600ED00DE0106005A01DE0106002100AC010F00FE01000006+
0x00490094010600D00094010600B100940106004101940106000D01940106002601940106007900940106003500BF0106001300BF0106009400940106006000940106000D028D010000000001000000000001000100000010002502140241000100010050+
0x20000000008618A601060001000900A60101001100A60106001900A6010A002900A60110003100A60110003900A60110004100A60110004900A60110005100A60110005900A60110006100A60115006900A60110007100A60110007900A60110008100A6+
0x0106002E000B00C5002E001300CE002E001B00ED002E002300F6002E002B000C012E0033000C012E003B000C012E00430012012E004B001D012E0053000C012E005B000C012E00630035012E006B005F012E0073006C0104800000010000000000000001+
0x0000002300140200000200000000000000000000001A000A000000000000000000003C4D6F64756C653E006D73636F726C696200477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472+
0x696275746500417373656D626C795469746C6541747472696275746500417373656D626C794B65794E616D6541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7946696C6556657273696F6E+
0x41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574+
0x6500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C69747941+
0x7474726962757465007453514C74417373656D626C794B65792E646C6C0053797374656D0053797374656D2E5265666C656374696F6E002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E746572+
0x6F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F646573004F626A656374007453514C74417373656D626C794B657900656D7074790000000000009C98AF3349A76E+
0x4AA6D306AB985BCF8E00042001010803200001052001011111042001010E042001010208B77A5C561934E08980A00024000004800000940000000602000000240000525341310004000001000100B9AF416AD8DFEDEC08A5652FA257F1242BF4ED60EF5A+
0x7B84A429604D62C919C5663A9C7710A7C5DF9953B69EC89FCE85D71E051140B273F4C9BF890A2BC19C48F22D7B1F1D739F90EEBC5729555F7F8B63ED088BBB083B336F7E38B92D44CFE1C842F09632B85114772FF2122BC638C78D497C4E88C2D656C166+
0x050D6E1EF3940801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301080100020000000000150100107453514C74417373656D626C794B657900000501000000000A0100057453514C74000017010012436F+
0x7079726967687420C2A920203230313900002901002430333536303035622D373166642D346466332D383530322D32376336613630366539653800000C010007312E302E302E3000001D0100187453514C745F4F6666696369616C5369676E696E674B65+
0x7900000000E73936ABF16E67A4775423AE67F541BFE077E40098D90CEE90D33A631CE7C62B60228445A1C9B8A698B13317C65FD100A55706470458F0CE8C8E8CF2F30B457604C6E5F07A6862B1B45AB75E470E9F5D20E7596164413276227809F5BA14FC+
0x8A08DEEB63878D480DAE9D11907249CE1C6F58C9E69E925F071F552DEE436ADA0400000000BB91FD5F00000000020000001C010000D4260000D408000052534453D827CA8B9C7A5A4597EBB065786433D601000000443A5C615C315C735C7453514C7443+
0x4C525C7453514C74417373656D626C794B65795C6F626A5C437275697365436F6E74726F6C5C7453514C74417373656D626C794B65792E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001828000000000000000000003228000000200000000000000000000000000000000000000000000024280000000000000000000000005F+
0x436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000+
0x00000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440300000000000000000000440334000000560053005F00560045005200530049004F004E005F0049004E0046004F00000000+
0x00BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073+
0x006C006100740069006F006E00000000000000B004A4020000010053007400720069006E006700460069006C00650049006E0066006F0000008002000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E0074+
0x00730000000000000022000100010043006F006D00700061006E0079004E0061006D00650000000000000000004A0011000100460069006C0065004400650073006300720069007000740069006F006E00000000007400530051004C0074004100730073+
0x0065006D0062006C0079004B006500790000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000004A001500010049006E007400650072006E0061006C004E0061006D00650000+
0x007400530051004C00740041007300730065006D0062006C0079004B00650079002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F00700079007200690067006800740020+
0x00A90020002000320030003100390000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000005200150001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000+
0x007400530051004C00740041007300730065006D0062006C0079004B00650079002E0064006C006C00000000002C0006000100500072006F0064007500630074004E0061006D006500000000007400530051004C0074000000340008000100500072006F+
0x006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000+
0x000C0000004438000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000+
0x000000000000000000 
  ,@AssemblyKeyThumbPrint = 0xE8FFF6F136D7B53E ;
GO
IF OBJECT_ID('tempdb..#Private_EnableCLR') IS NOT NULL DROP PROCEDURE #Private_EnableCLR;
GO
CREATE PROCEDURE #Private_EnableCLR
AS
BEGIN
  EXEC master.sys.sp_configure @configname='clr enabled', @configvalue = 1;
  RECONFIGURE;
END;
GO
IF OBJECT_ID('tempdb..#Private_GetSQLProductMajorVersion') IS NOT NULL DROP PROCEDURE #Private_GetSQLProductMajorVersion;
GO
CREATE PROCEDURE #Private_GetSQLProductMajorVersion
AS
  RETURN CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)),4) AS INT);
GO
DECLARE @Version INT;
EXEC @Version = #Private_GetSQLProductMajorVersion;
PRINT @Version;
GO
IF OBJECT_ID('tempdb..#RemoveAssemblyKey') IS NOT NULL DROP PROCEDURE #RemoveAssemblyKey;
GO
CREATE PROCEDURE #RemoveAssemblyKey
AS
BEGIN
  IF(NOT EXISTS(SELECT * FROM sys.fn_my_permissions(NULL,'server') AS FMP WHERE FMP.permission_name = 'CONTROL SERVER'))
  BEGIN
    RAISERROR('Only principals with CONTROL SERVER permission can execute this procedure.',16,10);
    RETURN -1;
  END;
  DECLARE @master_sys_sp_executesql NVARCHAR(MAX); SET @master_sys_sp_executesql = 'master.sys.sp_executesql';
  DECLARE @ProductMajorVersion INT;
  EXEC @ProductMajorVersion = #Private_GetSQLProductMajorVersion;
  IF SUSER_ID('tSQLtAssemblyKey') IS NOT NULL DROP LOGIN tSQLtAssemblyKey;
  EXEC @master_sys_sp_executesql N'IF ASYMKEY_ID(''tSQLtAssemblyKey'') IS NOT NULL DROP ASYMMETRIC KEY tSQLtAssemblyKey;';
  EXEC @master_sys_sp_executesql N'IF EXISTS(SELECT * FROM sys.assemblies WHERE name = ''tSQLtAssemblyKey'') DROP ASSEMBLY tSQLtAssemblyKey;';
  DECLARE @cmd NVARCHAR(MAX);
  IF(@ProductMajorVersion>=14)
  BEGIN
    DECLARE @TrustedHash NVARCHAR(MAX);
    DECLARE @AssemblyKeyBytes VARBINARY(MAX);
    EXEC #Private_GetAssemblyKeyBytes @AssemblyKeyBytes = @AssemblyKeyBytes OUT;
    SELECT @TrustedHash = CONVERT(NVARCHAR(MAX),HASHBYTES('SHA2_512',@AssemblyKeyBytes),1);
    SELECT @cmd = 
           'IF EXISTS(SELECT 1 FROM sys.trusted_assemblies WHERE hash = ' + @TrustedHash +' AND description = ''tSQLt Ephemeral'')'+
           'EXEC sys.sp_drop_trusted_assembly @hash = ' + @TrustedHash + ';';
    EXEC master.sys.sp_executesql @cmd;
  END;
END;
GO
IF OBJECT_ID('tempdb..#InstallAssemblyKey') IS NOT NULL DROP PROCEDURE #InstallAssemblyKey;
GO
CREATE PROCEDURE #InstallAssemblyKey
AS
BEGIN
  IF(NOT EXISTS(SELECT * FROM sys.fn_my_permissions(NULL,'server') AS FMP WHERE FMP.permission_name = 'CONTROL SERVER'))
  BEGIN
    RAISERROR('Only principals with CONTROL SERVER permission can execute this procedure.',16,10);
    RETURN -1;
  END;
  DECLARE @cmd NVARCHAR(MAX);
  DECLARE @cmd2 NVARCHAR(MAX);
  DECLARE @master_sys_sp_executesql NVARCHAR(MAX); SET @master_sys_sp_executesql = 'master.sys.sp_executesql';
  DECLARE @ProductMajorVersion INT;
  EXEC @ProductMajorVersion = #Private_GetSQLProductMajorVersion;
  DECLARE @AssemblyKeyBytes VARBINARY(MAX),
          @AssemblyKeyThumbPrint VARBINARY(MAX);
  EXEC #Private_GetAssemblyKeyBytes @AssemblyKeyBytes OUT, @AssemblyKeyThumbPrint OUT;
  SET @cmd = 'IF EXISTS(SELECT * FROM sys.assemblies WHERE name = ''tSQLtAssemblyKey'') DROP ASSEMBLY tSQLtAssemblyKey;';
  EXEC @master_sys_sp_executesql @cmd;
  SET @cmd2 = 'SELECT @cmd = ''DROP ASSEMBLY ''+QUOTENAME(A.name)+'';'''+ 
              '  FROM master.sys.assemblies AS A'+
              ' WHERE A.clr_name LIKE ''tsqltassemblykey, %'';';
  EXEC sys.sp_executesql @cmd2,N'@cmd NVARCHAR(MAX) OUTPUT',@cmd OUT;
  EXEC @master_sys_sp_executesql @cmd;
  DECLARE @Hash VARBINARY(64) = NULL;
  IF(@ProductMajorVersion>=14)
  BEGIN
    SELECT @Hash = HASHBYTES('SHA2_512',@AssemblyKeyBytes);
    SELECT @cmd = 
           'IF NOT EXISTS (SELECT * FROM sys.trusted_assemblies WHERE [hash] = @Hash)'+
           'BEGIN'+
           '  EXEC sys.sp_add_trusted_assembly @hash = @Hash, @description = N''tSQLt Ephemeral'';'+
           'END ELSE BEGIN'+
           '  SELECT @Hash = NULL FROM sys.trusted_assemblies WHERE [hash] = @Hash AND description <> ''tSQLt Ephemeral'';'+
           'END;';
    EXEC @master_sys_sp_executesql @cmd, N'@Hash VARBINARY(64) OUTPUT',@Hash OUT;
  END;
  SELECT @cmd = 
         'CREATE ASSEMBLY tSQLtAssemblyKey AUTHORIZATION dbo FROM ' +
         CONVERT(NVARCHAR(MAX),@AssemblyKeyBytes,1) +
         ' WITH PERMISSION_SET = SAFE;'
  EXEC @master_sys_sp_executesql @cmd;
  IF SUSER_ID('tSQLtAssemblyKey') IS NOT NULL DROP LOGIN tSQLtAssemblyKey;
  SET @cmd = N'IF ASYMKEY_ID(''tSQLtAssemblyKey'') IS NOT NULL DROP ASYMMETRIC KEY tSQLtAssemblyKey;';
  EXEC @master_sys_sp_executesql @cmd;
  SET @cmd2 = 'SELECT @cmd = ISNULL(''DROP LOGIN ''+QUOTENAME(SP.name)+'';'','''')+''DROP ASYMMETRIC KEY '' + QUOTENAME(AK.name) + '';'''+
              '  FROM master.sys.asymmetric_keys AS AK'+
              '  LEFT JOIN master.sys.server_principals AS SP'+
              '    ON AK.sid = SP.sid'+
              ' WHERE AK.thumbprint = @AssemblyKeyThumbPrint;';
  EXEC sys.sp_executesql @cmd2,N'@cmd NVARCHAR(MAX) OUTPUT, @AssemblyKeyThumbPrint VARBINARY(MAX)',@cmd OUT, @AssemblyKeyThumbPrint;
  EXEC @master_sys_sp_executesql @cmd;
  SET @cmd = 'CREATE ASYMMETRIC KEY tSQLtAssemblyKey FROM ASSEMBLY tSQLtAssemblyKey;';
  EXEC @master_sys_sp_executesql @cmd;
  SET @cmd = 'CREATE LOGIN tSQLtAssemblyKey FROM ASYMMETRIC KEY tSQLtAssemblyKey;';
  EXEC @master_sys_sp_executesql @cmd;
  SET @cmd = 'DROP ASSEMBLY tSQLtAssemblyKey;';
  EXEC @master_sys_sp_executesql @cmd;
  IF(@Hash IS NOT NULL)
  BEGIN
    SELECT @cmd = 'EXEC sys.sp_drop_trusted_assembly @hash = @Hash;';
    EXEC @master_sys_sp_executesql @cmd, N'@Hash VARBINARY(64)',@Hash;
  END;
  IF(@ProductMajorVersion>=14)
  BEGIN
    SET @cmd = 'GRANT UNSAFE ASSEMBLY TO tSQLtAssemblyKey;';
  END
  ELSE
  BEGIN
    SET @cmd = 'GRANT EXTERNAL ACCESS ASSEMBLY TO tSQLtAssemblyKey;';
  END;
  EXEC @master_sys_sp_executesql @cmd;
END;
GO
IF OBJECT_ID('tempdb..#PrepareServer') IS NOT NULL DROP PROCEDURE #PrepareServer;
GO
CREATE PROCEDURE #PrepareServer
AS
BEGIN
  EXEC #Private_EnableCLR;
  EXEC #InstallAssemblyKey;
END;
GO
EXEC #PrepareServer;
GO

我在 SQL-Server 2016-2017-2019 上测试了这段代码，运行良好。

- Amirhossein

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Solomon Rutzky · Accepted Answer

SQL Server 2017引入了一个名为“CLR strict security”的新服务器级配置选项，并默认启用。此选项要求所有程序集，甚至是SAFE的，都必须使用证书或强名称密钥进行签名，并且用于签名的证书或非对称密钥已加载到[master]中，并从中创建了一个登录，该登录已被授予UNSAFE ASSEMBLY权限。

由于现在需要在通过CREATE ASSEMBLY加载SAFE程序集之前放置基于签名的登录，因此不再可能具有空的、已签名的程序集，该程序集通过CREATE ASSEMBLY ... FROM 0x... WITH PERMISSION_SET = SAFE;加载到[master]中。

现在，只有两种方法可以从VARBINARY文字或变量（即不是来自外部文件）创建可用于设置SQLCLR安全性的对象：

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

选项1不再是一个选项，至少不能单独使用。选项2很好，但由于证书没有完全集成到Visual Studio / MSBuild构建过程中，因此并不受欢迎。

幸运的是，有两种方法可以解决这个问题，如下面两篇博客文章中所讨论的：

SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1——需要的步骤比Part 3, Solution 2多，但是适用于已有项目，因为它几乎不需要对现有解决方案甚至部署过程进行任何更改(实际上，这实际上是我针对SQL#项目采取的方法，因为它只在安装脚本的开头添加了3个简单的步骤)
SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

然而,

这只回答了你目前处于这种情况的原因。要解决这个问题，假设你可能不会更新tSQLt构建过程以包括证书，那么你可以进行一个简单的一次性修复：

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 权限，这曾经是可以的，但现在已经不足够了。

当然，在执行这 4 个步骤之前，您将无法加载 tSQLt 组件，因此如果该过程是先加载所有内容并且失败了，则需要执行以下操作：

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

请注意

这些可能的解决方案中都不包括使用新的“Trusted Assemblies”功能。任何人出于任何原因（除了出于好奇和测试目的）都不应该使用该功能。避免使用它的原因在多篇博客文章中有详细说明（目前为止有3篇，还会有更多），开始于：

SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment