Executing an external process() in SQLCLR Project

I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.

The following code:

Process newProcess = new Process();

string path = @."C:\TEST.BAT";

newProcess.StartInfo.FileName = path;

newProcess.Start();

Executes without error, but does not actually run the external.

Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.

So, am I out of luck?

Is your assembly deployed at the UNSAFE permission level? What happens if you try to run the same code in a console app run under the same user account (SQL Server service account or impersonated account, as appropriate)?|||

Hi Nicole - thx for responding.

I am able to execute a console app - the issue is that SQL Server will not allow touching any network drive or network resource period (from a SQLCLR Project). So my cmd app can update a DB on the C drive, but not one of my network drives.

As well, I can establish an OLEDB connection (Visual Foxpro) to a local directory, but not a network directory. I'm surprised that CLR will not even allow an external app to touch a network resource. Wild, eh? Although I understand why there is such security, there must be a way for me to execute an external console app that can update a DB on a network resource.

Oh - and yes, I am running the assembly at the UNSAFE permission level.

|||Might the problem be the user context rather than anything having to do with SQLCLR? Unless you deliberately impersonate another user (e.g.: via SqlContext.WindowsIdentity.Impersonate), your SQLCLR code will run under the user context of the SQL Server service account, which is highly unlikely to have any permissions on any network resources.|||

I think you're correct - it has to do with attempting to authenticate a local user (IUSR_Computername) on a network resource. I will look into impersonation (something my wife says I'm horrible at...)

You know, Nicole, you are the first person to assist me in these forums. I had actually thought of using a female handle - seems they get a pretty quick response... :)

I'll post the results of my efforts... and thx again.

|||

After impersonation, I can do something simple like use a streamwriter to create a text file on a network share. But it still throws an exception when attempting to execute an executable on the same network share. The impersonation rights are those of administrator (only for testing!). Oh, in case you're wondering - I simply change the Process StartInfo from @."C:\Mobile.exe" to @."F:\Mobile.exe", and you will have to take my word for it that the Mobile exists in both places. It runs without exception on the C drive.

What's really wild, is that the actual Mobile.exe code (clipper) will update a dbf on the C drive, but not on the F drive. What does the CLR do? Freeze all resources when in process? geez!

I'm also using:

[PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")]

So... I really thought this would allow my CLR code to access a network share. I can't understand why the answer to this wouldn't be just a little more simple, even for an old programmer like me.

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

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

Execute external command in SQL 2000

Is there a way to execute an external command in SQL 2000. For SQL 2005 I
found the xp_cmdshell procedure.
Thanks,
Tim Kelley wrote:
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
xp_cmdshell exists in SQL 2000 as well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The xp_cmdshell procedure is available in SQL Server 2000 as well.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
|||Hello,
Use XP_CMDSHELL. This extended procedure is available in Master database in
SQL 2000.
Master..xp_cmdshell 'dir c:\'
Ensure that you are not executing any looping commands or any commands with
high stress using XP_CMDSHELL.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>

Execute external command in SQL 2000

Is there a way to execute an external command in SQL 2000. For SQL 2005 I
found the xp_cmdshell procedure.
Thanks,Tim Kelley wrote:
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
xp_cmdshell exists in SQL 2000 as well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The xp_cmdshell procedure is available in SQL Server 2000 as well.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>|||Hello,
Use XP_CMDSHELL. This extended procedure is available in Master database in
SQL 2000.
Master..xp_cmdshell 'dir c:\'
Ensure that you are not executing any looping commands or any commands with
high stress using XP_CMDSHELL.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>

Execute external command in SQL 2000

Is there a way to execute an external command in SQL 2000. For SQL 2005 I
found the xp_cmdshell procedure.
Thanks,Tim Kelley wrote:
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
xp_cmdshell exists in SQL 2000 as well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The xp_cmdshell procedure is available in SQL Server 2000 as well.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>|||Hello,
Use XP_CMDSHELL. This extended procedure is available in Master database in
SQL 2000.
Master..xp_cmdshell 'dir c:'
Ensure that you are not executing any looping commands or any commands with
high stress using XP_CMDSHELL.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>