CREATE ASSEMBLY failed because assembly failed verification. Check if the referenced assemblies are up-to-date and trusted to execute in the database

Question

A database with a CLR dll was migrated from SQL Server 2008R2 to SQL Server 2017.
Deployment to this database is automated using DacPac in Azure DevOps.

The dll is registered with EXTERNAL_ACCESS.

Since the migration to the new server, the deployment pipeline is broken.

Here is what I've done so far to fix it.

1. I changed the Target platform in the Database Project from SQL Server 2008 to SQL Server 2017.

   Error in the pipeline changed from

   Unable to connect to master or target server. You must have a user with the same password in master or target server

   to

   Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql140DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.

2. From this answer, Microsoft SQL Server Data-Tier Application Framework (17.1 DacFx) was installed on the build agent.

   Error in the pipeline changed to

   Assembly 'system.componentmodel.dataannotations, version=3.5.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.' was not found in the SQL catalog.

3. I then changed the target framework of the dll in the Database Project from NET Framework 3.5 to NET Framework 4.0

   Error in the pipeline changed to:

   Could not deploy package. Error SQL72014: .Net SqlClient Data Provider: Msg 6218, Level 16, State 2, Line 1
   CREATE ASSEMBLY for assembly 'System.ComponentModel.DataAnnotations' failed because assembly 'System.ComponentModel.DataAnnotations' failed verification.
   Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database.
   CLR Verifier error messages if any will follow this message
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptionProvider::GetTypeDescriptor][mdToken=0x6000003][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptionProvider::.ctor][mdToken=0x6000001][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptionProvider::.ctor][mdToken=0x6000002][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptor::GetAttributes][mdToken=0x6000007][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptor::GetProperties][mdToken=0x6000006][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptor::GetProperties][mdToken=0x6000005][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociatedMetadataTypeTypeDescriptor::.ctor][mdToken=0x6000004][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociationAttribute::.ctor][mdToken=0x6000008][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociationAttribute::get_Name][mdToken=0x6000009][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociationAttribute::get_ThisKey][mdToken=0x600000a][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociationAttribute::get_OtherKey][mdToken=0x600000b][offset 0x00000000] Code size is zero.
   [ : System.ComponentModel.DataAnnotations.AssociationAttribute::get_IsForeignKey][mdToken=0x60000...

   Error SQL72045: Script execution error. The executed script: CREATE ASSEMBLY [System.ComponentModel.DataAnnotations] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B7E0A14B0000000000000000E00022200B01080000B00000000800000000000092CF00000020000000E000000000C4600020000000020000040000000000000004000000000000000020010000020000CBF4000003004085000010000010000000001000001000000000000010000000000000000000000040CF00004F00000000E00000E0040000000000000000000000BA000088170000000001000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000098AF00000020000000B0000000020000000000000000000000000000200000602E72737263000000E004000000E000000006000000B20000000000000000000000000000400000402E72656C6F6300000C0

Since steps 3, I have been unable to progress. All my attempts from there failed to do anything. Here is what I've tried.

• GRANT EXTERNAL ACCESS ASSEMBLY TO [userName]
• GRANT UNSAFE ASSEMBLY TO userName]
• ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
• SET [userName] as db_owner
  SET [userName] as sysadmin
• put all dependency of the dll as UNSAFE
• SET 'clr enabled' to 1
• SET 'clr strict security' TO 0
• Imported successfully a dummy CLR DLL
• I've replicated the issue with a simple dummy project that reference System.ComponentModel.DataAnnotations with property Model Aware activated.

What can I do next to fix the DacPac deployment?

EDIT :

My next attempt is to decompile System.ComponentModel.DataAnnotationsand see if it would be smoother for the pipeline using my own version of it. I trying this because, I have read somewhere that the server verify if the dll already exists in the GAC. If it does, then dll version/signature much match. Therefore, I feel like even if I manage to load this assembly, it is bound to fail again after other server maintenance. Thus, the cost versus benefit of using this dll is bad. Knowing, only a little part of the dll is used anyway, I might as well bring the code used from the decompiler.

Answer 1

It looks like you are impacted by clr strict security, to confirm it try to deploy your assembly manually (without using DacPac).
If confirmed, you may temporary disable strict security - change option clr strict security using sp_configure.
As a final solution you should start signing your assembles.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-assembly-transact-sql?view=sql-server-ver15

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017 (14.x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. For more information, see CLR strict security.