March 24, 2009 at 3:20 pm
OK ... here are the OS and SQL server data points:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
XP_CmdShell HAS been enabled via a facet and also progremmatically via TSQL just to hammer it home.
Executing the following code in a stored procedure that is called by a SQL job:
'EXEC Master..XP_CMDSHELL ''fsutil volume diskfree ' + Char(@Drive) + ':'''
Error FIRST returned in job step is as follows:
Executed as user: MyDomain\MyAcct. The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229)
I then tried granting the NT domain login execute right on xp_cmdshell in the Master DB.
After doing this I recieved the error of:
Executed as user: MyDomain\MyAcct. The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. [SQLSTATE 42000] (Error 15153)
The account being used for ALL of our SQL services is an NT Domain Admin account that has been granted SysAdmin rights on the server.
It is also the same account that we use to run all of our SQL jobs.
This job runs fine on all 50+ of our SQL 05 servers ... just not working on the 08 flavor.
If any more details are needed, please let me know.
March 24, 2009 at 11:15 pm
You have to use a proxy account when executing the xp_cmdshell extended stored procedure. Please look at http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htm.
Thanks :).
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 7:06 am
I do not beleive you have to use the proxy account if the login that is executing the xp_cmdshell is part of the sysadmin group ... which it is.
I will double check on that comment and post my findings to back it up.
I will also test setting up a proxy to see if that is a possible workaround but I do not like to leverage proxies if I have to.
Thanks for the help thus far!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply