Link to home
Start Free TrialLog in
Avatar of curtis591
curtis591

asked on

SQL Proxy Account

We are trying to setup the SQL Agent Proxy account on sql 2000 so that non system admins can exec xp_cmdshell but everytime that we try and type in a account password and domain it comes back and tells us that the specified user can not login.  Does anybody know how to make this work?
Avatar of miron
miron
Flag of United States of America image

without sysadmin grant xp_cmdshell may not be executed.

If you want to execute a sproc you need to run it either as machine local admin windows account or sa which gets resolved to the same security descriptor and permissions by SQL Server. This is not a limitation, but rather last defence for allowing only security principals to execute procedure that can destroy server in one easy command line. If all the trouble is that the account is windows  and the user is failing to logon on SQL Server, set the user as a machine administrator by adding user account to the Administrator's group on the local machine via compmgmt.msc applet. You can try various security settings, but by letting user to execute xp_cmdshell you are in fact entrusting the user - person - with highest permissions can be granted.

THanks
Avatar of curtis591
curtis591

ASKER

What we are trying to do is switch the sql server agent proxy agent.  Right click on sql agent in MMC ---> properties ----> unclick only user with sys....... pops up the box for username password and domain and no matter what we type into this box it seems to just spit back the error message without any pause.   The account we are trying to login in with is part of the System Administrators on the sql server and in the Administrator group on the server.
I tried to go into EM and found no pop - up dialog in the SQL Server agent. I checked on SQL Server 2000.

Opened EM
Navigated to Management
Open SQL Server Agent
Right Click
on the left most tab dialog choice of account to run the service, the choice is between local "system" and domain account by radio button. I have a windows account that is a member of local machine Adinistrator group running server.
I switched the account to a different windows account, account with no privilages on the box at all, just the member of a domain.

On the right most tab there is a choice of connecting to local SQL Server ( if you have named instantce(s) installed it will have a choice of virtual server instances intalled on the local machine ) where you can map a windows account on SQL Server account. I selected account "sa" ( if you try to put in windows account, it should be setup as a valid logon name on SQL Server and granted membership in sysadmin role on SQL Server ,) filled out dialog with incorrect password and got kicked off. Than, corrected the password and got it all working, it just seems to me a bit of a trick, why would you want someone to allow to access SQL Server with masking his Windows credentials under SQL Server account? It is much more ractical to give user all grants as a Windows account and hold the user responcible for what she does using SQL Server log, where each accoun action will be logged. The salt is that instead of limiting permissions, you are in fact granting un - limimited permissions and masking account that can be tracked with sa - something that only DBA should have access to.
Sorry I missed a step the login that we are trying to modify is under the  job system tab for SQL Server Agent Properties.  Then we unclick only users with SysAdmin privilages can execute cmdexec and active x Script jobs and the pop up pops up.   The reason we are trying to do this is we need a user to execute a dts package from an application and we don not want to make this user a system administrator to the server.  The best way we have found to execute a package without having to do additional setup on users computer is call a stored procedure from the app that runs the dts package.
Sorry for not addressing the issue sooner.

My setup agreed to use as a proxy account all accounts, administrators and guest accounts, on the machine where SQL Server is running under domain account. However I tested all accounts I tried by executing logon on the machine where SQL Server is located. I am confident if you could login with the account of your choice on the machine, you will arrive to success with running job under this same account as a proxy account. This is done by adding account to a group of users with least privilages on the machine where SQL Server is running.

Thanks
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20182487.html
https://www.experts-exchange.com/questions/Q.20263554.html
https://www.experts-exchange.com/questions/Q.20273205.html
https://www.experts-exchange.com/questions/Q.20275102.html
https://www.experts-exchange.com/questions/Q.20281649.html
https://www.experts-exchange.com/questions/Q.20296883.html
https://www.experts-exchange.com/questions/Q.20296884.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed in the link below
https://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
Never have found the solution to this problem.  Microsoft can't figure out what is going on either.
Please post a zero point question for this in the Community Support topic area and ask for a refund to close this.
Asta
ASKER CERTIFIED SOLUTION
Avatar of Mindphaser
Mindphaser

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
looks like the case is one of those that can be solved on the place.