locked
Process.Start Hangs when executed via SQL Server RRS feed

  • Question

  • I have an issue with a DLL I have created. I have a requirement for vbscript files to be executed as part of an automated application. For consistency with our other setups, I have created a C# .Net(2.0) Class Library, and written a class that will execute a specified vbscript file, with the supplied arguments. This works fine when running it via a console application (as any user related to the setup).

    However, when I hook it up into the SQL server via the create assembly command, the process hangs when executing the Process.Start block, and I have no idea why. SQL just continually waits and waits for the script to come back - to the point where we have had to restart the service to kill off the session and unlock the functions. The issue isnt with the link to the DLL however, as there is another class in the file that performs a network ping, and this works fine.

    Below is the script I am using:

     

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net.NetworkInformation;
    using System.Diagnostics;
    using Microsoft.SqlServer;
    
    namespace GIOR
    {
      [Serializable]
      public class Script_Execution
      {
        /// <summary>
        /// Execute the Specified Script, with the supplied arguments
        /// </summary>
        /// <param name="ScriptPath">Fully Qualified Script Path</param>
        /// <param name="args">Array of Arguments to pass into the script</param>
        /// <returns>Output from the script execution</returns>
        public static string ExecuteScript(string ScriptPath, string args)
        {
          List<String> lst = new List<string>(args.Split(','));
          String Args = BuildArgsString(lst);
    
          if (ScriptPath.Trim() != String.Empty)
          {
            ProcessStartInfo psi = CreatePSI();
    
            if (ScriptPath.Trim().ToLower().EndsWith("vbs"))
            {
              psi.FileName = System.Environment.GetEnvironmentVariable("WINDIR") + @"\System32\cscript.exe";
              psi.Arguments = string.Format("{0} {1} {2}", @"/nologo", ScriptPath, Args);
            }
            else
            {
              //Just Execute the file directly.
              psi.FileName = ScriptPath;
              psi.Arguments = Args;
            }
    
            return Exec(psi);
          }
          else
          {
            //No Script Path Specified, fail out immediately.
            return "No Script Path Specified";
          }
        }
    
        /// <summary>
        /// Create the Process Object and set default Params
        /// </summary>
        /// <returns>Process Object</returns>
        private static ProcessStartInfo CreatePSI()
        {
          ProcessStartInfo psi = new ProcessStartInfo();
          psi.UseShellExecute = false;
          psi.RedirectStandardOutput = true;
          psi.RedirectStandardInput = true;
          psi.RedirectStandardError = true;
          psi.WorkingDirectory = System.Environment.GetEnvironmentVariable("WINDIR");
    
          System.Security.SecureString Secpw = new System.Security.SecureString();
          foreach (Char Chr in @"Password123".ToCharArray())
          {
            Secpw.AppendChar(Chr);
          }
    
          psi.LoadUserProfile = false;
          psi.UserName = @"Test.User";
          psi.Password = Secpw;
          psi.Domain = "TESTDOMAIN";
          psi.CreateNoWindow = false;
    
          return psi;
    
        }
    
        /// <summary>
        /// Executes the Process and returns the output
        /// </summary>
        /// <param name="psi">Process Object</param>
        /// <returns>Output from the Process</returns>
        private static string Exec(ProcessStartInfo psi)
        {
          string output = string.Empty;
          string Error = string.Empty;
    
          try
          {
    
            using (System.Diagnostics.Process proc = System.Diagnostics.Process.Start(psi))
            {
              output = proc.StandardOutput.ReadToEnd();
              Error = proc.StandardError.ReadToEnd();
              //proc.WaitForExit(12000);
            }
    
            output += "\n**ERROR INFO***" + Error;
          }
          catch (Exception ex)
          {
            //output = "An Error Occurred whilst running the designated script\n";
            output += "Message: " + ex.Message + "\n";
            output += "InnerException: " + ex.InnerException + "\n";
            output += "StackTrace: " + ex.StackTrace + "\n";
            output += "TargetSite: " + ex.TargetSite + "\n";
            output += "Data:\n";
    
            foreach (string key in ex.Data.Keys)
            {
              output += key + ": " + ex.Data[key];
            }
          }
    
          return output;
        }
    
        /// <summary>
        /// Converts the List of Arguments into a string
        /// </summary>
        /// <param name="Args">List of Arguments</param>
        /// <returns>String of Arguments</returns>
        private static string BuildArgsString(List<String> Args)
        {
          System.Text.StringBuilder ScriptArgs = new StringBuilder();
          foreach (String arg in Args)
          {
            ScriptArgs.Append(arg.Trim() + " ");
          }
    
          return ScriptArgs.ToString().Trim();
        }
      }
    }
    
    

    If it helps, the script is located on another server, accessable via a $ share, for which the impersonating account has full access. Additionally, I am aware that StartProcess with impersonation doesnt work when the class is run using the local SYSTEM account, and I have ensured that the SQL Server Service is running under an AD account.

    Thursday, January 20, 2011 1:11 PM

Answers

  • In the interests of providing the answer to future people (Lots of people are asking similar questions, but I'm not seeing many answers.

    This link (http://asprosys.blogspot.com/2009/03/perils-and-pitfalls-of-launching.html) Provides details of what is causing this issue.

    I had to change my code in a couple of ways - First, I had to change how I was reading the output streams so that I could hit the WaitForExit(Timeout) line, otherwise it wouldn't terminate of it's own accord.

    Secondly, I had to add External Dll functions, and a Reference to AsproLock.dll (http://www.asprosys.com/resources/projects/asprolock/default.aspx) and associated code in order to permit the user account to have access to the running resource.

    Below is the fully working code:

     

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net.NetworkInformation;
    using System.Diagnostics;
    using Microsoft.SqlServer;
    using System.Runtime.InteropServices;
    using System.Threading;
    using Asprosys.Security.AccessControl;
    
    namespace GIOR{  
    
      public class Script_Execution
      {
        #region External DLL (WinAPI) Methods
    
        [DllImport("user32.dll", SetLastError = true)]
        public static extern IntPtr GetProcessWindowStation();
    
        [DllImport("user32.dll", SetLastError = true)]
        public static extern IntPtr GetThreadDesktop(int dwThreadId);
    
        [DllImport("kernel32.dll", SetLastError = true)]
        public static extern int GetCurrentThreadId();
    
        #endregion    
    
        private StringBuilder Output { get; set; }
        private StringBuilder Error { get; set; }
    
        /// <summary>
        /// Execute the Specified Script, with the supplied arguments
        /// </summary>
        ///<param name="ScriptPath">Fully Qualified Script Path</param>
        /// <param name="args">Array of Arguments to pass into the script</param>
        /// <returns>Output from the script execution</returns>
        public static string ExecuteScript(string ScriptPath, string args)
        {
          //Instantiating the class to prevent any crossover with the Output and Error async properties between different calls
          //(Since they were static, the string's persisted between different calls).
          Script_Execution thisclass = new Script_Execution();
          return thisclass.DoExecuteScript(ScriptPath, args);
        }
    
        /// <summary>
        /// Execute the Specified Script, with the supplied arguments
        /// </summary>
        /// <param name="ScriptPath">Fully Qualified Script Path</param>
        /// <param name="args">Array of Arguments to pass into the script</param>
        /// <returns>Output from the script execution</returns>
        private string DoExecuteScript(string ScriptPath, string args)
        {
          List<String> lst = new List<string>(args.Split(','));
          String Args = BuildArgsString(lst);
    
          if (ScriptPath.Trim() != String.Empty)
          {
            ProcessStartInfo psi = CreatePSI();
    
            //If the file is a vbscript file, then set it up to execute the Cscript.exe with the correct arguments.
            if (ScriptPath.Trim().ToLower().EndsWith("vbs"))
            {
              psi.FileName = System.Environment.GetEnvironmentVariable("WINDIR") + @"\System32\cscript.exe";
              psi.Arguments = string.Format("{0} {1} {2}", @"/nologo", "\"" + ScriptPath + "\"", Args);
            }
            else
            {
              //Just Execute the file directly.
              psi.FileName = ScriptPath;
              psi.Arguments = Args;
            }
    
            return Exec(psi);
          }
          else
          {
            //No Script Path Specified, fail out immediately.
            return "No Script Path Specified";
          }
        }
    
        /// <summary>
        /// Create the Process Object and set default Params
        /// </summary>
        /// <returns>Process Object</returns>
        private ProcessStartInfo CreatePSI()
        {
          ProcessStartInfo psi = new ProcessStartInfo();
          psi.UseShellExecute = false;
          psi.RedirectStandardOutput = true;
          psi.RedirectStandardError = true;
          psi.ErrorDialog = true;
          psi.WindowStyle = ProcessWindowStyle.Hidden;
          psi.CreateNoWindow = true;
    
          //Set the working Directory to Windows. Otherwise it will throw accessdenied errors.
          psi.WorkingDirectory = System.Environment.GetEnvironmentVariable("WINDIR");
    
          //Build up the Password.
          System.Security.SecureString Secpw = new System.Security.SecureString();
          foreach (Char Chr in @"Password123".ToCharArray())
          {
            Secpw.AppendChar(Chr);
          }
    
          psi.LoadUserProfile = false;
          psi.UserName = @"Test.User";
          psi.Password = Secpw;
          psi.Domain = "TESTDOMAIN";
          
    
          return psi;
    
        }
    
        /// <summary>
        /// Executes the Process and returns the output
        /// </summary>
        /// <param name="psi">Process Object</param>
        /// <returns>Output from the Process</returns>
        private string Exec(ProcessStartInfo psi)
        {
          string output = string.Empty;
    
          Output = new StringBuilder();
          Error = new StringBuilder();
    
          try
          {
            //Configure the user account to have access to the Windows Station and Desktop
            //The the service is running in. If we dont do this, then the command will execute and immediately hang.
            IntPtr hWinSta = GetProcessWindowStation();
            WindowStationSecurity ws = new WindowStationSecurity(hWinSta,
             System.Security.AccessControl.AccessControlSections.Access);
            ws.AddAccessRule(new WindowStationAccessRule(@"TESTDOMAIN\Test.User",
              WindowStationRights.AllAccess, System.Security.AccessControl.AccessControlType.Allow));
            ws.AcceptChanges();
    
            IntPtr hDesk = GetThreadDesktop(GetCurrentThreadId());
            DesktopSecurity ds = new DesktopSecurity(hDesk,
              System.Security.AccessControl.AccessControlSections.Access);
            ds.AddAccessRule(new DesktopAccessRule(@"TESTDOMAIN\Test.User",
              DesktopRights.AllAccess, System.Security.AccessControl.AccessControlType.Allow));
            ds.AcceptChanges();
    
            //Run the process and get the output.
            using (System.Diagnostics.Process proc = System.Diagnostics.Process.Start(psi))
            {
              proc.OutputDataReceived += new DataReceivedEventHandler(ReceiveStandardOutput);
              proc.ErrorDataReceived += new DataReceivedEventHandler(ReceiveStandardError);
              
              proc.BeginOutputReadLine();
              proc.BeginErrorReadLine();
    
              proc.WaitForExit(120000);           
            }
            
            //Concatenate the Output Information.
            if (Output != null) { output = Output.ToString(); }
    
            if (Error.ToString() != String.Empty)
            {
              output += "\n***SCRIPT ERROR INFO***\n" + Error.ToString();
            }
          }
          catch (Exception ex)
          {
            output = "***PROCESS ERROR INFO***\n";
            output += "Message: " + ex.Message + "\n";
            output += "InnerException: " + ex.InnerException + "\n";
            output += "StackTrace: " + ex.StackTrace + "\n";
            output += "TargetSite: " + ex.TargetSite + "\n";
            output += "Data:\n";
    
            foreach (string key in ex.Data.Keys)
            {
              output += key + ": " + ex.Data[key];
            }
          }
    
          return output;
        }
    
        /// <summary>
        /// Converts the List of Arguments into a string
        /// </summary>
        /// <param name="Args">List of Arguments</param>
        /// <returns>String of Arguments</returns>
        private string BuildArgsString(List<String> Args)
        {
          System.Text.StringBuilder ScriptArgs = new StringBuilder();
          foreach (String arg in Args)
          {
            ScriptArgs.Append(arg.Trim() + " ");
          }
    
          return ScriptArgs.ToString().Trim();
        }
    
        #region Output Handlers
    
        private void ReceiveStandardOutput(object sendingProcess, DataReceivedEventArgs outLine)
        {
          if (!String.IsNullOrEmpty(outLine.Data))
          {
            if (Output == null) { Output = new StringBuilder(); }
    
            Output.AppendLine(outLine.Data);
          }
    
        }
    
        private void ReceiveStandardError(object sendingProcess, DataReceivedEventArgs outLine)
        {
          if (!String.IsNullOrEmpty(outLine.Data))
          {
            if (Error == null) { Error = new StringBuilder(); }
    
            Error.AppendLine(outLine.Data);
          }
    
        }
    
        #endregion
    
      }
    }
    
    
    Friday, January 21, 2011 4:32 PM

All replies

  • hi,

    If it helps, the script is located on another server, accessable via a $ share, for which the impersonating account has full access. Additionally, I am aware that StartProcess with impersonation doesnt work when the class is run using the local SYSTEM account, and I have ensured that the SQL Server Service is running under an AD account.

    Have you tested to run your test executable using your library in a console when logged in on your SQL Server AD account?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, January 20, 2011 1:23 PM
  • I have. I've created a console App, and ran it as several users: Myself, the SQL Server account, and the actual account that is being impersonated.

     

    I have also created a cmd shell using runas for the user account that the script should be running under, and ensured that it is able to run the cscript file as it would be running from the DLL.

    Thursday, January 20, 2011 1:30 PM
  • Oh, I meant to add the SQL Scripts for reference too:
    Create ASSEMBLY GIORFunctions
    From 'C:\SQLCLR\GIOR_Class.dll'
    With Permission_Set = UNSAFE;
    Go
    
    Create Function GI_PingMachine(@HostNameorAddress nVarchar(max))
    Returns bit
    With Execute as Caller
    as
    External Name [GIORFunctions].[GIOR.Network_Tasks].[PingMachine]
    Go
    
    Create Function GI_ExecuteScript(@ScriptPath nVarchar(max), @Args nVarchar(max))
    Returns nVarchar(max)
    With Execute as Caller
    as
    External Name [GIORFunctions].[GIOR.Script_Execution].[ExecuteScript];
    Go
    
    --Execute Command:
    Select dbo.GI_ExecuteScript('[Filepath]\ServerThere.vbs', 
    								'[SERVERNAME]');
    
    Thursday, January 20, 2011 1:32 PM
  • btw, I don't think it hangs. The problem is that your code does not wait for the process to terminat.

    See the remarks:

    http://msdn.microsoft.com/en-us/library/e8zac0ca.aspx

    The process is started and runs then async to your calling process, the library Exec() method. so it returns imho immediatly after starting the process.

    You need to wait:

    http://msdn.microsoft.com/en-us/library/fb4aw7b8.aspx
    http://msdn.microsoft.com/en-us/library/xb73d10t%28v=vs.71%29.aspx


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, January 20, 2011 1:50 PM
  • Ah sorry, I should have clarified - I've tried it both with and without the proc.WaitForExit(12000); being uncommented. In both cases it does the same.

     

    It's also not returning immediately. The server query continues to run indefinately (well beyond the 2 mins specified as a timeout). If I comment out the using block, and replace it with a line setting the output to "Script Run", the SQL query returns almost immediately with this value.

    Thursday, January 20, 2011 1:54 PM
  • Further Info if it helps, Executing the SQL function does generate the cscript.exe on the server. Monitoring it on Process Explorer shows a number of details as expected:

     

    The User is correctly listed as the impersonated user, the Command Line field correctly shows the full command based on what is entered (running a separate CMD shell and copying in exactly what appears here works correctly). The performance graph tab shows about 100MB under "Private Bytes", but no CPU usage.

    Thursday, January 20, 2011 2:42 PM
  • Ok. So I have narrowed down the actual cause.

     

    If I remove the impersonation lines of code from the CreatePSI function, and grant the user that SQL Server is running under access to the share, the script executes successfully. But always hangs when the impersonation is in place (even if the script file is moved to the C:\ directory).

     

    I have no idea how to solve it, but at least I know where the problem is.

    Thursday, January 20, 2011 4:55 PM
  • In the interests of providing the answer to future people (Lots of people are asking similar questions, but I'm not seeing many answers.

    This link (http://asprosys.blogspot.com/2009/03/perils-and-pitfalls-of-launching.html) Provides details of what is causing this issue.

    I had to change my code in a couple of ways - First, I had to change how I was reading the output streams so that I could hit the WaitForExit(Timeout) line, otherwise it wouldn't terminate of it's own accord.

    Secondly, I had to add External Dll functions, and a Reference to AsproLock.dll (http://www.asprosys.com/resources/projects/asprolock/default.aspx) and associated code in order to permit the user account to have access to the running resource.

    Below is the fully working code:

     

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net.NetworkInformation;
    using System.Diagnostics;
    using Microsoft.SqlServer;
    using System.Runtime.InteropServices;
    using System.Threading;
    using Asprosys.Security.AccessControl;
    
    namespace GIOR{  
    
      public class Script_Execution
      {
        #region External DLL (WinAPI) Methods
    
        [DllImport("user32.dll", SetLastError = true)]
        public static extern IntPtr GetProcessWindowStation();
    
        [DllImport("user32.dll", SetLastError = true)]
        public static extern IntPtr GetThreadDesktop(int dwThreadId);
    
        [DllImport("kernel32.dll", SetLastError = true)]
        public static extern int GetCurrentThreadId();
    
        #endregion    
    
        private StringBuilder Output { get; set; }
        private StringBuilder Error { get; set; }
    
        /// <summary>
        /// Execute the Specified Script, with the supplied arguments
        /// </summary>
        ///<param name="ScriptPath">Fully Qualified Script Path</param>
        /// <param name="args">Array of Arguments to pass into the script</param>
        /// <returns>Output from the script execution</returns>
        public static string ExecuteScript(string ScriptPath, string args)
        {
          //Instantiating the class to prevent any crossover with the Output and Error async properties between different calls
          //(Since they were static, the string's persisted between different calls).
          Script_Execution thisclass = new Script_Execution();
          return thisclass.DoExecuteScript(ScriptPath, args);
        }
    
        /// <summary>
        /// Execute the Specified Script, with the supplied arguments
        /// </summary>
        /// <param name="ScriptPath">Fully Qualified Script Path</param>
        /// <param name="args">Array of Arguments to pass into the script</param>
        /// <returns>Output from the script execution</returns>
        private string DoExecuteScript(string ScriptPath, string args)
        {
          List<String> lst = new List<string>(args.Split(','));
          String Args = BuildArgsString(lst);
    
          if (ScriptPath.Trim() != String.Empty)
          {
            ProcessStartInfo psi = CreatePSI();
    
            //If the file is a vbscript file, then set it up to execute the Cscript.exe with the correct arguments.
            if (ScriptPath.Trim().ToLower().EndsWith("vbs"))
            {
              psi.FileName = System.Environment.GetEnvironmentVariable("WINDIR") + @"\System32\cscript.exe";
              psi.Arguments = string.Format("{0} {1} {2}", @"/nologo", "\"" + ScriptPath + "\"", Args);
            }
            else
            {
              //Just Execute the file directly.
              psi.FileName = ScriptPath;
              psi.Arguments = Args;
            }
    
            return Exec(psi);
          }
          else
          {
            //No Script Path Specified, fail out immediately.
            return "No Script Path Specified";
          }
        }
    
        /// <summary>
        /// Create the Process Object and set default Params
        /// </summary>
        /// <returns>Process Object</returns>
        private ProcessStartInfo CreatePSI()
        {
          ProcessStartInfo psi = new ProcessStartInfo();
          psi.UseShellExecute = false;
          psi.RedirectStandardOutput = true;
          psi.RedirectStandardError = true;
          psi.ErrorDialog = true;
          psi.WindowStyle = ProcessWindowStyle.Hidden;
          psi.CreateNoWindow = true;
    
          //Set the working Directory to Windows. Otherwise it will throw accessdenied errors.
          psi.WorkingDirectory = System.Environment.GetEnvironmentVariable("WINDIR");
    
          //Build up the Password.
          System.Security.SecureString Secpw = new System.Security.SecureString();
          foreach (Char Chr in @"Password123".ToCharArray())
          {
            Secpw.AppendChar(Chr);
          }
    
          psi.LoadUserProfile = false;
          psi.UserName = @"Test.User";
          psi.Password = Secpw;
          psi.Domain = "TESTDOMAIN";
          
    
          return psi;
    
        }
    
        /// <summary>
        /// Executes the Process and returns the output
        /// </summary>
        /// <param name="psi">Process Object</param>
        /// <returns>Output from the Process</returns>
        private string Exec(ProcessStartInfo psi)
        {
          string output = string.Empty;
    
          Output = new StringBuilder();
          Error = new StringBuilder();
    
          try
          {
            //Configure the user account to have access to the Windows Station and Desktop
            //The the service is running in. If we dont do this, then the command will execute and immediately hang.
            IntPtr hWinSta = GetProcessWindowStation();
            WindowStationSecurity ws = new WindowStationSecurity(hWinSta,
             System.Security.AccessControl.AccessControlSections.Access);
            ws.AddAccessRule(new WindowStationAccessRule(@"TESTDOMAIN\Test.User",
              WindowStationRights.AllAccess, System.Security.AccessControl.AccessControlType.Allow));
            ws.AcceptChanges();
    
            IntPtr hDesk = GetThreadDesktop(GetCurrentThreadId());
            DesktopSecurity ds = new DesktopSecurity(hDesk,
              System.Security.AccessControl.AccessControlSections.Access);
            ds.AddAccessRule(new DesktopAccessRule(@"TESTDOMAIN\Test.User",
              DesktopRights.AllAccess, System.Security.AccessControl.AccessControlType.Allow));
            ds.AcceptChanges();
    
            //Run the process and get the output.
            using (System.Diagnostics.Process proc = System.Diagnostics.Process.Start(psi))
            {
              proc.OutputDataReceived += new DataReceivedEventHandler(ReceiveStandardOutput);
              proc.ErrorDataReceived += new DataReceivedEventHandler(ReceiveStandardError);
              
              proc.BeginOutputReadLine();
              proc.BeginErrorReadLine();
    
              proc.WaitForExit(120000);           
            }
            
            //Concatenate the Output Information.
            if (Output != null) { output = Output.ToString(); }
    
            if (Error.ToString() != String.Empty)
            {
              output += "\n***SCRIPT ERROR INFO***\n" + Error.ToString();
            }
          }
          catch (Exception ex)
          {
            output = "***PROCESS ERROR INFO***\n";
            output += "Message: " + ex.Message + "\n";
            output += "InnerException: " + ex.InnerException + "\n";
            output += "StackTrace: " + ex.StackTrace + "\n";
            output += "TargetSite: " + ex.TargetSite + "\n";
            output += "Data:\n";
    
            foreach (string key in ex.Data.Keys)
            {
              output += key + ": " + ex.Data[key];
            }
          }
    
          return output;
        }
    
        /// <summary>
        /// Converts the List of Arguments into a string
        /// </summary>
        /// <param name="Args">List of Arguments</param>
        /// <returns>String of Arguments</returns>
        private string BuildArgsString(List<String> Args)
        {
          System.Text.StringBuilder ScriptArgs = new StringBuilder();
          foreach (String arg in Args)
          {
            ScriptArgs.Append(arg.Trim() + " ");
          }
    
          return ScriptArgs.ToString().Trim();
        }
    
        #region Output Handlers
    
        private void ReceiveStandardOutput(object sendingProcess, DataReceivedEventArgs outLine)
        {
          if (!String.IsNullOrEmpty(outLine.Data))
          {
            if (Output == null) { Output = new StringBuilder(); }
    
            Output.AppendLine(outLine.Data);
          }
    
        }
    
        private void ReceiveStandardError(object sendingProcess, DataReceivedEventArgs outLine)
        {
          if (!String.IsNullOrEmpty(outLine.Data))
          {
            if (Error == null) { Error = new StringBuilder(); }
    
            Error.AppendLine(outLine.Data);
          }
    
        }
    
        #endregion
    
      }
    }
    
    
    Friday, January 21, 2011 4:32 PM