Go Premium for a chance to win a PS4. Enter to Win


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

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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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.  http://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.

*****  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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

886 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