I'm working on an upgrade to SQL Server 2019 and I need to sign assemblies. However the assembly code I have is using assembly_bits. I cannot find a good way to sign this assembly. How can sign this assembly?

Below is a non-working example for reference:

CREATE ASSEMBLY HelloWorld  
    FROM 0x4D5A900000000000  
WITH PERMISSION_SET = UNSAFE;

CodePudding user response：

Fortunately, there is a way that's not very difficult. You can sign the assembly in-place, once it has been created. In order to create an unsigned assembly, you will need to temporarily enable the TRUSTWORTHY database setting, which will be disabled moments later.

The steps are as follows:

1. ALTER DATABASE [{db_name}] SET TRUSTWORTHY ON;

2. Load one or more assemblies:

   CREATE ASSEMBLY HelloWorld
   FROM 0x4D5A900000000000
   WITH PERMISSION_SET = UNSAFE;
   

3. ALTER DATABASE [{db_name}] SET TRUSTWORTHY OFF;

4. Create a certificate to sign the assembl(y|ies):

   CREATE CERTIFICATE [HelloWorldAssemblies]
       ENCRYPTION BY PASSWORD = '{choose your own password}'
       WITH
           SUBJECT = 'Support loading HelloWorld while avoiding TRUSTWORTHY',
           EXPIRY_DATE = '2099-12-31';
   

5. Copy certificate (public key only) to [master] DB:

   DECLARE @TempCRT NVARCHAR(MAX) =
       CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'HelloWorldAssemblies')), 1);
   
   EXEC (N'USE [master];
           CREATE CERTIFICATE [HelloWorldAssemblies]
           FROM BINARY = '   @TempCRT);
   

6. Create login from the certificate AND grant it the UNSAFE ASSEMBLY permission:

   EXEC (N'USE [master];
   CREATE LOGIN [HelloWorldAssemblies]
       FROM CERTIFICATE [HelloWorldAssemblies];
   
   GRANT UNSAFE ASSEMBLY TO  [HelloWorldAssemblies];');
   

7. Sign the assembl(y|ies):

   ADD SIGNATURE 
       TO ASSEMBLY::[HelloWorld]
       BY CERTIFICATE [HelloWorldAssemblies]
       WITH PASSWORD = '{choose your own password}';
   

I have a blog post describing this process, including a link to a walk-through demo script:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment (Msg 10314)