Re: call to xp_cmdshell from trigger problem
From: Daniel Gard (dgard_at_neo.rr.com)
Date: 02/21/05
- Next message: Daniel Gard: "Re: call to xp_cmdshell from trigger problem"
- Previous message: Adam Machanic: "Re: drop / create new database with old name"
- In reply to: Sasan Saidi: "RE: call to xp_cmdshell from trigger problem"
- Next in thread: Daniel Gard: "Re: call to xp_cmdshell from trigger problem"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Feb 2005 12:58:59 -0500
Sasan
We have tried all of the following:
1. Logged on to the Windows server as the Domain Administrator and
connecting to SQL Server using windows authentication.
2. Logged on to the Windows server as the SQLProxy Account and connecting
to SQL Server using windows authentication.
We did notice is logged on to the Windows server as either 1 or 2 and
logging onto the SQL Server as the application user it does not work.
Thanks
Dan
"Sasan Saidi" <SasanSaidi@discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@microsoft.com...
> 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.
> >
> >
> >
> >
> >
> >
> >
> >
> >
- Next message: Daniel Gard: "Re: call to xp_cmdshell from trigger problem"
- Previous message: Adam Machanic: "Re: drop / create new database with old name"
- In reply to: Sasan Saidi: "RE: call to xp_cmdshell from trigger problem"
- Next in thread: Daniel Gard: "Re: call to xp_cmdshell from trigger problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
