Solved

SQL Proxy Account

Posted on 2001-09-13
10
333 Views
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?
0
Comment
Question by:curtis591
10 Comments
 
LVL 9

Expert Comment

by:miron
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.

THanks
0
 
LVL 6

Author Comment

by:curtis591
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.
0
 
LVL 9

Expert Comment

by:miron
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.
0
 
LVL 6

Author Comment

by:curtis591
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.
0
 
LVL 9

Expert Comment

by:miron
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.

Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

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.

 
LVL 1

Expert Comment

by:Moondancer
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.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
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.
http://www.experts-exchange.com/questions/Q.20182487.html
http://www.experts-exchange.com/questions/Q.20263554.html
http://www.experts-exchange.com/questions/Q.20273205.html
http://www.experts-exchange.com/questions/Q.20275102.html
http://www.experts-exchange.com/questions/Q.20281649.html
http://www.experts-exchange.com/questions/Q.20296883.html
http://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
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://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
0
 
LVL 6

Author Comment

by:curtis591
ID: 7024180
Never have found the solution to this problem.  Microsoft can't figure out what is going on either.
0
 
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.
Asta
0
 
LVL 6

Accepted Solution

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

** Mindphaser - Community Support Moderator **
0
 
LVL 9

Expert Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

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)

Join & Write a Comment

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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now