Permission Set Safe SqlClr Dlls

[tcartwright]

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.


http://msdn.microsoft.com/en-us/library/ms345101.aspx
SAFE ACCESS

Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.

Only the context-connection can be used and the connection string can only specify a value of "context connection=true" or "context connection=yes". Blank passwords are not permitted.


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
http://dl.dropbox.com/u/4701425/TriggerError.PNG

Here is the trigger:

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%20Trigge r%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

[tcartwright]

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

[bruce]

We were not able to "crash" the SQL server w/ CLR code. The problem is that there's not resource governing nor process segregation (like in IIS). ie. if someone wrote something dumb (like a infinite loop), it will take up all CPU resource on the server

[tcartwright]

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

[tcartwright]

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

[bruce]

thanks for your suggestion. The last time we looked at this was back when SQL 2005 was released. I'll put this on the list of potential things to look into.

Thanks for your interest.

[tcartwright]

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

[bruce]

yeah.. we looked at that already. the problem is that you can only create up to 10 pools per server.