What is a DBA/sa prevented from doing if not a local admin on the SQL Server?

Question

As the DBA and SQL sa, I have always had local admin privileges in all environments: dev, qa, staging, and production. This has given me access to the file system for copying backup files from production to the other environements, the ability to install software (SQL, Analysis Services, SSRS, Windows and SQL patches, etc), and the ability to log in via Remote Desktop. I also view the Event logs, use PerfMon, and create local users, as needed.

 

Although wer'e just a 17-person start-up, we have a zealous IT manager/sysadmin who wants to remove DBAs from the local admin group. He would like to eliminate the DBA's access to the file system, right to install and configure software, and ability to login via Remote Desktop. He also believes that only he should have access to the Event Viewer and PerfMon. (He argues that DBAs should not be permitted to use PerfMon!!)

 

He states (correctly) that it is his responsibility to give to each person just the minimal privileges needed to accomplish their job. Unfortunately, he goes one step too far and defines the DBA's and other's jobs as he sees them. Just several days ago, he argued (unsuccessfully) that database backups were not the DBAs' responsibility, but rather the sysadmin's duty.

 

So my questions are:

 

What OS privileges do DBAs have in your organization? Domain admins, local admins, power users, or custom rights?

What tasks would you be unable to perform if you were not a local admin?

Do you restrict DBA permissions as you go up the development chain, and if so, who takes over the restricted permissions?

 

Any comments are much appreciated.

 

Jeff Roughgarden

Ph.D., MCSD, MCDBA

 

Answer 1

I am a Local Admin on all of my SQL Servers, and none of the other ones.  For SOX compliance, I am not allowed to control backup of specific databases, or even have access to the backups except for approved and documented reasons.  If he expects you to do your job without Performance Monitoring and Event Log access then you have a problem in my opinion.  You have to know what your server is doing to know whether it is healthy or not.  I also have access to a Domain Admin account in the event I actually need to level of rights, which I have never needed it. 

 

It is feasible to think that a DBA could function without access to the Server I guess.  You can get perfcounter data from the SQL 2005 DMV's, and as long as the service accounts have the correct permissions, then you don't really need access to the OS.  I rarely log on to my SQL Servers, but I do most administration remotely since I am a Local Admin on the servers.  I don't know that I would work in an environment that took my control over my servers away from me.  Only a select few of the Domain Admins have access to the SQL Servers in our network, and they are the ones that know what to do and not to do in the event I am away and there is a problem.

 

Answer 2

Jonathan Kehayias wrote:

I am a Local Admin on all of my SQL Servers, and none of the other ones.  For SOX compliance, I am not allowed to control backup of specific databases, or even have access to the backups except for approved and documented reasons.

 

Jonathon, I find this quite interesting. When you say are not allowed to control backups, can you elaborate a little? And if you are a local admin, doesn't that mean you have access to the backup files?

 

I have some experience of the whole "minimal privileges" and "segregation of duties" issue and haven't yet seen a satisfactory solution. Typically I have found that what is actually required for compliance is a grey area and not many people understand it so you just get an individuals interpretation which tends to be a little over zealous.

 

If you have any Domain Admins, is it not true that they have the potential for complete control of the box? If you have windows accounts running SQL Agent then they are must be sysadmins and Domain Admins could "tamper" with these accounts to get access to SQL.

I would like to see a SQL server role which allowed you to monitor the health of SQL server etc but without access to the user databases. In your opinion would that be useful?

 

Interested on anyones thoughts.

Answer 3

richbrownesq wrote:

Jonathon, I find this quite interesting. When you say are not allowed to control backups, can you elaborate a little? And if you are a local admin, doesn't that mean you have access to the backup files?

 

We use netbackup with a HP VLS to do our backups.  The Media servers control rights to specific backups, they aren't on local disk at all.  The Netbackup administrator sets the policies and can grant or revoke access to the the backup files based on need/responsibility.  I dictate what kinds of backups and how often I need them, she actually implements it, and determines when they will run as a part of our enterprise backup planning.

 

If you have any Domain Admins, is it not true that they have the potential for complete control of the box? If you have windows accounts running SQL Agent then they are must be sysadmins and Domain Admins could "tamper" with these accounts to get access to SQL.

 

You know it goes back to what my Drill Sergeant in basic training said about locks.  Locks keep honest people honest.  A true theif is going to get your stuff no matter what you do, but a lock takes the opportunity/temptation away from an otherwise honest person.  I could access the Domain Controllers on our network, and goof around in there if I really wanted to, but I have no reason to do that, nor do I care to look at another server that isn't in my realm of responsibility.  You won't keep someone who is determined to do wrong out of your servers if they are a Domain Admin, true enough.  Of course they could change the password on my Service Account which would cause startup problems for the service, and other such things, but with the service already running, they could get away with it temporarily.

 

A truly paranoid DBA could make a LOGON DDL trigger that refuses any connection using the SQL Service Account that wasn't from the local host I guess.  That would prevent this from being an issue.  I personally am not inclined to go this far.  If they were trustworthy/reliable, then they wouldn't be in the position they are in, or at least I like to believe that. 

Answer 4

I agree with you. I don't actually think its the DBA that thinks in these terms but more those who dictate security policy within an organisation (such as in the OPs example). I believe that there is often a knee jerk reaction to lock everything down to the point where no-one can do anything which may make you system secure but all but unmanageable.

 

MS have made some good strides on the security front by locking down features but i've been in situations where a compliance team has tried to lock down every conceivable loophole. Trustworthiness doesn't come into it. If its possible, it shouldn't be.

 

Still, the locks analagy is a good one - i'll be sure to use that if I have to!

Answer 5

Jonathan,

 

Thanks for the response.

 

In my experience of doing SOX compliance at a prior job, I came to realize that DBAs should not be domain admins. If they need this level of permissions for some situation, they should have to ask for it explicitly. However, I do think they should be local admins on all SQL Servers.

 

To summarize, in your opinion then, is it generally the case, and a good idea as well, for DBAs to be local admins on SQL Server boxes? If not, how would you configure their lesser privileges and what would they be giving up? Would you apply these policies uniformly to all SQL servers in the development chain? If not, who would take over the privileges DBAs lost?

 

And finally, for separation of duties, do you think that DBAs should remove Local Admins from the SQL System Admin role? The idea is that the DBA could log into the machine as a local admin, but would access the SQL server as a member of the SQL system admin group. This permits the DBA to lock out all local and domain admins from the SQL Server, providing true separation of duties. This is how we complied with SOX at a public utility I once worked at.

 

I realize I'm asking a lot of questions, but I think this thread could be of general interest.

 

Thanks again.

 

Jeff Roughgarden

Answer 6

I previously worked for a company that took locking stuff down to the point that they revoked permissions on all kinds of objects in the master database.  This was taken to the point that it was obvious that standard support features that are available from SSMS would become unusable.  I quickly realized that all of these aims would force our database servers into a state in which they would be technically in a state in which they were out of compliance with our agreements for MS support.  Before any of this was implemented I objected strongly stating this non-support as an issue.  Management stated that their understanding was the opposite.  I opened a ticket with MS to verify.  MS confirmed my suspicions, I reported back and the initiative was tabled for a few months.  Once we started approaching the end of the year when annual performance for someone still had the lock down as a target the initiative was re-vived.  The same discussion was held.  I raised the same objections and again was confronted with the same disbelief.  I again opened another ticket with MS and it was again confirmed that the initiatives would break compliance.  The plan was nominally implemented; however, most (read that as maybe all) DBAs agreed that the plan was implemented with actual compliance very low at best.  I left the company, FDIC seized the company's assets.  I was glad I got out first.

 

Answer 7

It really does depend on how "secure" you want/need your environment to be. What sort of activities do you need to perform on the actual OS? What does being a local admin allow you to do?

I would say that its often argued that removing BUILTIN\Admins completely is best practice as it does reduce the risk of people being dropped in as local admins and having access to SQL Server.

 

As i mentioned previously, i don't think its possibel to completely lock out domain admins, just make it harder/more transparent for them to get in (via the SQL Agent account or via any windows group you use as logins).

 

Unfortunately, its a really grey area with more questions than answers.

Answer 8

OK, I’m the security administrator for my company, you know the person that it seems everyone here “assumes” or blames for not being able to do their job as a dba.  My question(s) is this… Why does a dba need access to the OS on the server?  Why does the dba need permissions to reboot a production server?  Why does the dba need access to do anything on that machine other than to effectively manage the database, other than local (physical), remote and administrative access?  I have read these posts and responses but nobody has offered a valid reason.  Look, I’m not trying to be combative here but can someone please offer a reason WHY I should go to bat for my dba’s, the sysadmin folks make their case very clear, they are responsible for the hardware and the OS and the file system on that server, all that I hear from a dba is that he/she needs this access, that’s it.

 

Oh, and enforcement of policy isn’t “knee-jerk” and the security people most likely didn’t create whatever knee-jerk security policies that you have, management does that.  Me, I’m just trying to find a way to make this work for everyone but I need information.  Thanks.

Answer 9

Here is the real deal after thinking about this:

 

A DBA doesn't necessarily need to be a Local Admin, but they do need to have rights to the Server that allow them to do things like, manage database files, view performance counters, apply SQL Server updates and service packs, view System Event Logs, view process information in Task Manager, start and stop services for SQL, and probably a few other things.  If your sysadmins can provide a reason, beyond the fact that they are responsible for the OS, for not allowing the above minimums to a DBA, then the DBA should have these rights because these things are also the responsibility of a DBA for SQL Server.  So long as I had the above rights, I don't care if I am an Administrator on the Server or not.  It is my job to ensure the SLA's regarding SQL and that my services are working properly.

 

This is not just specific to SQL Server.  Our Oracle admin has access to the Oracle server through SSH with permissions to manage items similar to those listed above.  Since the database engine runs on the OS, you have to know about the OS also to be able to properly manage and administer the database engine.  To me this is always an over controlling person in one form or another trying to exert their power of another.  DBA's do it the same way, as there are other posts on here where DBA's are arguing why server team members should have no access to SQL Server in any way, shape, form, or fashion, which is equally ridiculous. 

 

Answer 10

Don,

 

I appreciate your response. You ask:

• Why does a DBA need access to the server OS? This question reveals the need for some education about what DBAs do. DBA's need access to the server OS for many reasons, simplest of which is to manage SQL files (*.mdf, *.ndf, and *.ldf) and backup files (*.bak). In the course of attaching and detaching DBs, one sometimes needs to access a file directly, to rename it, or to copy it. Similarly for backing up and restoring. Copies of production backup files are regularly copied to dev servers. If DBAS are to responsible for the overall operation, integrity, and reliability of SQL Servers, they must be able to check on whether Windows as well as SQL Server updates and patches have been applied. Of course, DBAs should never apply Windows patches and updates, but they can discuss the merits of applying them or not. DBAs should have the final say on applying SQL patches and updates, and should also apply them personally. In a sensitive enterprise (e.g financial or health industry) where DBAs must be restricted maximally, the Admin can apply SQL updates and patches with the DBA watching over his shoulder.
  
• Why does a DBA need permission to reboot a SQL Server? Rebooting a production SQL Server should require a joint decision made in advance, preferably with mgmt approval, in which case it doesn't matter who actually does it. If either the SysAdmin or the DBA reboots a production server without consulting the other or mgmt, depending on the outcome, his head might justifiably roll.
  
• Why does the DBA need access to do anything on that machine other than to effectively manage the database, other than local (physical), remote and administrative access? I'm not clear on this question since local and remote admin access would suffice just fine, but DBAs need to use/access
  • PerfMon regularly to establish and monitor benchmarks, create traces for importing into Excel for further analysis
  • EventLog to review events
  • Disk Management or SAN software to review the state of disk storage
  • Remote Desktop to manage servers from home or the road when necessary
  • Software installation rights to install and configure Server Monitoring software, and other diagnostic software as needed
    
• WHY should I go 'to bat' for my DBAs? The DBAs are not your's any more than you are the DBAs' Security/System Admin. Going to bat suggests you're facing an opposing pitcher or entity. As Security Admin, you are implementing likely vague instructions from senior management to 'run a tight ship' or the equivalent. Since senior management is clueless about technical details, in fact, you are defining a tight ship. Blind acceptace of senior management's delegation will make you the pitcher opposing the DBA at bat. A wiser course is to cast yourself on the same team as the DBA, as perhaps the third base coach, enforcing rules that will help the DBA batter advance the runner rather than swing for the fences.

In the end, and especially in a 15 person startup, you and the DBA are on the same team. You (and your options) sink or swim together. If either of you feel the other is incompetent, that's a different matter. But if you both respect each other technically, you have to live with joint responsiblity over the totality of SQL Servers, not necessarily infrastructure versions for Exchange and Sharepoint, but main line versions supporting the core business of the enterprise..

 

I seriously doubt 'management' is micro-managing your firm's security policy, but if they do, you seem sharp enough to find a better position. If they don't, then I'm afraid your attitude does suggest an unpleasant and unproductive desire to do battle on the field of ego.

 

Jeff Roughgarden

Ph.D., MCSD, MCDBA

Answer 11

I was thinking you two worked for the same company, and his question was a followup to yours.  Intersting how big a question this really is, and how many other people are facing this problem more and more.

 

Jeff Roughgarden wrote:

WHY should I go 'to bat' for my DBAs? The DBAs are not your's any more than you are the DBAs' Security/System Admin. Going to bat suggests you're facing an opposing pitcher or entity. As Security Admin, you are implementing likely vague instructions from senior management to 'run a tight ship' or the equivalent. Since senior management is clueless about technical details, in fact, you are defining a tight ship. Blind acceptace of senior management's delegation will make you the pitcher opposing the DBA at bat. A wiser course is to cast yourself on the same team as the DBA, as perhaps the third base coach, enforcing rules that will help the DBA batter advance the runner rather than swing for the fences. In the end, and especially in a 15 person startup, you and the DBA are on the same team. You (and your options) sink or swim together. If either of you feel the other is incompetent, that's a different matter. But if you both respect each other technically, you have to live with joint responsiblity over the totality of SQL Servers, not necessarily infrastructure versions for Exchange and Sharepoint, but main line versions supporting the core business of the enterprise..   I seriously doubt 'management' is micro-managing your firm's security policy, but if they do, you seem sharp enough to find a better position. If they don't, then I'm afraid your attitude does suggest an unpleasant and unproductive desire to do battle on the field of ego.

 

From what I read from Don's post, he is trying to figure out the middle ground where the DBA's have what they need, while still maintaining an appropriate restriction of access to suffice the server admins.  I don't take him as providing any kind of attitude or ego in his response.  To me Don would be facing the opposing pitcher in his request, since he has to go against the policy set forth by the Server Admins who say the DBA has no business in their Server.  I don't see blind acceptance of this statement at this point from his post. 

 

I went ahead and asked this question on the MVP Private Newsgroups to get some feedback from those with much more experience than I have in managing SQL Servers as consultants and in very restricted environments.  When I have some feedback from them, I will gladly repost it here. 

Answer 12

 

Without starting some “virtual” brew-ha-ha over the subject of security versus access levels for DBA’s, I must say that it seems that I stepped on someone’s big toe in my choice of words.  As I stated, I am the security administrator and it is my job to monitor and enforce the security policy, for everyone, not just users, but sysadmins and DBA’s which is why I came here looking for a solution, not a lecture.  No management does not micromanage security but they do like to know what’s happening and if my use of the terms “my DBA’s” and “going to bat” upset you then that leads me to assume that you are a DBA scorned by policy, again which is the reason I’m here trying to find any valid reasons to request a change in policy that will allow more control for DBA’s in my company, thus making the enforcement of security policy much easier.  Personally, I’d rather have a solution that everyone can live with than have someone forego the rules and jeopardize everything… wouldn’t you agree?

 

Judging by the credentials that you display at the end of your posting I can only assume that you must be an intelligent person, so please when you decide to quote me or use my words I would ask that you use them in the context as I intended, not to suit a point that you’re trying to make, I never asked why would a DBA need to re-boot a SQL Server, even I know the answer to that.  And I believe that my use of the terms “my dba’s” or “going to bat” does suggest that I am a team player and want an amicable solution, not claiming possession or ownership.  As to your reference “…in a 15 person startup, you and the DBA are on the same team” this suggests to me that you might could be top dog there, excuse me, management, however, this IT shop has more than 15 people and neither are we a startup, which really has absolutely nothing to do with teamwork or doing what’s right for the company.

 

As another reader suggested, I am trying to find a way that will allow DBA’s and SysAdmins (which I do no have to do, could just enforce policy) to some agreement that is based on fact, not what anyone thinks, and to this point as I said before, I cannot find anything that will support the case to help DBA’s get what they’re asking for.  I’ve spoken to admins here and in other companies that say the following items do not require Local Admin Access;

 

• PerfMon
• EventLog

 

 

These other two items are separate issues all together, and should be addressed separately as far as policy is concerned, more specifically, Remote Access, Change Management and Configuration Management to address changing any aspect of a production system.

 

• Remote Desktop to manage servers from home or the road when necessary
• Software installation rights to install and configure Server Monitoring software, and other diagnostic software as needed

 

But thanks for the responses and I’ll look for more on this topic.

 

Thanks, Don

Answer 13

Don,

 

You did not offend me, and I apologize if I offended you.

 

I tried to explain to you what a DBA needs by answering the questions you posed.

 

In my experience, it's simply been easier to make DBAs local admins on SQL Servers. However, if that is somehow not acceptable for security purposes, then it would be very useful if a Network Admin would publish a list of detailed permissions that would be adequate to permit the activities I listed above.

 

I'm looking for something like the DBA could be an ordinary user, but with right A if he needs to do X, right B if he needs to do Y, right C if he needs to do Z. I listed the X,Y,Z parts but don't know the A, B, & C parts. It would be even nicer if this sort of thing was scriptable.

 

Jeff

 

Answer 14

Thanks, I appreciate that, no hurt feelings here.  Security is my only concern in this issue, I want to maintain a secure computing environment and if we could base our decisions on fact and absolutes to me that’s preferred over what’s easier, because as we all know easier isn’t always right.  I would not ask the SysAdmin to provide a list of what he’s willing to give up because that only leaves us where this all began.  What I would like to have is documented proof that a DBA cannot do his/her job without specific permission, more precise local admin.  In my world, security, these things lead to people looking for short cuts around controls.  Someone in one of the previous posts mentioned that DBA’s have DomainAdmin rights… WHAT!  WHY?  They’re not doing any DomainAdmin functions, even if you are a small shop then there should be multiple userid’s that represent the job being performed at the time.

 

Again, this is something that I really do not need to do, I could just enforce policy and say “live with it”, and really, if the DBA cared about it he would be running this stuff down, but in the team environment what affects one affects all… so here I am.

 

Again, thanks for the help.

 

Answer 15

DonJack wrote:

Someone in one of the previous posts mentioned that DBA’s have DomainAdmin rights… WHAT!  WHY?  They’re not doing any DomainAdmin functions, even if you are a small shop then there should be multiple userid’s that represent the job being performed at the time.

 

Reread that post in context.  I said I have access to a Domain Admin Account.  I didn't say I have Domain Admin rights.  My personal user has no rights except to SQL Servers.  I can only use the Domain Admin account when documented, and approved by my Department Director, and that would only happen in the event that no Server Admin was available and a specific task requiring Domain Admin rights had to be performed in their absence to resolve a problem.  I can think of a couple of scenarios where this could be required, but they are all theoretical and highly unlikely.  As I said in that post, I have never had a need to use it.

 

This isn't distinct to my company alone.  I know some DBA's for financial firms that have similar systems in place where DBA's can get full access to systems through a workflow requiring Department Head approval, and they can do it automatically using internally developed applications that have documentation and auditing for SOX and or PCI compliance audits later on.  There are always controls in place to prevent the abuse of escalated rights in these types of environments.

Answer 16

One of the problems that I have under these circumstances is running server performance benchmarks.  Specifically:

• Memory bandwidth performance benchmarks
• Various disk performance benchmarks including (1) random, buffered and sequential read througput, (2) random, buffered and sequential write throughput, (3) IO rate throughput
• CPU performance benchmarks
• Level 2 cache performance benchmarks
• Networkbandwith performance bentchmarks

These are benchmarks that do not come from the standard perfmon counters and therefore perfmon availabilty does not help for these benchmarks.

 

I typically run these quarterly unless a problem has been identified.  If a problem is identified I will run these as needed.

Answer 17

DonJack,

I'm trying to work through the same minutia right now in our company.  I'm a sysadmin and we're trying to implement minimum permission SQL servers, and we get the same vague answers to the question:  "Why exactly do you need Local Administrator access on the SQL Servers?"

Usually it starts out as "I need access to the filesystem".  When I explain that can easily be achieved through a share, it becomes something else that can be achieved if you take a little time, define your requirements and let the sysadmins configure the server appropriately.

The problem is that people don't like giving up access, plain and simple.  Nobody in this thread has given a single technical answer on why a DBA needs Local Admin.  Even those with some pretty impressive looking credentials in their sig can't come up with an answer.  You know why?  Because there isn't one.  Other people do this because the DBA works with them and doesn't have some kind of admin envy complex.  What this really comes down to is personality conflicts.

For my company, it's up to the sysadmins to keep the servers up.  The best way to achieve stability to any system is to reduce the number of people who can cause the system to go down.  The effect is that it makes troubleshooting and documentation easier, which in return causes your system to become more stable.  This is especially important in cluster configurations.

You've been more than polite by not wielding your "Security Admin Sword" and trying to collect valid information.  I say either let the DBA's have the box (and be responsible for keeping it up) or lock them out.  How can you effectively manage a system with different groups changing things on the OS layer?  You can't.

Answer 18

Running Database Consistency Checks (DBCC)

Only users that are members of the sysadmin server role or the database
owner can execute DBCC commands. Execution of DBCC commands cannot be
granted to non-sysadmin users.

or

configuring SSRS and IIS

and installing SQL Hot fixes.

Personally i have no problem having the responsibility on administrating the box on which SQL Server resides, in fact I'm happier with that arrangement!

Answer 19

Very interesting and informative posts. I enjoyed reading them.

---

Mpumelelo

Answer 20

Hi,

I think your boss is 70% right, even if he's a bit over-zealous.

I'm the SQL sysadmin of our database boxes, but just a Power User + Remote Desktop User privileges from Windows security perspective.

I can't install Windows patches or new apps, update drivers, reboot the servers, format disks, add hardware, change RAID configuration, create

AD groups or deploy GPOs. That stuff falls in AD and OS team territories.

On the other hand, I can log to my boxes via RDP and run Perfmon or Event Viewer as well.

When users complain about slow performance, you need to take a quick look at Windows Task Manager, run Perfmon and even browse Event Viewer.

Otherwise, transfer all those 3:00 AM slowness complains to your boss. You'll see how quickly you regain access to Perfmon ;-)

My privileges don't change from Development to Production, in fact, I'm needed the most in Production! That's where nightmares occur and where

my skills come handy.

Database and NTFS backups are different beasts with their own strategies and intricacies behind.

I'm quite sure that your boss comes from an AS/400 background, where a database and a folder ("library" in OS/400 terms) are the same thing.

But, on the MSSQL world, that vision doesn't apply.

For example, you can't have a Point-In-Time consistent database restore relying exclusively NTFS backups.

If he wants to relieve you from DB backup, then ask him to sign a disclosure form stating that you're not responsible for database safeguard.

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu