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?

gbn · Accepted Answer · 2011-07-04 08:16:47Z

11

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...

answered Jul 4, 2011 at 8:16

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mabstrei Over a year ago

thanks had to set trustworthy on first and changed the database owner to sa, but now it works with unsafe.

Rahul Over a year ago

@gbn I have created assembly and also given Unsafe permission there But still getting the error.

garryp · Accepted Answer · 2015-09-14 16:11:16Z

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.

answered Sep 14, 2015 at 16:11

garryp

5,7861 gold badge31 silver badges43 bronze badges

1 Comment

roman Over a year ago

Had to found this by myself, but I think this one deserves to be upvoted so people know about this

Neil.Work · Accepted Answer · 2019-08-23 16:28:56Z

1

Here is a list of types and members that the CLR will reject because they MayLeakOnAbort, note this URL is for SQL 2017.

MayLeakOnAbort Members and Types

So check if you are using any of those and see if you can change it. Otherwise you will have to mark UNSAFE or something similar

answered Aug 23, 2019 at 16:28

Neil.Work

1,0258 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related