Created new SQL Server project in Visual studio, to generate DLL file having SQL function, when we tried to use published function we ended with below error message:
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
To fix the above issue enable CLR in sql server:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
After applying the code we tried to execute the created function, but ended in below error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "<functionname>":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort
System.Security.HostProtectionException:
at UserDefinedFunctions.<functionname>(SqlDateTime pstTime, String Timezone)
To overcome the above issue, run below commands:
ALTER ASSEMBLY <functionname> WITH PERMISSION_SET = Safe
ALTER DATABASE <DBNAME>
SET TRUSTWORTHY ON
ALTER ASSEMBLY Timezone WITH PERMISSION_SET = UnSafe
