CREATE ASSEMBLY for assembly is not trusted : SQL 2019

Padmanabhan, Venkatesh 241 Reputation points
2020-12-24T09:39:13.043+00:00

Hi .
I am using SQL Server 2019. I am trying yo add an assembly in SQL. The code works fine in SQL 2016, but errors out in 2019 with the below error :

"CREATE ASSEMBLY for assembly 'ABC.MSSQL' failed because assembly 'ABC.MSSQL' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly."

How to fix this ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,071 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 7,121 Reputation points
    2020-12-24T11:06:29.123+00:00

    CLR strict security is on by default in SQL 2017 and later versions. The proper approach is to follow the recommendations detailed in the error message you received so that the assembly can be created. Your other option is to disable the CLR strict security option but that is not recommended per the documentation.

  2. AmeliaGu-MSFT 13,891 Reputation points Microsoft Vendor
    2020-12-25T03:05:41.687+00:00

    Hi @Padmanabhan, Venkatesh ,

    Are there any updates to this issue?
    And based on my research, before an Assembly can be created, you first need to:

    1. Sign the Assembly with a strong-name key or a certificate
    2. Create an Asymmetric Key or Certificate in master from whatever you signed the Assembly with
    3. Create a Login based on that Asymmetric Key or Certificate
    4. Grant that Login the UNSAFE ASSEMBLY permission

    And You don't need to grant both UNSAFE ASSEMBLY and EXTERNAL ACCESS ASSEMBLY permissions to the signature-based login. The UNSAFE ASSEMBLY permission assumes the EXTERNAL ACCESS ASSEMBLY permission such that you can set assemblies to either PERMISSION_SET if you have the UNSAFE ASSEMBLY permission.
    For more details, please refer to this thread and this article which might help.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.