January 9, 2011 at 12:43 pm
sqldba_icon (1/7/2011)
M only aguement is if i do not have proxy set up for xp_cmdshell a user can only harm the server if he has sa priviledge. Lets say there is a usera with sa priviledge on sql ServerA but does not have admin priviledge on the box itself. However since he has sa priviledge he could enable xp_cmdshell and do what he wants on the box through sql client. So my feeling is if we do not have proxy accounts setup we do not need to worry about xp_cmdshell..does that make sense?
I don't know if this stuff has changed since I last looked at it, but thje above sounds to me as if you are running the SQL Server service under a Domain Admin or a Local Admin. That's an outrageously in=secure to do (although I must admit that I sometimes did it, when I had what I hoped was enough control over the firewall config and over the NT User accounts as well as over the SQL accounts).
Tom
January 10, 2011 at 8:25 am
SQL Server is running under domain account. Which account would you prefer?
January 10, 2011 at 8:39 am
Domain account is fine, a domain *ADMIN* account is not.. And MANY people add the account that SQL runs as to the local admin group on the box. It does allow for potentially undesireable behaviors but in the scheme of things not horrible..
CEWII
January 10, 2011 at 9:15 am
The advice is
- domain user
- everyone group ONLY
- use SQL Server Configuration Manager to assign the account as the service account(s)
- add specific file share rights to access folders as needed, just as you would for a normal user.
January 11, 2011 at 4:29 am
The OP mentions SQL 2005, SQL 2008, Windows 2008 and Windows 2003. Although there are no differences in how xp_cmdshell works within SQL 2008 and SQL 2008, there are differences in behaviour within Windows between Windows 2008 and earlier operating systems.
If the person running xp_cmdshell has sysadmin authority in SQL Server, then xp_cmdshell will run using the SQL service account security context. If the person running xp_cmdshell does not have sysadmin authority in SQL Server, then xp_cmdshell will run using the xp_cmdshell proxy account authority. I think everyone was agreed on this.
If the SQL Service account has local admin authority in Windows, then the windows rights available to xp_cmdshell will depend on the operating system. Under and operating system prior to Windows 2008 and Vista, xp_cmdshell will have full local administrator rights. Under Windows 2008 and above, any account in the local administrators group has two sets of rights - the privileged set and the unprivileged set. The unprivileged set uses the same set of authorities as the local users group. If user account control is active (by default it is), then any use of a local admin account will run under the unprivileged set of authorities, unless it explicitly asks to use the privileged set. Not everybody picked up on this difference.
Assuming that the local users group has not been given rights to start and stop services, this means that if you run xp_cmdshell as a sysadmin and the SQL Service account is a member of the local administrators group, then on Windows 2008 you will not be able to start and stop services. If you ran the same scenario on Windows 2003 you would be able to start and stop services.
If you want to start and stop services via xp_cmdshell on Windows 2008, then an approach that would work is to run a script that is either configured to run as privileged or asks to be privileged and which also includes the service start and stop requests. It would be poor practice to use the alternatives of turning off UAC or granting service control rights to the local users group.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 11, 2011 at 8:37 am
Excellent explanation, Ed. This would actually make a great article if you wanted to write it. I wasn't aware of the UAC issue for local administrators on W2K8.
January 11, 2011 at 9:23 am
Ed,
Are you sure about UAC as it relates to xp_cmdshell? I seem to have seen that the two sets of rights that we see interactively is not true when we are running non-interactively, as we are in xp_cmdshell. If the user has admin rights then nothing special has to be done in xp_cmdshell to us them.
I ran into this on a WIN 2008/SQL 2008 environment..
CEWII
January 11, 2011 at 9:29 am
Sounds like there could be more to this than I have checked...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply