call to xp_cmdshell from trigger problem

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.
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.
>
>
>
>
>
|||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[vbcol=seagreen]
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
assistance[vbcol=seagreen]
problem[vbcol=seagreen]
for[vbcol=seagreen]
created to[vbcol=seagreen]
were[vbcol=seagreen]
seemed[vbcol=seagreen]
issue[vbcol=seagreen]
when I[vbcol=seagreen]
it[vbcol=seagreen]
builds[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
from[vbcol=seagreen]
instructed[vbcol=seagreen]
could[vbcol=seagreen]
|||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[vbcol=seagreen]
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
assistance[vbcol=seagreen]
problem[vbcol=seagreen]
for[vbcol=seagreen]
created to[vbcol=seagreen]
were[vbcol=seagreen]
seemed[vbcol=seagreen]
issue[vbcol=seagreen]
when I[vbcol=seagreen]
it[vbcol=seagreen]
builds[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
from[vbcol=seagreen]
instructed[vbcol=seagreen]
could[vbcol=seagreen]
|||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...
> Authentication
> assistance
> problem
> for
> created to
> were
> seemed
> issue
> when I
> it
> builds
> in
> to
> the
> when
> from
> instructed
> could
>
>
|||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[vbcol=seagreen]
> 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:
account.[vbcol=seagreen]
application[vbcol=seagreen]
the[vbcol=seagreen]
analyzer.[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
use[vbcol=seagreen]
system,[vbcol=seagreen]
area[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
This[vbcol=seagreen]
new[vbcol=seagreen]
the[vbcol=seagreen]
works,[vbcol=seagreen]
portion)[vbcol=seagreen]
It[vbcol=seagreen]
use[vbcol=seagreen]
file[vbcol=seagreen]
(Code[vbcol=seagreen]
altered[vbcol=seagreen]
indicates[vbcol=seagreen]
that[vbcol=seagreen]
SQLProxy[vbcol=seagreen]
the[vbcol=seagreen]
called[vbcol=seagreen]
it[vbcol=seagreen]
|||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/de...v bscript.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...
> fixed
> which
> account.
> application
> the
> analyzer.
> the
> the
> use
> system,
> area
> and
> the
> This
> new
> the
> works,
> portion)
> It
> use
> file
> (Code
> altered
> indicates
> that
> SQLProxy
> the
> called
> it
>
>
|||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/de...v bscript.asp[vbcol=seagreen]
>
> Sasan
> "Daniel Gard" wrote:
very[vbcol=seagreen]
process[vbcol=seagreen]
same[vbcol=seagreen]
account?[vbcol=seagreen]
certificate.[vbcol=seagreen]
process[vbcol=seagreen]
run[vbcol=seagreen]
XML[vbcol=seagreen]
vbs[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
proxy[vbcol=seagreen]
proxy[vbcol=seagreen]
was[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
SQL[vbcol=seagreen]
message[vbcol=seagreen]
have a[vbcol=seagreen]
a[vbcol=seagreen]
updates[vbcol=seagreen]
notify[vbcol=seagreen]
been in[vbcol=seagreen]
was[vbcol=seagreen]
the[vbcol=seagreen]
tables[vbcol=seagreen]
this[vbcol=seagreen]
changed[vbcol=seagreen]
a[vbcol=seagreen]
to[vbcol=seagreen]
information.[vbcol=seagreen]
currently[vbcol=seagreen]
seems[vbcol=seagreen]
when[vbcol=seagreen]
know[vbcol=seagreen]
the[vbcol=seagreen]
We[vbcol=seagreen]
should.[vbcol=seagreen]