Re: call to xp_cmdshell from trigger problem
From: Daniel Gard (dgard_at_neo.rr.com)
Date: 02/21/05
- Next message: Steve Kass: "Re: curious problem in production"
- Previous message: Ollie Riches: "Re: curious problem in production"
- In reply to: Sasan Saidi: "Re: call to xp_cmdshell from trigger problem"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Feb 2005 15:31:22 -0500
Sasan,
I'll look this over maybe there is something we missed in the vbs script
being called by the trigger.
Thank You,
Dan
"Sasan Saidi" <SasanSaidi@discussions.microsoft.com> wrote in message
news:BF67C18C-AEEC-408D-9185-86D4B5DC0772@microsoft.com...
> Daniel, I am not sure if this can help in any ways but you might want to
have
> a quick look at it:
>
> Article: Setting the Default Process Security Level Using VBScript
> Link:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/setting_the_default_process_security_level_using_vbscript.asp
>
>
> Sasan
>
> "Daniel Gard" wrote:
>
> > 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: Steve Kass: "Re: curious problem in production"
- Previous message: Ollie Riches: "Re: curious problem in production"
- In reply to: Sasan Saidi: "Re: call to xp_cmdshell from trigger problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
