Unable to create the tSQLtCLR assembly in SQL Server 2017

Asked 3/10, 2017 at 18:29 Answered 8/7, 2021 at 17:38

Solved sql-server automated-tests sqlclr tsqlt sql-server-2017

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.

The SQL error message states:

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.

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?

Roadability answered 3/10, 2017 at 18:29 Comment(7)

I just read about this yesterday. I found sqlquantumleap.com/2017/08/28/… to be enlightening. The TL;DR seems to be that you sign the assembly with a certificate and then do an ADD SIGNATURE. Full disclosure, I haven't done it myself. – Peace 3/10, 2017 at 19:31

This was a helpful article. I've been trying to apply some of the suggestions, but still haven't quite got it yet...still working on it. – Roadability 6/10, 2017 at 13:23

If creating the certificate is causing you problems, you can whitelist an assembly with sp_add_trusted_assembly – Commander 9/10, 2017 at 20:4

@EdHarper and Brent (and all others): No, absolutely do not whitelist the assemblies via "Trusted Assemblies". That is a totally useless and less secure method. Certificates are not that difficult to use and I show several approaches in my series on SQLCLR in SQL Server 2017 at: sqlquantumleap.wordpress.com/category/programming/… I will post an answer below to explain the issue with regards to 3rd party assemblies (the one area I have not yet covered in my blog posts). – Ginger 11/10, 2017 at 15:23

"Alternatively, you can trust the assembly using sp_add_trusted_assembly" is an easy way to create the SAFE assembly. First, you should turn on the database TRUSTWORTHY by ALTER DATABASE [Your_Database_Name] SET TRUSTWORTHY ON. Then you can easily create your assembly. But for some security reasons, you should set your DB TRUSTWORTHY OFF but before that, your created assembly should be in DB trusted assembly list. Therefore you should use sp_add_trusted_assembly to place your created assembly in DB trusted assembly list. Then you can turn off DB TRUSTWORTHY by [next comment]. – Repine 13/4, 2018 at 16:35

ALTER DATABASE [Your_Database_Name] SET TRUSTWORTHY OFF. sp_add_trusted_assembly use hash value of assembly to add it. To get assembly hash value use this 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' – Repine 13/4, 2018 at 16:40

@MohammadAfrashteh (and others): there is no need, nor purpose, nor benefit, in using sp_add_trusted_assembly here (or pretty much anywhere, but certainly in this case). The tSQLtCLR assembly is already signed with a strong-name / asymmetric key, so it is best to make use of that. I can't think of a single advantage that sp_add_trusted_assembly has over the two suggestions I made in the HOWEVER section of my answer. – Ginger 2/9, 2018 at 16:32

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.

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;.

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...;

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 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

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;

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.

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;

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

PLEASE NOTE

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 vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

Ginger answered 11/10, 2017 at 16:19 Comment(3)

Solomon, thank you for you responses. I now have a good understanding as to why the problem exists. I'm still struggling to fix it. I think I'm approaching things from a slightly different perspective...in two ways. One, I'm not installing tSQLt in the master database, but rather the actual database being created. Second, that database is not pre-existing. I'm generating and re-generating it in a build process. – Roadability 30/10, 2017 at 13:34

Hey Brent. I am not suggesting to install tSQLt into master. I am simply showing how to get the Asymmetric Key (which needs to be loaded into master) installed (including the Login and UNSAFE ASSEMBLY permission). But tSQLt is assumed to not live in master. Once the Asymmetric Key and Login are created, they should remain even if you remove the other DBs and recreate them each time (this is the first block in the HOWEVER section, assuming you do block 2 first, one time). But if you need to create the Key and Login each time, then you would just use block 2 in the HOWEVER section. – Ginger 30/10, 2017 at 14:24

Okay, I got it working. I added the code from the first block of your HOWEVER section to the tSQLt.class file...immediately before the "CREATE ASSEMBLY [tSQLtCLR]" line and it worked. Thanks for all the help, its much appreciated. – Roadability 30/10, 2017 at 15:30

The tSQLt assembly is signed already. For now, you can create the assembly in master, create a certificate from it, drop the assembly again and then take the necessary steps with that certificate.

I'm working on getting the required step to install tSQLt on 2017 automated.

Verlaverlee answered 4/10, 2017 at 15:17 Comment(5)

The assembly can't be created in master. The assembly creation fails with the message listed above. – Roadability 6/10, 2017 at 13:12

You'll have to turn the CLR security strict option off while you execute the above steps. You can turn it back on afterwards. (At least I think that should work.) – Verlaverlee 6/10, 2017 at 13:25

I was able to turn the clr strict security option off and then create the tSQLtCLR assembly. However, I don't seem to understand certificates well enough to do anything with it. I've created a certificate, but I don't know what to do with it...sign it? Create a login with it? Sorry, this is all pretty new territory for me. – Roadability 6/10, 2017 at 14:19

@Roadability and Sebastian: I have posted an answer with details on a temporary yet immediate fix (essentially what you did) but with explanation. To be clear, if you accomplish load Assemblies by turning one of the two security mechanisms OFF and then back ON, then you don't need to mess with Certificates. I also included links to 2 posts in my series on these changes in 2017 that explain the long-term fixes, neither of which are "Trusted Assemblies", which should be avoided at all costs. I will post an issue in the tSQLt GitHub repository with the steps needed to fix this on a permanent basis. – Ginger 11/10, 2017 at 16:24

@Roadability and Sebastian: I have just updated my answer with a minor correction: you also need to grant the UNSAFE ASSEMBLY permission to the Login as that is not currently being done with the InstallExternalAccessKey procedure. Also, I finally completed the GitHub issue: github.com/tSQLt-org/tSQLt/issues/25 – Ginger 12/10, 2017 at 6:26

This worked for me:

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

Rip answered 30/4, 2021 at 20:6 Comment(3)

What error message did you get, @Amirhossein ? – Rip 9/7, 2021 at 20:38

@Philip Procedure expects parameter 'hash' of type 'binary(64)/varbinary(64)' – Haldeman 10/7, 2021 at 6:45

@Haldeman You have to run the whole thing as a single command. The only way I could recreate the error is to run EXEC sys.sp_add_trusted_assembly without parameters – Rip 3/11, 2023 at 19:10

You can use this script on master and then try to run tSQLt framework script query:

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

I was tested this code, on SQL-Server 2016-2017-2019, worked well.

Haldeman answered 8/7, 2021 at 17:38 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

HOWEVER,

PLEASE NOTE

Recommended topics

Hot tags