May 3, 2004 at 2:03 pm
I have a set of active/passive sql2k servers w/t sp3 and xp_cmdshell is not working for the use of non-sysadmin jobs. Please tell me how I should get that to work in steps.
In addition I have the following questions:
1) For the use of sqlagent proxy account should I assign a admin domain account or a non-administrator account is also working? (This is at the domain level)
2) Should I give this domain account (using windows authentication) a Sysadmin Server Role or some other role? Also what database role should I give to this domain account if I need have this database specific?
3) How do I test this account?
4) Should I have sa as dbo or a sysadmin as the dbo works the same?
5) How about grant execute?
6) Does the Microsoft have a fix because of sp3 causing xp_cmdshell not functioning? Any suggestions? Or this will work through sql2005?
7) What else I should do?
8) I have a .Net app trying to execute xp_cmdshell as non-sysadmin using the sql proxy account.
Thank you
ktf
May 3, 2004 at 9:36 pm
1. A regular domain user account should suffice.
2. This account should have "logon as a batch job" right. Besides it should have SQL Server access and all other permissions that you expect whatever xp_cmdshell command requires to do. Let's say if xp_cmdshell is executing a DTS job and the dts job accesses 5 folders and 3 sql servers for read/write it should have perms to all those.
3. Login with the account and run whatever command you expect to run from xp_cmdshell.
4. sa is "a" dbo of all databases. I do not think you can override that.
5. explain more..
6. I have it working it in a cluster. So may be specific to your environment. Provide details on what is not working with error message. Memebers will help!
One corollary, the SQL server agent account should be part of the windows administrators (local group) for the sqlagent proxy to work.
Also be aware that some domain admins have more restrictive domain policies that override your local account rights. Verify that they are not overwritten.
Please post where and what is not working.
May 4, 2004 at 7:34 am
Please tell me how I should get that to work in steps. It was working before sp3 installation.
Thank you
May 4, 2004 at 8:25 am
If it is not working now, you have to explain where it is not working. Like,
what is it that you are trying to do with xp_cmdshell (is it BCP, batch file execution, DTS or something else)
Where does xp_cmdshell run from (again is it in a stored procedure, in a DTS or a SQL agent job)
Where does it fail (launching the job, running the job for lack of permissions)
The only thing that comes to mind with SP3 is how MS fixed the cross database ownership chaining issues. This might have broken the permissions for sqlagent proxy account in some databases. Can you enable the cross DB ownership chaining (refer to BOL) and see if it fixes that (though you may have to fix the permissions in the long term, since it is not recommended to enable cross db ownership chaining).
May 4, 2004 at 8:36 am
Our main use is to create sql accounts and reset passwords through proxy agent by non-admin or anybody. This call comes from a .net application and was working before sp3 patch.
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply