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?

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.

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.

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.

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.

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.

Featured Post

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server.
First, to frame this stu…

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…

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed