SQL Server 2017 Management Studio: CREATE ASSEMBLY error

Question

I created user defined type in C#.

When I run this:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  

EXEC sp_configure 'show advanced option', '1'; 
RECONFIGURE;  
GO  

USE NameOfDatabase

CREATE ASSEMBLY Database1
FROM 'C:\Users\MyDoc\Desktop\Project\Database1\Database1\bin\Debug\Database1.dll'
WITH PERMISSION_SET = SAFE; 

CREATE TYPE [dbo].[TypeName] EXTERNAL NAME [Database1].[NameInC#];

I get an error:

Msg 10343, Level 14, State 1, Line 10
CREATE or ALTER ASSEMBLY for assembly 'Database1' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

How to solve this?

There are two solutions given in the error message. Which one did you try? — Andrew Morton, Jan 15 '20 at 12:50
By reading what it says and following the advice given: `you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.` OR, if that doesn't suit you, as an alternative, `you can trust the assembly using sp_add_trusted_assembly.` — TechGnome, Jan 15 '20 at 12:51
@Lazar It would have been a good idea to say that in the question ;) Were there any error messages when you tried those solutions? — Andrew Morton, Jan 15 '20 at 13:46
@AndrewMorton sry, my mistake:) yes, sql gives me an error too — Lazar, Jan 15 '20 at 13:59
@Lazar OK, then you should [edit](https://stackoverflow.com/posts/59751673/edit) your question to add everything you did and the error messages. — Andrew Morton, Jan 15 '20 at 14:01

Solomon Rutzky · Answer 1 · 2020-01-15T14:43:26.297

```
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
```
There is / was no reason to execute this command. It really doesn't hurt anything, but it served no purpose either.
The error message clearly stated two options to get around this. Please do NOT enable TRUSTWORTHY. That is the easier / lazier route, but is a security risk that is unnecessary to open when the alternative isn't difficult to implement. For a detailed look at the issues surrounding TRUSTWORTHY, please see:
PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
For more explanation on "CLR strict security" (which was introduced in SQL Server 2017) and how to handle it properly, please see my answer to the following question (here on S.O.):

CLR Strict Security on SQL Server 2017

The basic concept is this (which is the other option stated in the error message):
1. Sign the assembly (using either a strong name key — which equates to an asymmetric key in SQL Server — or a certificate, or both). If you are compiling in Visual Studio, then "signing" (under project properties) means giving it a "strong name" (i.e. not using a certificate)
2. create the corresponding public key in SQL Server, in the [master] database (strong name key == CREATE ASYMMETRIC KEY ...; certificate == CREATE CERTIFICATE ...). For now you can create either one of those from the DLL file. If this code is to move into production, it is best to not rely on a DLL file as that is an external dependency on the release script. There are two approaches to fully automating this process, and those options are covered in the following two posts:
  - SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
  - SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2
3. Create a login from the asymmetric key or certificate
4. Grant the signature-based login the UNSAFE ASSEMBLY permission
5. Now you can create the assembly in any database, and no need to enable TRUSTWORTHY
6. Finally create the T-SQL wrapper objects

For more info on working with SQLCLR in general, please visit: SQLCLR Info

score 1 · Answer 2 · answered Nov 01 '20 at 13:12

Had same problem on SQL Server 2017. Solved it by execution simple script:

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE; 
EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;

Reason that SQL Server 2017 introduced a new server-level configuration option called "CLR strict security", and it is enabled by default.

This option requires that all Assemblies, even the SAFE ones, should be signed with a certificate or a strong name key. The Certificate (or the Asymmetric Key) used to do the signing needs to be loaded into the [master] and have a Login created from it. This Login in turn needs to have been granted the UNSAFE ASSEMBLY permission.

You should also confirm the system requirements concerning the supported SQL version for your XProtect release as this is indicative that you are running on a unsupported/untested configuration.

SQL Server 2017 Management Studio: CREATE ASSEMBLY error

2 Answers2