RE: call to xp_cmdshell from trigger problem

From: Sasan Saidi (SasanSaidi_at_discussions.microsoft.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 09:27:12 -0800

Hi Daniel,

I am just wondering under what user you did your test on query analyzer.

Have you tried the following to see what kind of result you get:
1. log in on the server by using your SQLProxy
2. Open query analyzer and connect to the server with Windows Authentication
3. try a test and see if the client gets the required info.

Sasan Saidi
Senior DBA

"Daniel Gard" wrote:

> To any SQL Server MVP:
>
> We have a problem that out group can not seem to resolve. We have a
> database that is used to alert and calculate required amounts of assistance
> to clients that monitor through a private frame relay WAN. When a problem
> occurs client 1 enters the request at a workstation which updates the
> database and causes a trigger to fire and starts a process to notify the
> other clients that assistance is required. This portion has been in use for
> several years but now need to send information to a control area system,
> rather than single entity, that is in XML format so a program was created to
> take the data and format in accordance to the XML Schema rules the area
> used.
>
> At first it failed because the trigger called the EXE but the tables were
> locked by the first program called in the trigger. To resolve this and
> allow the trigger to continue thus unlocking the tables we changed the
> trigger to call a vbs script file rather than the EXE directly. This seemed
> to resolve the situation with locked tables in the database but a new issue
> has come up I can not figure out.
>
> The EXE has to use a X509 digital certificate to pass the data to the
> client's server. When I run the EXE from the command line it works, when I
> use SQL Query Manager and run the trigger code (less the alert portion) it
> works but when the trigger fires is fails to send the information. It builds
> everything but the client never gets the XML stream? We currently use
> simple text files to track where the program process is but this file
> indicates it finishes properly and all cleanup of objects occurs (Code in
> VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
> indicate within the text file who started the program and this indicates the
> "SQLProxy" account is who is running the EXE file. It almost seems that when
> impersonate process occurs the local server does not treat the SQLProxy
> account as the right one?
>
> How can we test using SQL Query Manager the exact process as when the
> trigger is fired from SQL Server?
>
>
> Information:
>
> ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called from
> the trigger by the application user. This is what we created as instructed
> by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> works because the first step in the trigger works.
>
> CERTIFICATE: We logged on to the server using SQLProxy so the
> certificate could be installed into the "My" store for the user. We could
> move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
>
>
>
>
>
>
>
>
>

Relevant Pages

  • RE: slow connections with jdbc driver and no entry in hosts file
    ... Query Analyzer returns instantly and works fine. ... > | for the server in our local hosts file. ... > Query Analyzer from the same client workstation? ... can you successfully resolve the host name ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: xp_cmdshell error
    ... Consider the client where you are running query analyzer from has, ... Novell client installed on it,(and hence the directory listing works fine ... from the command prompt) the server where SQL is running may not have the ...
    (microsoft.public.sqlserver.security)
  • Re: How could I directly trigger a very simple on localhost and a known port listening server from m
    ... > little server would be running on the client machine, where my browser ... > side would be to trigger a scanner, ...
    (comp.lang.javascript)
  • Reidrect stderr of child process
    ... I have inherited the maintenance of a client app and the associated server. ... Simply, the server waits for the client to trigger it; ... Save current STDERR, to be restored later. ...
    (microsoft.public.win32.programmer.networks)
  • Re: Displaying Error Messages From Triggers
    ... I find RAISERROR messages from SQL Server are consistently reported in client ADPs in both Access 2002 and Access 2003 but ONLY if ... In my post below I described a problem I have been experiencing since the client upgraded to Access 2003. ... If I can't get the trigger to raise errors, what is the best work around? ... I would like to leave the business logic on the server ...
    (microsoft.public.access.adp.sqlserver)
Loading