5

I have CLR assembly in SSDT and to deploy that it has to be trusted. What I understand there are 4 options how to do that

First option, use TRUSTWORTHY

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
  
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;

Second option, disable strict security

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
  
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

Third option, sign assembly with key or certificate

Seems complicated and I was not able to manage that yet. I will appreciate the instructions, because the workflow is not clear here.

Fourth option, use sp_add_trusted_assembly

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;

declare @assembly varbinary(max) = 0x4D5A90000300000004000000FFFF0000... -- I have to manually copy that from the failed SQL publish file.

declare @hash varbinary(64) = HASHBYTES('SHA2_512', @assembly);

EXEC sys.sp_add_trusted_assembly @hash, N'Foo Assembly';

In the 4th option I have to manually register the assembly as trusted and only after that I can publish the assembly. It's possible to somehow automate this process?

I am thinking about creating pre-deployment script that can run the 4th option code but I don't know how to populate the @assembly variable from the file of the assembly .dll.

Alternatively, if it's possible to deploy assembly as untrusted I can make it trusted on the server with the following code (post-deployment script)

-- Register all database assemblies as trusted
declare @name nvarchar(4000),
@content varbinary(max);

DECLARE appCursor CURSOR FAST_FORWARD FOR
    SELECT [name], content
    FROM   SourceDatabase.sys.assembly_files
 
OPEN appCursor
FETCH NEXT FROM appCursor INTO @name, @content
 
WHILE @@FETCH_STATUS = 0
BEGIN
   
   DECLARE @hash varbinary(64) = HASHBYTES('SHA2_512', @content);

    EXEC sys.sp_add_trusted_assembly @hash, @name;

   FETCH NEXT FROM appCursor INTO @name, @content
END
CLOSE appCursor
DEALLOCATE appCursor
GO

Any idea or what's your approach?

1 Answer 1

5

My preference is for Option 3: using the built-in / "traditional" mechanism of asymmetric key (i.e. strong name) / certificate. Unfortunately, SQL Server 2017 introduced "CLR strict security" and with no built-in means of overcoming this particular issue (of getting the security set up ahead of time). So, I came up with possible approaches that both with Visual Studio / SSDT (or without), one for asymmetric key (as this is how Visual Studio works by default, though slightly more complicated than the next option), and one for certificates (requires an extra EXE to handle certificates since Visual Studio / SSDT only handles strong-naming, but a simpler process overall):

The basic idea for "Solution 1" (i.e. using an asymmetric key) is to sign an empty assembly with the same strong name that is used for the DLL(s) that you are trying to install (and this is only because we still cannot create an asymmetric key by hexbytes / VARBINARY literal!!!). This will be uploaded to master so that we can set up the security before uploading the real assembly in one or more user databases. This alone worked prior to SQL Server 2017 and the ill-conceived "CLR strict security". With that new security constraint, we can no longer upload an unsigned SAFE assembly (i.e. the empty one signed with the asymmetric key), so now we need to also sign the empty assembly with a certificate (as this can be created via hexbytes, prior to uploading an assembly). This allows for loading the empty assembly so that we can then extract the asymmetric key. Convoluted yes, but unfortunately that's where we are until asymmetric keys can be created from hexbytes.

"Solution 2" skips the empty assembly and simply signs the intended custom assembly(ies) with a certificate. In this case, the certificate can be loaded directly into [master] and then the assemblies can be imported. However, since Visual Studio / SSDT does not sign with certificates, you need to manually update the .sqlproj file to inject a step to handle this.

6
  • Thank you! I am worried that I cannot include such a complexity into our solutions. We'll stick with an option 2 for now, as it's fine with the Vulnerability Assessment Scan. I thought option 4 could be a compromise (middle way), but if it's complicated to automate it, I'll leave it alone. But thank you for your articles, it might be handy later if someone will force us to be super secure.
    – Muflix
    Oct 16, 2022 at 21:26
  • In the article you say in point 1.2 Before an Assembly can be created: there needs to be a Login created from the same signature what do you mean by the signature?
    – Muflix
    Oct 16, 2022 at 21:30
  • 1
    @Muflix Rather than "signature" I probably should have said "Public Key", or even more technically correct would be "hash of the Public Key" or "Thumbprint". Oct 16, 2022 at 22:11
  • Thank you, now it's clear. So basically the workflow is to create a certificate, create a login from that certificate with unsafe assembly permission and sign assembly with that certificate.
    – Muflix
    Oct 16, 2022 at 22:24
  • 1
    @Muflix In a general sense, yes. But, if you create the certificate within SQL Server, you will need to export it to a file in order to sign the DLL since that needs to be signed before being imported into SQL Server. Technically, you can sign it once it has been imported, but you lose a little bit of functionality when doing that. Oct 17, 2022 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.