How to register CLR assembly as trusted in SSDT deployment

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 appC

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

-
SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1

-
SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

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.

StackExchange Database Administrators Q#318280, answer score: 5