September 19, 2008 at 3:10 pm
Hi there.
I have a problem on one of my Dev servers that is puzzling our entire team.
We have xp_cmdshell set up with a liberal proxy account (not as liberal as local admin) to use for non-sa users. IF the SQL Agent Service is turned on, xp_cmdshell does nothing. It doesn't throw any errors, it just does nothing and returns NULL under Output regardless of what we do.
IF we turn the SQL Agent off, then xp_cmdshell works just fine for our non-sa users.
The SQL Service is running under the same local admin account as the SQL Agent.
Does anyone have any idea what is going on?
Thanks,
September 25, 2008 at 12:16 am
Further work on this problem has shown that on this particular SQL 2005 server, if the agent is not running under the same account as the SQL Service account, then the xp_cmdshell setup with proxy works just fine. However, it's only when the Agent Service is running under the same account as the SQL Service that xp_cmdshell essentially does nothing other then return a column of "output" which is "NULL" with the O/S command not executed.
Preliminary tests show that this is not the case if we do the same configuration to one of our test servers.
Has anyone else ever encountered this before?
The next thing for our team to determine is if it's a poor practice to have the SQL Agent Service to run under the same account as the SQL Service.
May 8, 2009 at 12:37 am
i am facing a problem which is not allowing me to run my Windows app. using Xp_cmdShell and using that i want to run it based upon a trigger ,
here what my question is if i run the Win App manually it is running just fine but when i am running it using SQL Server it is showing me output as NULL
Any help??
May 3, 2010 at 8:34 am
Has anyone gotten anywhere with this? I'm having this problem, too. One thing to mention is that the NULL returns right away... Like it's unable to start a Windows process somehow. Nothing gets written to the system, security or application logs It's really inconsistent, and restarts seem to make it go away for awhile.
In the meantime, I'm going to put a SQL Agent job in place that "watches" for when
exec master..xp_cmdshell 'dir \\servername\Sharename\' returns NULL (which is where we're seeing this problem).
This is happening on SQL Server 2005 x86 Standard Edition, running on 32-bit Windows 2003 Enterprise Edition.
May 3, 2010 at 9:22 am
I was never able to track down the exact issue. Rather, in time I rebuilt the server and it worked fine. My theory related to the issue is that there are working directories that SQL Server uses that have permissions revoked to the account that's running the service. Running the service under Domain Admin doesn't help if the permissions have been revoked even if the account has the ability to grant itself the permissions back as it doesn't change the fact that those permissions aren't currently in place.
In my case, xp_cmdshell also returned NULL instantly which indicates to me as well that it simply did nothing, however if I ran the service using the local system account, at that point it worked. This, however, wasn't a "fix all" because you can't go beyond the local server when using that account.
May 3, 2010 at 9:29 am
Yeah, this server definitely needs permissions elsewhere on the domain, so using a local account doesn't really get to the bottom of it.
Well, we're migrating it to a Windows 2008 box soon. Hopefully that will take care of it. If anyone else out there has seen this issue and could make any sense of it, please let me know.
May 3, 2010 at 9:48 am
I would suggest, however, to try using the local system account to see if that "fixes" it. Then we know we had the same issue.
I feel confident that the issue boils down to Windows permissions which means it's just a matter of finding out which directories were affected.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply