While deploying SQLCLR Assembly to SQL Server 2016 or later, CREATE ASSEMBLY fails with the following error
Creating [Assembly name]... Msg 10343, Level 14, State 1, Line 65 CREATE or ALTER ASSEMBLY for assembly 'Assembly name' 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. ** An error was encountered during execution of batch. Exiting.
The error is because assembly is built as Untrusted. In your visual studio project properties, navigate to Project setting – Database Setting – select the Miscellaneous Tab – Check the “Trustworthy” checkbox and click OK.
Rebuild the project and publish it again.
While accessing the SQLCLR Assembly you may encounter the following error
Msg 6522, Level 16, State 1, Line 4 A .NET Framework error occurred during execution of user-defined routine or aggregate "Domaincheck": System.Net.NetworkInformation.PingException: An exception occurred during a Ping request. ---> System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=188.8.131.52, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.Dns.GetHostAddresses(String hostNameOrAddress) at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress, Int32 timeout, Byte buffer, PingOptions options) System.Net.NetworkInformation.PingException: at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress, Int32 timeout, Byte buffer, PingOptions options) at System.Net.NetworkInformation.Ping.Send(String hostNameOrAddress) at UserDefinedFunctions.Domaincheck()
The assembly permission is set to Safe which is prohibiting from execution , set it to EXTERNAL_ACCESS and try again.
USE [Database Name] GO ALTER ASSEMBLY [Name] WITH PERMISSION_SET = EXTERNAL_ACCESS GO