CreateProcessAsUser in SQLCLR

I'll keep trying new threads here... sooner or later, I'm sure an expert Microsoft CLR employee will gladly lend a helping hand!

The pieces:

1. SQL 2005, MS Windows Server 2003, Standard Edition, SP 1

2. .NET 2005/C#

3. Instance of SQL 2005 running locally.

Trigger on local SQL2005 DB table INSERT calls 2 CLR Functions:

1. Retrieve data from SQL2005 DB table and populate local DBF through OLEDB

2. Call external 16-bit application (written in Clipper) that iterates through local DBF records (added from step 1 above) and populates DBF on domain resource.

Step 2 detail:

External 16-bit application is called by CreateProcessAsUser after impersonating token.

THIS IS SUCCESSFUL - IF: I populate SQL2005 DB table using TSQL insert statement. The trigger executes, Step 1 and Step 2 execute perfectly!

THIS IS UNSUCCESSFUL - IF: The SQL2005 DB table is populated by synchronizing SQL Mobile Server database from a SQL Mobile Edition 2005 PDA emulation. The trigger executes. Step 1 executes perfectly. Step 2 executes without exceptions. HOWEVER, the 16-bit application does not execute! Remember, no exceptions. In fact, the result variable returns true from function below:

result = ProcessUtility.CreateProcessAsUser(

hDupedToken,

null,

@."C:\MobileDB\Mobile.exe",

ref sa, ref sa,

false, 0, IntPtr.Zero,

@."C:\MobileDB\", ref si, ref pi

);

Also, if I Right-click on the Step 2 function in the Server Explorer and click "Execute", it works perfectly. Domain DBF is updated successfully.

So, in short CLR "CreateProcessAsUser" is not doing it's job when the trigger is fired after SQL 2005 DB is populated via replication/sychronization. I would appreciate a response... Thx!

? OK, I'll bite :). First, some clarification: When your trigger is firing via an INSERT, are you connected to the SQL Server using Windows authentication or SQL Server authentication? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <DVCasmey@.discussions.microsoft.com> wrote in message news:8db71548-fdb3-4bb5-bb1f-d43a8a75a047@.discussions.microsoft.com... I'll keep trying new threads here... sooner or later, I'm sure an expert Microsoft CLR employee will gladly lend a helping hand! The pieces: 1. SQL 2005, MS Windows Server 2003, Standard Edition, SP 1 2. .NET 2005/C# 3. Instance of SQL 2005 running locally. Trigger on local SQL2005 DB table INSERT calls 2 CLR Functions: 1. Retrieve data from SQL2005 DB table and populate local DBF through OLEDB 2. Call external 16-bit application (written in Clipper) that iterates through local DBF records (added from step 1 above) and populates DBF on domain resource. Step 2 detail: External 16-bit application is called by CreateProcessAsUser after impersonating token. THIS IS SUCCESSFUL - IF: I populate SQL2005 DB table using TSQL insert statement. The trigger executes, Step 1 and Step 2 execute perfectly! THIS IS UNSUCCESSFUL - IF: The SQL2005 DB table is populated by synchronizing SQL Mobile Server database from a SQL Mobile Edition 2005 PDA emulation. The trigger executes. Step 1 executes perfectly. Step 2 executes without exceptions. HOWEVER, the 16-bit application does not execute! Remember, no exceptions. In fact, the result variable returns true from function below: result = ProcessUtility.CreateProcessAsUser( hDupedToken, null, @."C:\MobileDB\Mobile.exe", ref sa, ref sa, false, 0, IntPtr.Zero, @."C:\MobileDB\", ref si, ref pi ); Also, if I Right-click on the Step 2 function in the Server Explorer and click "Execute", it works perfectly. Domain DBF is updated successfully. So, in short CLR "CreateProcessAsUser" is not doing it's job when the trigger is fired after SQL 2005 DB is populated via replication/sychronization. I would appreciate a response... Thx!|||

Great question - and thx for answering -

I'm using NT authentication. My processes are running under IUSR_<Server Name>. I'm authenticating (impersonating) my domain login which has full rights.

Also, as another piece of information - when the 16-bit dos application runs successfully, it is closed and the process (ntvdm.exe) is no longer in the task manager. When it does NOT run successfully, it remains. The CloseHandle method passing hprocess, hthread and hdupedToken handles throw NO exceptions either.

|||? I'm betting that the impersonation is failing during the replication. Can you add some code to the trigger log the value of SqlContext.WindowsIdentity.Name in both cases? This might help you narrow down what login the process is really running as. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <DVCasmey@.discussions.microsoft.com> wrote in message news:d10bca5b-1fd1-4bee-97da-53366aeef5ee@.discussions.microsoft.com... Great question - and thx for answering - I'm using NT authentication. My processes are running under IUSR_<Server Name>. I'm authenticating (impersonating) my domain login which has full rights. Also, as another piece of information - when the 16-bit dos application runs successfully, it is closed and the process (ntvdm.exe) is no longer in the task manager. When it does NOT run successfully, it remains. The CloseHandle method passing hprocess, hthread and hdupedToken handles throw NO exceptions either.|||

The impersonation was successful in both cases - I did, however, determine that the Dos application failed when attempting to open the target DBF files with drive letters. I used the \\server\sharename - and am now successfully pushing data from a PDA, through sychronization, through SQL 2005 DB and on to my legacy application. Thank you, Adam, for your help - I also had some insider help from with MSft (don't tell anyone!)

|||

This thread was a great help! Here is my experience:

I had the similar problems where calling a clr with external_access from a trigger worked perfectly. Except when triggered from the actions of a SQL Server login with limited rights. The called function in the clr assembly simply throwed a System.NullReferenceException back to the calling .Net application on a different server.

I found it a bit easier to "impersonate" in Stored Procedure level with EXECUTE AS a Windows user with sufficient rights:

CREATE PROC SPXmlExport
(
@.invoiceID bigint
)
WITH EXECUTE AS 'DOMAIN\user'
AS
EXTERNAL NAME SPXmlExportAssembly.[NAME.CLASS].ExportStuff
GO