Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parody
Posting Yak Master
111 Posts |
 Posted - 2010-01-29 : 09:11:58
|
| Hi Got a strange one here. I am executing a command line PGP application using xp_cmdshell from SQL2008. and receiving access denied error from the command line. This is being execute as an SA so should use the SQL Service credential which is a domanin account, and to elimanate any obvious permissions problems has been made an admin on the box. The was working fine on our old SQL2000 server. So far I have tried: Executing as me (sa) on my local machine Executing as a local SQL account (sa) on local machine Both of the above on the server Executing as the SQL Service account on the server Trying a simple file copy on another drive Set up a share with full permissions and used that set up the xp_CMDShell proxy account (even though it shouldnt be needed) Strangely though printing the command and copying it to the command line works fine, when logged in and both me and the sql service account. Also trying a file copy on a remote server works fine. So the issue is limited specifically to xp_CMDShell attempting operations on the local server. Its driving me nuts... must be something soooo obvious. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2010-01-29 : 10:23:35
|
| You'll need to enable it. It's off by default in 2005 and higher.-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGOMike"oh, that monkey is going to pay" |
 |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-01-29 : 10:27:47
|
| Thanks for the suggestion, however it is already enabled. As I said, trying a simple file copy on a remote server works fine. The error is from the cmd not a permissions or config error from sql.Thanks anyway. |
|
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-02-01 : 04:01:56
|
| nobody? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 07:36:35
|
| The problems I have had, similar to this, have all been file/folder-level permissions and determining exactly which UserID the process is running as. |
|
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-02-01 : 08:18:03
|
| same here - thats why I decided to give everything god rights on the server and in SQL to eliminate anything. I also tried full permissions directly on the folders. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-01 : 11:30:11
|
| First, it doesn't matter what login executes the xp_cmdshell as it will ALWAYS execute under the credentials of the service account.Now, make certain the SQL Service account has permission on the share. Make sure the share was not set up as read-only.finally, you said xp_cmdshell is enabled, but said you know it is because a filecopy works. did you use xp_cmdshell to perform the file copy? If not, follow mfemenel's advice.What OS command(s) are you trying to execute with xp_cmdshell? |
|
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-02-02 : 07:29:57
|
| In SQL2008 the proxy account is used when a non-sa user executes cmdshell.It is enabled in sp_configure. It is running a PGP exe for encrypting files we email to 3rd parties.As it happens, we sorted it yesterday afternoon. Basically went through removing and re-apply all the directory permissions and got it working, then removed one by one and have narrowed it down to a share with full permissions to the service account (which we had already tried, or so we thought). Strangely though it still doesnt work without that, even if the account is an admin of the server. |
|
|
|
|
|
|
|
|
xp_cmdshell access denied