How to sign a SQL Server assembly created with assembly_bits / hex bytes?

user_62511 0
Viewed 112

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 / hex bytes. I cannot find a good way to sign this assembly. How can this assembly be signed?

Below is a non-working example for reference:

CREATE ASSEMBLY HelloWorld  
    FROM 0x4D5A900000000000  
WITH PERMISSION_SET = UNSAFE;
sqlclr sql-server-2017 sql-server-2019 assemblies security
1 Answers

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)

user_5777650
Related