Server Administrator Permissions
-
Hi all,
Our company recently had a security audit in which our DBA access to the production databases was called into question. To be specific, the auditors wanted to know why it was necessary for the DBA to be an administrator on the server that contained SQL Server 2005. I know of the necessities for installation, and I have run into problems with jobs and tasks between our different servers (as far as ownership and permissions), but I was wondering if anyone could offer a definitive source or explanation of the advantages of Administrator access to the SQL Server machine. Any thoughts would be greatly appreciated!
Thanks.
-
I've always found this makes general troubleshooting easier (and I've run into a few situations in which I think it was necessary, I'm trying to recall specifics)
In general, being able to view error logs on the server, start/stop services, etc is nice for day-to-day stuff (not sure this requires administrative privileges).
I've had to reconfigure the security on a server to let linked servers, replication, and RPC's go through on the accounts being used for this access. I've had to change accounts that start the SQL services - frequently (this was a problem related to having changed the name of the machine after SQL was installed)
I'll let you know if I remember anything else, but I have found that it is generally nice to have unfettered access to a server when you're troubleshooting puzzling emergencies.
-- http://dbachman.blogspot.com
-
Thanks for the prompt reply, Grasshopper! Yes please let me know of anything else you think of or if you happen to think of any other sources I could check.
Thanks again!
-
I'll probably catch some flak on this answer but here goes:
To be honest, your DBA does not need to be a local admin on the machine in question assuming that the machine has been properly configured and there are competent sysadmins available/on call to assist the DBA should additional permissions be required to troubleshoot a problem, etc.
You cite a security audit, by a third party I would assume (e.g. SOX audit?), which implies that your organization is big enough to have an IT staff of more than a few individuals in which case the DBA should be only one of a team of individuals responsible when/if there are problems with the production server which pretty much kills the standard arguments for why the DBA "needs" local admin on a server - the team as a whole is responsible for identifying, correcting and then documenting the root cause, etc. As a matter of fact, the DBA may not (or maybe should not) even be in the 1st tier of individuals called when there is a problem?
Joe
-
DBA does not require Admin privileges on Windows but most companies give that so that he can perform his tasks with ease without needing them to depend on the Windows admin. I have worked for a company that does not give windows admin to DBAs they do have only DBA rights that too much restricted.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thanks for the replies. Joe, I can appreciate the scenario you outline in your response, but I don't believe we have reached that kind of size just yet. If anyone has any further input on situations where Windows Admin access has been necessary (whether for SQL accounts work, scheduled tasks, maintenance plans, jobs, etc) please continue to post. Thank you very much!
-
Joseph -
One last thought on this one - MOST functions that might be performed by the DBA or by SQL Server itself in the course of normal activity (e.g. scheduled tasks, jobs, etc.) do not require Windows Admin/Local Admin on the server. If the DBA or SQL Server needs access to additional resources (e.g. a physical or network drive), the permissions granted/used should be the minimum necessary to complete the job. The principal of least privilege applies...
All too often I see SQL Server service accounts set up as domain admin or server admin accounts - which is often the easy way out when trying to get SQL Server or it's components to interact with the operating system/gain access to resources.
Joe
-
DBA without Local admin rights ... Been there
The right answer is "it depends" ... If you have SLAs that are tight and a server requires you to run certaint scripts that may fail at time 0 because of security or access rights I guarrantie you that you won't be pleased. I know what you are going to say next ... well you should have run that script and determine which are the necessary rights in advance ... Sorry but hardly ever systems are that static
On the other hand if your SLA account for a decent time so that windows admin do their thing and you can work on a team basis well that is a totally different ball game. You can definitelly restric the privileges as much as you want (just don't over do it)
Just my $0.02
* Noel -
Another instance when a DBA may have local admin rights on a box is when the DBA is responsible for "soft" server administration support. This has been the case in a couple of places I've worked. Where the box is owned by a small local team and the true server support guys are out of town. It has been my responsibility to watch hard drive space, Event Logs, use Perf Mon to troubleshoot issues that may or may not be SQL related.
Then, when it comes to hardware repair or something complicated regarding the software, we send the ticket upstream. But for little stuff, the DBAs maintain the boxes ourselves.
-
Good point Brandie, and that is most like our scenario now. Also Joe, I have run into problems with tasks/jobs etc in the past with this company because of the need to have them run across multiple servers on different domains. I've tried to find suitable solutions for this, but in the interest of saving time and money not many resources could be devoted to a 'best solution.' Would you, or anyone, have any ideas on this one? It's kind of a side note, but when setting these jobs up I found Administrator access incredibly valuable.
Thanks!
-
To have jobs run across multiple servers, you don't need Admin access. You need a plain vanilla domain user (not domain admin) account for the SQL Services to use and you need that account to have read/write permissions for the network shares or server folders that are being used for these jobs. The domain user account needs to have permissions on each of the boxes also, though it may or may not need local administrative rights.
I don't recommend giving the Services account any admin rights at all if possible because it's a major security hole if someone gets a hold of that account & password. But if you have all your AD group security policy permissions set up correctly for the domain user account and have permissions for the shares and run all your SQL Services through that account, that should take care of most of your issues.
Oh, and the domains have to have 2 way trusts set up between them and have a login that can be impersonated through SQL Server. The login and password should be identical on both domains, even if the domain name is different.
-
I'll echo Brandie here - access to resources outside the local machine requires a domain or other account with access to the target machine whether another windows machine, NAS device, etc.
SQL 2005 changes the equation somewhat in that we now have various proxy accounts available to us which allows a SQL Server configured to use a proxy account for access to external resources, even when configured to run as a service account on the local machine.
Often the hardest part of accessing remote resources in a secure manner is figuring out the context/"who am I right now" of the process that is attempting to access the remote resource. Depending on what you're doing you could be running under the content of the SQL Server account, SQL Server Agent account, SSIS account or a proxy account... but it's well worth the time/effort to understand/determine what the context is and how to get by without running under an admin account.
Running using a windows admin does make your life a lot easier but is a bad practice for a lot of reasons. Probably the most frustrating & serious of those reasons is that by running as admin you are short circuiting a lot of the security protections build into both the operating system and SQL server - not too many years ago, SQL Slammer nailed a lot of folks and the worst hit were those running their SQL Server machines with local or domain admin service credentials, an "owned" server running under elevated privileges is just a nightmare.
Another common issue I've seen when running SQL server using admin privileges is that eventually you'll end up running everything using elevated privileges, largely because nobody knows how to do anything different - eventually it will come back to bite you in the butt whether on an audit or when someone takes advantage of the hole that's been created. I think you'll find that cleaning up the mess after the fact takes a lot longer than doing it right the first time...
Joe
-
To echo what's been said: local administrator rights aren't strictly required but often granted. Why?
- Ability to stop and restart services (although power user can give you this, Mark Russinovich demonstrated how to escalate privs from power user to administrator).
- Ability to view the security event log.
- Ability to take ownership of files, if necessary.
- If dealing with a cluster, ability to manage clustered resources.
K. Brian Kelley
@kbriankelley -
You can also list the needed permissions for a user and apply them through a security template on the SQL Server. In this way you DBA have permissions to start and stop the sql services but not the others. Through templates you can grant selective permissions.
-
This is true... and those templates can be applied via GPO so you can push out to all SQL Servers. However, if you go down this road, don't forget to include auxiliary but related services (such as MS DTC).
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply