Execute external process from CLR based Stored procedure

Hi All,

I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine.

Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet.

-

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void RunProc(string arg)

{

SqlPipe pipe = SqlContext.Pipe;

pipe.Send("Hello");

Process.Start("E:\test.exe");

}

}

CREATE ASSEMBLY [RunProcess]

FROM 'RunProcess.dll'

CREATE PROCEDURE dbo.sqlclr_RunProc

(

@.arg nvarchar(1024)

)

AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]

--

Thanks

Sid

Also the Error that i get is

Msg 6522, Level 16, State 1, Procedure sqlclr_RunProc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'sqlclr_RunProc':

System.Security.SecurityException: Request failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)

at StoredProcedures.RunProc(String arg)

|||hi

i have done my sp to write eventlog in .txt i too faced some problems try these queries

use master
go
exec sp_dbcmptlevel 'databasename', 90
go

to know more about it see

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

then make the sql server databae trustworthy

ALTER DATABASE databasename SET TRUSTWORTHY ON

try this and let me now|||Your assembly the you deploy to the the database has to have the unsafe permission set:

Code Snippet

CREATE ASSEMBLY some_name
FROM 'some_path'
WITH PERMISSION_SET = UNSAFE

However, in order to create an unsafe assembly some other permissions need to be in place. This can be done in two ways:
1. by using certificates
2. by setting the database to be trustworthy and to grant UNSAFE ASSEMBLY to the login of the owber of the database.

The second way is the easiest, but I would not recommend it for production. Assuming the database is created by dbo and the dbo is also admin on the box, you do something like this:

Code Snippet

use master;
go

GRANT UNSAFE ASSEMBLY to [Builtin\Administrators];
go

ALTER database your_db_name
set trustworthy on;
go

Oh, and notice that when you then finally execute your proc and your external process runs, it will run under the SQL Server Service account.

Hope this helps.

Niels

|||

Thanks Neils, That helps.

|||Hi

altering database to trustworthy only for executing sql clr is very easy but not advicable method too
see my below post u will get some more option to do this without enabling trustworthy for the database

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2027982&SiteID=1

Thanks