Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Proxy Account

Posted on 2001-09-13
Medium Priority
Last Modified: 2012-08-13
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?
Question by:curtis591
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 6480929
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.


Author Comment

ID: 6481094
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.

Expert Comment

ID: 6481246
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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Author Comment

ID: 6482727
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.

Expert Comment

ID: 6487133
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.


Expert Comment

ID: 7018732
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.
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.

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.

*****  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 
POINTS FOR EXPERTS awaiting comments are listed in the link below
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.
Moderator @ Experts Exchange

Author Comment

ID: 7024180
Never have found the solution to this problem.  Microsoft can't figure out what is going on either.
LVL 27

Expert Comment

by:Asta Cu
ID: 7144170
Please post a zero point question for this in the Community Support topic area and ask for a refund to close this.

Accepted Solution

Mindphaser earned 0 total points
ID: 7144431
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

Expert Comment

ID: 7145118
looks like the case is one of those that can be solved on the place.

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question