May 29, 2007 at 9:11 am
I have a trigger that fires xp_cmdshell xcopy to move files from a path found in a sql server 2000 table.
If the trigger is fired from within Query Analyzer, it works. If I(having sysadmin privilege) use the application and the trigger fires, it works. If a person without sysadmin permission runs it errors when running from within our application that uses an odbc link.
I have tried following the directions in http://support.microsoft.com/kb/890775, but users still get
Connectivity error: [Microsoft ODBC SQL Server Driver][SQL Server] xp_cmdshell failed to execute because current security context is not sysadmin and proxy account is not set up correctly.
If I make the users sysadmin, the application works, which I cant do on the live system. I have set up the proxy to use the same windows login that the MS SQL Server service uses. I also tried setting up the proxy to use my login and pw.
I am pretty sure I have something wrong with the proxy setup, but I dont know how to trouble shoot it. I know that login/pw/domain didnt give any error, and that if i type in the wrong pw it told me the pw wasnt valid.
The users all belong to a windows group and the group has execute permission on master.dbo.xp_cmdshell.
I appreciate any ideas. Maybe I have focused on the proxy thing and need to step back. I just dont know anymore, I keep going around and around without success.
-Tracey
May 29, 2007 at 9:24 am
Are you sure that your SQL Server service account has the following local policy settings set in Active Dir? See KB article... also, likely you'll need to reboot after making these changes. Make sure the local policy settings are not being "overwritten" by global policy settings.
http://support.microsoft.com/kb/248391
A.J.
DBA with an attitude
May 30, 2007 at 9:54 am
The following articles could be helpful as well - 264155, 248407 and 248391. I just had similar issues on one of my dev servers (Win2000, SQL7) when non-sa users tried to execute xp_cmdshell. There was a problem with SQLAgentCmdExec account.
May 30, 2007 at 10:13 am
I ended up switching to the local system account for the sql server service and now everything is working...for the moment.
I am now under the assumption the Windows login I had been using wasnt the Administrator login I had been told it was, and didnt have the permissions it needed. I do not have the ability to check this out though.
Thank you so much for the articles. Next thing is to try this on a customers system. I'm sure Ill be referencing the articles as nothing works the same twice for me.
May 30, 2007 at 11:04 am
ahhh ,...but you can check your windows account to see if it has administrator authority ...
xp_logininfo 'domain\username'
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply