CODEWITHSUNDEEP
c#sql-serversqlclr
BeginnerCoding
BeginnerCoding

Reputation: 23

Error publish user defined type in C# to SQL Server

I am using Microsoft SQL Server 2017 Express edition and Visual Studio 2017.

I followed this tutorial https://www.youtube.com/watch?v=nczucm60YF0&t=2s, but I get this error:

Executing publish script on database 'database name'

Full message is:

Creating [SQLCLRUDTypeDemo]... (94,1):
SQL72014: .Net SqlClient Data Provider:

Msg 10343, Level 14, State 1, Line 1
CREATE or ALTER ASSEMBLY for assembly 'SQLCLRUDTypeDemo' 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.

(94,0): SQL72045: Script execution error.
The executed script:
CREATE ASSEMBLY [SQLCLRUDTypeDemo]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E6BA245E0000000000000000E00022200B013000000A0000000600000000000066280000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000142800004F00000000400000C802000000000000000000000000000000000000006000000C000000DC2600001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000006C08000000200000000A000000020000000000000000000000000000200000602E72737263000000C80200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000
An error occurred while the batch was being executed.

What is the solution? I didn't find this problem anywhere. I found with an error in SQL Server, but not in C#.

Upvotes: 0

Views: 565

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

I didn't find this problem anywhere. I found with an error in SQL Server, but not in C#.

There error is not in .NET or C#. This is a permissions error coming from SQL Server. When searching for info on errors in SQL Server, you should, at the very least, include the "Msg" number and "Level". For example: "Msg 10343, Level 14" should be part of what you search for, including the double-quotes. It also helps to include "SQL Server" as part of the search term.

For info on how to properly sign and publish SQLCLR assemblies without disabling security (and even within the context of Visual Studio), please see my answer to another S.O. question, especially the second part (below the horizontal line):

CLR Strict Security on SQL Server 2017

Upvotes: 1

Related Questions