Permission Set Safe SqlClr Dlls

First off let me start with that I read this thread : Enabling CLR in SQL Server 2005 http://community.discountasp.net/showthread.php?p=12330 and I completely understand the position stated.

I would like to inquire as to what permission_level it was that you were able to cause the server to crash? Was it safe, or the other two levels unsafe, and external_access? If it was safe, was the same effect reproducible in normal t-sql? I would like to propose a solution that would allow you guys to allow only safe mode sqlcrl dlls.

If you are not able to reproduce the error using safe mode, could you let us use safe mode sqlclr dlls? I wrote a server trigger that you could use to block the other two access modes. I have tested it from visual studio deploy, as well as using the create assembly method in enterprise manager. This is a server level trigger, and would automatically work for all old, and any new databases.

Here is what you get from Visual Studio


Here is the trigger:

Code:

if  exists (select * from master.sys.server_triggers where parent_class_desc = 'SERVER' AND name = N'safe_only_assemblies')
drop trigger [safe_only_assemblies] on all server
go

create trigger safe_only_assemblies 
on all server 
for CREATE_ASSEMBLY, ALTER_ASSEMBLY
as 
	/*	Example of the xml returned by eventdate found here
		http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Implementation%20of%20DDL%20Trigger%20in%20SQL%20Server%202005
	*/
	print 'Create Assembly issued!'
	declare @eventdata nvarchar(max)
	select @eventdata = lower(cast( eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as nvarchar(max)))
	if patindex('%external_access%', @eventdata ) > 0 or patindex('%unsafe%', @eventdata ) > 0
	begin
		raiserror ( 'You can not create or alter an assembly unless the level is safe.', 10, 1) with seterror
		rollback
	end 	
go

 

Any reply to this? Just curious if you guys would try this out.

 

A infinite loop would eventually exception out? Leaving the appdomain, and sql server intact?

 

I have been researching, and researching, and I just can't find any evidence of sqlclr causing the behavior you mention unless it is running as unsafe. Sorry, don't mean to be pushy.

Has some good information about the separation of the layers.
http://www.devx.com/codemag/Article/31193/1763/page/2

 

I actually found something that may help you manage out of control sqlclr dlls for sql server 2008. I am not positive, but it looks like you can create a new pool that you can then use to manage them.

Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866.aspx