Re: call to xp_cmdshell from trigger problem
From: Daniel Gard (dgard_at_neo.rr.com)
Date: 02/21/05
- Next message: Weston Weems: "Limit server resources per query"
- Previous message: Mary Chipman [MSFT]: "Re: MSAcces as a SQL front-end"
- In reply to: Sasan Saidi: "Re: call to xp_cmdshell from trigger problem"
- 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 14:06:10 -0500
Sasan
I understand that and we have all of this working. The problem seems very
strange even in my book because I am not sure if the impersonation process
uses a modified SID from the one if you log on the the server using the same
account. Does it take the domain user and create a temporary local account?
If so then that is the problem and this user can not find the certificate.
We are going to try to use a public certificate store and see if the process
will complete.
The program we wrote runs no matter what method the difference is when run
from the command line or from SQL Query manager it finishes by sending XML
to the the client but if the same process runs from being called by the vbs
script that is called by the trigger the client get nothing.
Thanks
"Sasan Saidi" <SasanSaidi@discussions.microsoft.com> wrote in message
news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@microsoft.com...
> When xp_cmdshell is invoked by a user who is a member of the sysadmin
fixed
> server role, xp_cmdshell will be executed under the security context in
which
> the SQL Server service is running. When the user is not a member of the
> sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail.
>
> Sasan
>
> "Daniel Gard" wrote:
>
> > 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.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> >
> >
- Next message: Weston Weems: "Limit server resources per query"
- Previous message: Mary Chipman [MSFT]: "Re: MSAcces as a SQL front-end"
- In reply to: Sasan Saidi: "Re: call to xp_cmdshell from trigger problem"
- 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
|
