Results 1 to 10 of 10
  1. #1
    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.

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

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,558
    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.

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

  5. #5
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,914
    Yes, make the DTS task a job and start that job using sp_startjob.

    -PatP

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

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

  9. #9
    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!

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,914
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •