Re: call to xp_cmdshell from trigger problem

From: Daniel Gard (dgard_at_neo.rr.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 13:14:41 -0500

Sasan

The application user is a SQL Server account and not a Windows user account.
We noticed the following behavior. If the application user account was
placed into the SQL System Administrator group the program runsa as the
Domain account for the SQL Server and if this user is not a in the
administrator group then the program runs as SQLProxy.

The more I mess with this the more I think the user running the application
after the call from SQL Server can not find the digital certificate in the
CURRENT_USER\My store. How does the impersonation really work with SQL
Server?

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.
> >
> >
> >
> >
> >
> >
> >
> >
> >

Relevant Pages

  • Re: Stored procedure/trigger and scripts
    ... must have been the trigger that locked up the table. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... I just ran some additional tests and it looks like my trigger is fine. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: sql server events on data create
    ... If I were to do this, I would set up the client to listen on a socket, write ... an extended stored procedure that can trigger this socket, ... triggers on the tables of interest to call this extended stored procedure, ... Or you could start a custom SQL Server trace event with the right filters? ...
    (microsoft.public.sqlserver.programming)
  • SOLUTION: Problem connecting to sql server
    ... it is an account problem. ... Not associated with a trusted SQL Server ... go to Client Configuation properties on the client machine ... >> settings on the SQL server, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SMS Client issue
    ... The account with the error needs to be an administrator account on your ... communication and one to the SQL Server that account must exist in there. ... boundaries are correct to assign and install. ... Push installation to client machines does ...
    (microsoft.public.sms.misc)