call to xp_cmdshell from trigger problem
From: Daniel Gard (dgard_at_neo.rr.com)
Date: 02/21/05
- Next message: Sachin: "IDENTITY Insert doesnt work for Linked servers"
- Previous message: Sue Hoegemeier: "Re: SQL Maintenance Optimization"
- Next in thread: Sasan Saidi: "RE: call to xp_cmdshell from trigger problem"
- Reply: Sasan Saidi: "RE: call to xp_cmdshell from trigger problem"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Feb 2005 10:42:35 -0500
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.
- Next message: Sachin: "IDENTITY Insert doesnt work for Linked servers"
- Previous message: Sue Hoegemeier: "Re: SQL Maintenance Optimization"
- Next in thread: Sasan Saidi: "RE: call to xp_cmdshell from trigger problem"
- Reply: Sasan Saidi: "RE: call to xp_cmdshell from trigger problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
