Solved

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

Posted on 2008-10-30
4
290 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
  • 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard 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.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now