WMI 使用（安全）

使执行sql身份与执行程序身份分离。提高sql安全。

 

note：两个执行时必须在同一机器上，否则权限被拒绝。

 

The Database permission type set Unsafe.

 

        /// <summary>
        /// Execute a command on a remote target workstation
        /// </summary>
        /// <param name="command">The command string, both command and its param is required</param>
        /// <param name="computerName">The full qualified domain Name of Remote workstation</param>
        /// <param name="exitStatus">0:Failed 1: Success -1: Exception is thrown</param>
        /// <param name="errorMessage">Return exception info</param>
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void RemoteCmdExec(string command,string computerName,out SqlInt32 exitStatus,out SqlString errorMessage)
        {
            errorMessage = string.Empty;
            WindowsImpersonationContext impersonatedContext = null;
            try
            {
                WindowsIdentity currentIdentity = SqlContext.WindowsIdentity;
                impersonatedContext = currentIdentity.Impersonate(); //提高身份              

                ConnectionOptions connOptions = new ConnectionOptions();
                connOptions.Impersonation = ImpersonationLevel.Impersonate;
                connOptions.EnablePrivileges = true;
                ManagementScope manScope = new ManagementScope(String.Format(CultureInfo.InvariantCulture, @"//ROOT/CIMV2", computerName), connOptions);
                manScope.Connect();
                ObjectGetOptions objectGetOptions = new ObjectGetOptions();
                ManagementPath managementPath = new ManagementPath("Win32_Process");
                ManagementClass processClass = new ManagementClass(manScope, managementPath, objectGetOptions);
                ManagementBaseObject inParams = processClass.GetMethodParameters("Create");
                inParams["CommandLine"] = command;
                ManagementBaseObject outParams = processClass.InvokeMethod("Create", inParams, null);
                if (outParams["returnValue"].ToString() == "0")
                {
                    exitStatus = 1;
                }
                else
                {
                    exitStatus = 0;
                }

                if (impersonatedContext != null)
                {
                    impersonatedContext.Undo();
                }
            }
            catch (Exception e)
            {
                if (impersonatedContext != null)
                {
                    impersonatedContext.Undo();
                }

                exitStatus = -1;
                errorMessage = e.Message + ", " + e.StackTrace;
            }           
        }

        /// <summary>
        /// Get system info for a target workstation
        /// </summary>
        /// <param name="computerName">The Full qulified domain name of a target workstation</param>
        /// <param name="cpuType">Cpu Type</param>
        /// <param name="systemDirectory">system Directory</param>
        /// <param name="operationType">Operation system type</param>
        /// <param name="ExecuteResult">0:Failed 1: Success</param>
        /// <param name="errorMessage">Return exception info</param>
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void GetSystemInfo(string computerName,out SqlString cpuType,out SqlString systemDirectory,out SqlString operationType,out SqlInt32 exitStatus,out SqlString errorMessage)
        {
            errorMessage = string.Empty;
            cpuType = string.Empty;
            systemDirectory = string.Empty;
            operationType = string.Empty;
            WindowsImpersonationContext impersonatedContext = null;
            try
            {
                WindowsIdentity currentIdentity = SqlContext.WindowsIdentity;
                impersonatedContext = currentIdentity.Impersonate();
                if (computerName.Length != 0)
                {
                    ManagementObjectSearcher cpuSearcher = new ManagementObjectSearcher(
                        "\\\\" + computerName + "//root//CIMV2", "SELECT * FROM Win32_Processor");
                    foreach (ManagementObject wmiObj in cpuSearcher.Get())
                    {
                        cpuType = wmiObj.GetPropertyValue("AddressWidth").ToString();
                    }

                    ManagementObjectSearcher searcher = new ManagementObjectSearcher(
                        "\\\\" + computerName + "//root//CIMV2", "SELECT * FROM Win32_OperatingSystem");
                    foreach (ManagementObject wmiObj in searcher.Get())
                    {
                        systemDirectory = wmiObj.GetPropertyValue("SystemDirectory").ToString();
                        operationType = wmiObj.GetPropertyValue("Caption").ToString();
                    }

                    exitStatus = 1;
                }
                else
                {
                    exitStatus = 0;
                }

                if (impersonatedContext != null)
                {
                    impersonatedContext.Undo();
                }
            }
            catch (Exception e)
            {
                if (impersonatedContext != null)
                {
                    impersonatedContext.Undo();
                }

                exitStatus = -1;
                errorMessage = e.Message + "," + e.StackTrace;
            }
        }

        /// <summary>
        /// Copy or delete AdtAgent.exe file.
        /// </summary>
        /// <param name="agentPath">The OS directory of AdtAgent.exe</param>
        /// <param name="targetWorkstationPath">The directory of target workstation where the adtagent should nest</param>
        /// <param name="fileName">Need operation file name</param>
        /// <param name="operation">1:Copy 0:Delete</param>
        /// <param name="exitStatus">0:Failed 1: Success</param>
        /// <param name="errorMessage">Return exception info</param>
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void OperationFile(string agentPath,string targetWorkstationPath,string fileName,int operation,out SqlInt32 exitStatus,out SqlString errorMessage)
        {
            errorMessage = string.Empty;
            WindowsImpersonationContext impersonatedContext = null;
            try
            {
                WindowsIdentity currentIdentity = SqlContext.WindowsIdentity;
                impersonatedContext = currentIdentity.Impersonate();
                if (!string.IsNullOrEmpty(fileName.Trim()))
                {
                    if (operation == 1)
                    {
                        if (!File.Exists(targetWorkstationPath + fileName))
                        {
                            File.Copy(agentPath + fileName, targetWorkstationPath + fileName, true);
                        }
                    }
                    else if (operation == 0)
                    {
                        if (File.Exists(targetWorkstationPath + fileName))
                        {
                            File.Delete(targetWorkstationPath + fileName);
                        }
                    }

                    exitStatus = 1;
                }
                else
                {
                    exitStatus = 0;
                }
               
                if (impersonatedContext != null)
                {
                    impersonatedContext.Undo();
                }
            }
            catch (Exception e)
            {
                if (impersonatedContext != null)
                {
                    impersonatedContext.Undo();
                }

                exitStatus = -1;
                errorMessage = e.Message + ", " + e.StackTrace;
            }
        }