Solved

How to send control commands to a remote Win32 services from within a stored procedure

Posted on 2008-10-30
4
310 Views
Last Modified: 2013-12-04
Hi experts,

in one of our web-based software products, we launch long-running background processes from within T-SQL code in stored procedures. We do this by calling an Extended Stored Procedure 'master..xp_svcctl' written in C (source partly shown below) which sends a service control command to a Win32 Service written in C# residing on another machine in the same domain. The service then connects to SQL Server, obtains job details from the database and starts to work (e.g. converting documents to PDF, or something like that). So the service control command is needed only to signal that there is a new "job in the queue", we do not need to pass any parameters, only the signal, and it works like a charm since many years in critical production environments.

Now we are about to setup this software on a load-balanced, virtualized cluster environment and the customer does not allow to install any extended stored procedures and we look for a workaround. Also using a managed code assembly is not an option.

Do you have a solution which would allow to keep the Win32 Service as the recipient of the service control command? Is there a best practice to send service control commands other than Start/Stop/Pause from SQL Server to remote machines, maybe via Notification Services?
1. What the code in the extended stored proc does:
 
[...]
ServiceController sc = new ServiceController("cdo01BkgProc");
sc.ExecuteCommand(129); /* >128=range of user-defined service control codes */
[...]
 
 
2. T-SQL to consume and diagnose the service:
set nocount on
 
declare
  @servicetype int,
  @curstate int,
  @controlsaccepted int,
  @exitcode int,
  @specificexitcode int,
  @checkpoint int,
  @waithint int,
  @ret int
 
declare @@machine nvarchar(255)
select @@machine=N'remote_server_name'
 
exec master..xp_svcctl
  @machine=@@machine,
  @service=N'cdo01BkgProc', /* service short name */
  @control=129, /* service signal number */
  @servicetype=@servicetype OUTPUT,
  @curstate=@curstate OUTPUT,
  @controlsaccepted=@controlsaccepted OUTPUT,
  @exitcode=@exitcode OUTPUT,
  @specificexitcode=@specificexitcode OUTPUT,
  @checkpoint=@checkpoint OUTPUT,
  @waithint=@waithint OUTPUT,
  @result=@ret OUTPUT
 
declare 
  @sservicetype nvarchar(255),
  @scurstate nvarchar(255),
  @scontrolsaccepted nvarchar(2048)
 
select @sservicetype=case @servicetype
  when 0x2 then N'SERVICE_FILE_SYSTEM_DRIVER'
  when 0x1 then N'SERVICE_KERNEL_DRIVER'
  when 0x10 then N'SERVICE_WIN32_OWN_PROCESS'
  when 0x20 then N'SERVICE_WIN32_SHARE_PROCESS'
  when 0x100 then N'SERVICE_INTERACTIVE_PROCESS'
  else N'SERVICE_UNKNOWN'
end
 
select @scurstate=case @curstate
  when 0x5 then N'SERVICE_CONTINUE_PENDING'
  when 0x6 then N'SERVICE_PAUSE_PENDING'
  when 0x7 then N'SERVICE_PAUSED'
  when 0x4 then N'SERVICE_RUNNING'
  when 0x2 then N'SERVICE_START_PENDING'
  when 0x3 then N'SERVICE_STOP_PENDING'
  when 0x1 then N'SERVICE_STOPPED'
  else N'SERVICE_UNKNOWN'
end
 
select @scontrolsaccepted=N''
if @controlsaccepted & 0x10=0x10 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_NETBINDCHANGE' end
if @controlsaccepted & 0x8=0x8 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_PARAMCHANGE' end
if @controlsaccepted & 0x2=0x2 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_PAUSE_CONTINUE' end
if @controlsaccepted & 0x100=0x100 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_PRESHUTDOWN' end
if @controlsaccepted & 0x4=0x4 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_SHUTDOWN' end
if @controlsaccepted & 0x1=0x1 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_STOP' end
if @controlsaccepted & 0x20=0x20 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_HARDWAREPROFILECHANGE' end
if @controlsaccepted & 0x40=0x40 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_POWEREVENT' end
if @controlsaccepted & 0x80=0x80 begin select @scontrolsaccepted=@scontrolsaccepted+N' '+N'SERVICE_ACCEPT_SESSIONCHANGE' end
select @scontrolsaccepted=LTrim(@scontrolsaccepted)
 
select
  @ret as ret,
  @servicetype as servicetype,
  @sservicetype as sservicetype,
  @curstate as curstate,
  @scurstate as scurstate,
  @controlsaccepted as controlsaccepted,
  @scontrolsaccepted as scontrolsaccepted,
  @exitcode as exitcode,
  @specificexitcode as specificexitcode,
  @checkpoint as [checkpoint],
  @waithint as waithint

Open in new window

0
Comment
Question by:dnebel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 86

Expert Comment

by:jkr
ID: 22842771
One thing is that the remote server name in your above example does not follow MS' naming conventions. These are

<domain>\<machine_name>

e.g.

MYDOMAIN\server1

if you are on a real domain or

.\server1

if it is a workgroup.
0
 

Author Comment

by:dnebel
ID: 22843025
jkr,

thanks for your response. You are right, but this "remote_server_name" I wrote was only a placeholder in my code sample meaning that the actual server name should go there. At runtime, it will be something like "LUNATICUS\SERVER03".

The problem is not that the code would not work as it is - it does;
the problem is that the customer refuses to allow use of extended stored procedures and
we must find a workaround.
SQLServerAgent in connection with xp_cmdshell is also not an option because of security considerations.

Additional Info:

Put more simple, I need a way to signal a remote service process (.NET 1.1 or higher on Windows Server, may run as NETWORK SERVICE or as a local user or as a domain user whatever is required)
and my starting point is a plain T-SQL query, ideally code in a stored procedure.
0
 

Accepted Solution

by:
dnebel earned 0 total points
ID: 23013547
We've been able to solve the matter by circumvention. The piece of code was ported to the application's source language and integrated into it. Now the signal is sent from the application server, no longer from the database server. That does not address the original problem albeit it makes sense (this is an application task, not a database task)
Option Explicit
 
Private Type SERVICE_STATUS
    dwServiceType As Long
    dwCurrentState As Long
    dwControlsAccepted As Long
    dwWin32ExitCode As Long
    dwServiceSpecificExitCode As Long
    dwCheckPoint As Long
    dwWaitHint As Long
End Type
 
Private Declare Function OpenSCManager Lib "advapi32.dll" Alias "OpenSCManagerA" (ByVal lpMachineName As String, ByVal lpDatabaseName As String, ByVal dwDesiredAccess As Long) As Long
Private Declare Function CloseServiceHandle Lib "advapi32.dll" (ByVal hSCObject As Long) As Long
Private Declare Function OpenService Lib "advapi32.dll" Alias "OpenServiceA" (ByVal hSCManager As Long, ByVal lpServiceName As String, ByVal dwDesiredAccess As Long) As Long
Private Declare Function StartService Lib "advapi32.dll" Alias "StartServiceA" (ByVal hService As Long, ByVal dwNumServiceArgs As Long, ByVal lpServiceArgVectors As Long) As Long
Private Declare Function ControlService Lib "advapi32.dll" (ByVal hService As Long, ByVal dwControl As Long, lpServiceStatus As SERVICE_STATUS) As Long
 
Private Const GENERIC_EXECUTE As Long = &H20000000
Private Const SERVICE_CONTROL_STOP As Long = 1
Private Const SERVICE_CONTROL_PAUSE As Long = 2
Private Const SERVICE_CONTROL_CONTINUE As Long = 3
Private Const SERVICE_CONTROL_BKGPROCDEQUE As Long = 129 'ServiceCommands.setsignal_dequepoll = 129
 
Public Function ServiceCommand(ByVal MachineName As String, ByVal ServiceName As String, ByVal iCommand As Long, ByRef sErr As String) As Boolean
    Dim hSCM As Long
    Dim hService As Long
    Dim Res As Long
    Dim lpServiceStatus As SERVICE_STATUS
    'open a connection to the Service Control Manager, exit if error
    hSCM = OpenSCManager(MachineName, vbNullString, GENERIC_EXECUTE)
    If hSCM = 0 Then
        sErr = "Failed to open the service control manager in an attempt to send control command " & CStr(iCommand) & " to the """ & ServiceName & """ service with code " & CStr(Err.LastDllError)
        Exit Function
    End If
    'open the specified service, exit on error
    hService = OpenService(hSCM, ServiceName, GENERIC_EXECUTE)
    If hService = 0 Then
        sErr = "Failed to open the """ & ServiceName & """ service with code " & CStr(Err.LastDllError)
        GoTo Cleanup
    End If
    'send the service command
    Res = ControlService(hService, iCommand, lpServiceStatus)
    If Res = 0 Then
        'failed
        sErr = "Failed to send the control command " & CStr(iCommand) & " to the """ & ServiceName & """ service with code " & CStr(Err.LastDllError)
        GoSub Cleanup
        Exit Function
    End If
    'succeeded
    GoSub Cleanup
    ServiceCommand = True
    Exit Function
Cleanup:
    'free handles
    If hService Then CloseServiceHandle hService
    CloseServiceHandle hSCM
    Return
End Function

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question