If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > xp_cmdshell from IIS: proxy not working

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 163
Red face xp_cmdshell from IIS: proxy not working

I have a procedure which prepares a csv file on demand using xp_cmdshell to invoke bcp.

It works fine in sql server. In fact, I have setup a proxy account to run as the domain administrator so it should even work for limited sql server accounts.

When IIS 6.0 attempts to run the procedure, however, I get "xp_cmdshell failed to execute because current security context is not sysadmin and proxy acount is not setup correctly."

For some reason, IIS 6.0 is not able to assume proxy privileges.

Recently, the machine hosting IIS was promoted to a domain controller. Is this causing a problem? My suspicion is that the proxy account has to be a LOCAL user, and since DC's do not have local users, the proxy privileges are useless.

Anybody got any thoughts?

Thanks.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Location: Long Island
Posts: 696
Perhaps related to account that starts the SQL Agent. I've seen similar problems occur with DTS and if the Agent is started with localsystem, not an Account that has permissions to where you are creating the .csv file.
Reply With Quote
  #3 (permalink)  
Old
Annie's Dog Walker
 
Join Date: Nov 2004
Location: on the wrong server
Posts: 8,411
setting up the proxy user is a big security no no an my guess here is that IIS needs to use the account set up as a proxy but giving the I_USR account effectively sysadmin access gives me the willies.

once upon a time before dotnet i believe i resolved this issue by writing a COM object that was called by my ASP page and compiled within the com object was a connection string to the database that had sysadmin rights to do what i needed sysadmin rights to do.
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 163
I would rather not give these rights to the I_USR account - but I have to provide ad hoc reports both to screen and to file. ASP.NET was originally producing the csv file, but it ran all the webserver's processors up to 98% usage (and still spun for minutes for larger datasets) as well as gobbling virtual memory and throwing out of memory errors. I thought a good solution would be to offload file-creation to the db-server - but now it's looking like this is a no-go.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 163
Hi PMAschmed - this isn't a job; it's a procedure invoked directly from ASP.NET, so agent shouldn't be involved at all.

I can't think of any alternative for this. Even DTS would require cmdshell to compile a file on the fly.

Is there a way to execute a job from tsql without using cmdshell?
Reply With Quote
  #6 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,422
Yes, make the DTS task a job and start that job using sp_startjob.

-PatP
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 163
Good suggestion, Pat.

Unfortunately, the user must be sysadmin to run sp_startjob - so ASP.NET is not even allowed to call that.

Seems like they've really gone out of their way to make real-time file creation impossible from a web request.

Oh well...

Thanks for your help.
Reply With Quote
  #8 (permalink)  
Old
9th inning DBA
 
Join Date: Jan 2004
Location: In a large office with bad lighting
Posts: 1,039
No big deal ... let the user write to a table to request the job to run. Have a scheduled job check periodically for the request (would 1 minute be real-time enough?). The scheduled job can do a xp_cmdshell to bcp the data out to the csv.

However, what removes the csv if the user requests the job a second time in the same day ... are you putting a datetime in the file name?
__________________

-- This is all just a Figment of my Imagination --
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 163
Exactly - I'm including datetime as a string including ms. Then I have a nightly job that clears all accrued reports (some of these monsters are 30+ MB of csv data).

The file-write workaround you propose is interesting. The page should instantly come back with a report link - but I think there are some interesting possibilities here - perhaps with a scheduled task on the server that runs every 10 seconds. Hmm. I'll need to mull this over.

Thanks for the tip!
Reply With Quote
  #10 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,422
Quote:
Originally Posted by onansalad
Unfortunately, the user must be sysadmin to run sp_startjob - so ASP.NET is not even allowed to call that.
I don't think that's the case, and neither do the folks in this thread, but maybe we're all wet.

-PatP
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On