Re: call to xp_cmdshell from trigger problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Sasan Saidi (SasanSaidi_at_discussions.microsoft.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 12:03:01 -0800

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

Relevant Pages

  • Re: Informix beats Oracle
    ... Daniel seems to want to change the issue. ... I'm a consultant and when I pick or choose a client, I'm forced to work within their limited constraints. ... >> My experience is with Oracle 9. ... necessarily privileges over the tables, a third user cannot execute the ...
    (comp.databases.informix)
  • Re: Moving files to another computer
    ... Daniel Cohen wrote: ... on the friend's Mac will be owned by the user who did the moving. ... make sure you are logged in to your account when you do the moving. ...
    (comp.sys.mac.system)
  • Re: Kein Zugriff auf Lizenzserver
    ... Habe die Registry entsprechend angepasst. ... Anschliessend habe ich mich am TS - von diesem Client ... Gruss Daniel ...
    (microsoft.public.de.german.windows.terminaldienste)
  • Re: Holub on getters/setters again
    ... >> accessors are backed directly by instance variables, ... >> client needs them, that's bad. ... Daniel Parker ...
    (comp.object)
  • Re: Domain name field empty
    ... A server is not a workstation, and normal user accounts by default ... I'd hope you couldn't log in as "any account" - that's not a good ... This is the way domain controllers work. ... Thanks Daniel ...
    (microsoft.public.windows.server.general)