System.Security.HostProtectionException when Executing User Defined Function on SQL Server

Question

I wrote a User Defined Function that gets a UtcTimeStamp and a windowstimezoneid as parameters and returns the TimeStamp for the TimeZone.

But if i want to execute it in a simple select i get the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "ToLocalTime": 
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: 
   bei TimeFunctions.ToLocalTime(DateTime UtcTimestamp, String WindowsTimeZoneId)

I execute the select as sa. I set clr enabled to 1. I use SQL Server 2008 R2 (10.50.1600).

Does anyone know what i have to set to get this working or what i may done wrong?

Answer 1

CLR assemblies have trust levels.

This one requires UNSAFE permissions because of the rights required to use "MayLeakOnAbort"

Either change the CLR to something safer, or re-add the assembly with UNSAFE rights. The word "UNSAFE" is exactly that of course...

Answer 2

In my case I found that changing a HashSet to a List in my CLR code made this issue go away. Have no idea why that is. Hopefully that might help someone.