Is it bad to put WITH PERMISSION_SET = UNSAFE in SQL 2005?

Question

Hi

I need to use CLR to make a stored procedure that uses stuff in .net 3.5. If I don't put Permission_Set = UnSafe I can't do it it will just die and give me this error

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. An error occurred while the batch was being executed.

So I found this article

http://weblogs.asp.net/paulomorgado/archive/2009/06/13/playing-with-sql-server-clr-integration-part-iv-deploying-to-sql-server-2005.aspx

And the last line says this

"Now the DBAs won’t definitely let me use this, but it was fun to build it." I am not sure if he was referring to the permissions being set to unsafe.

So can some huge gaping hole happen if you do this?

Answer 1

There are three different permission_set options which restrict what the assembly can do

SAFE - Restricts the assembly to managed code

EXTERNAL_ACCESS - allows access to files, network resources, etc..

UNSAFE - Unrestricted access - including the execution of non-managed code

MSDN docs give the following guidance

Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies.

If your assembly only uses features of .NET 3.5, i don't see why it would need UNSAFE access.

It's possible you are using one of the types or members disallowed from the System.Core library. Microsoft has a list of these. Disallowed Types and Members in System.Core.dll

There is some more info here. Host Protection Attributes and CLR Integration Programming

Answer 2

Sorry to state the obvious, but what part of "UNSAFE" is hard to comprehend?

You can:

• destroy your SQL Server and OS installation
• introduce memory leaks
• add instability

I assume related to your question "How to make this CLR work with 2005?" where you want to use methods that could have the latter two side effects...