Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Should DBAs have local administrator rights?

This was actually spurred by a post from Ted Krueger (@onpnt), which led to a short, but hearty, discussion on Twitter. He was discussing removing access from a local server admin to the SQL Server. My discussion was in regards to the fact that this isn't a successful preventative control. A system administrator who knew what he or she was doing can bypass the lack of access and still get access to SQL Server. I've gone into how previously, so I won't rekindle that debate here. But that does raise another question, and another controversy.

Should DBAs have local administrative rights on their SQL Server? DBAs would argue that it makes their jobs easier. Indeed, it does. But server admins and security folks would argue that it makes their jobs harder. I've been on both sides. I'm back as a senior DBA now, but I spent the previous seven years as an infrastructure and security architect. I've seen some crazy stuff done by folks who have admin rights and who don't understand the implications of what they are doing. Notice I was quick not to limit it to DBAs. Case in point, a particular IT pro (not a system administrator type) created a share on a production system where Everyone had read permissions through the share and at the NTFS level Authenticated Users had read rights as well. Meaning anyone on the domain could access that share. And that share periodically contained sensitive information. Internal audit caught it and flagged it immediately. But you get the idea.

The argument against system administrators having rights inside SQL Server is that they don't necessarily have a full understanding of what they are doing. But couldn't that same argument apply in reverse? Yes, yes, it could. When I start talking about GPOs, about where things are in registry, about what services are critical, about NIC configuration, about shares and NTFS permissions, quite a few DBAs start getting that glassy-eyed stare. The same stare you get when you start talking recovery models, rebuilding indexes, securables, and the like when talking to most system administrators. So if the argument applies in one direction (no to sysadmins because of the lack of knowledge), it must apply in the other (no to DBAs because of the lack of knowledge). Meaning DBAs aren't local administrators on the SQL Server (and incidently, neither is the SQL Server or SQL Agent service, since it's a simple matter to privilege escalate using them if you're a DBA). Yeah, I said it. I know it's not popular. But it's the logical argument carried back in the other direction.

Another point that is made to keep system administrators out is separation of duties. Sysadmins shouldn't be touching the SQL Servers because it's not in their job duties. As a matter of fact, to prevent one person from stealing everything, the duties are split and so are the permissions. Now, realistically this doesn't work, but it's a good argument. And if it's a good argument as applied to system adminstrators, it's a good argument when applied to DBAs. Meaning DBAs have the rights to their SQL Servers, but not to the servers themselves. Again, yeah, I said it. And again, I know it's not popular. But again, it's the logical argument carried back in the other direction.

So does this mean we should just forget the whole thing? Or does it mean we should just lock everyone down, start building trenches, and lobbing mustard gas at each other (though that's banned)? Well, it depends. Yeah, I said that, too. It depends on your organization. It depends on the data. It depends on the job functions. It depends on the other controls in place. There are enough factors that you can't give one of those "best practice" answers and move on. You really have to consider each and every situation independently. Is that a cop out? No, that's reality. I know of cases where controls say production DBAs can access a particular server but development DBAs can't. That's even more stringent. But if you're talking HR data or intellectual property, maybe that makes sense for your organization. But if you're going to consider stripping local admins from getting into SQL Server for security reasons, then I would think that if you're serious about security, and not just about building personal fiefdoms that you've got to look at the other side, too. You might come to the conclusion that DBAs need to main admin rights. And if that's fine with your organization, there's nothing wrong with that. Just as long as the question is considered in the first place.

 

Comments

Posted by Steve Jones on 20 November 2009

I'm on the side of allowing some Windows admins rights into SQL Server, if for no other reason than to be my remote hands in an emergency. That's assuming you don't have many DBAs.

I do think you need strong auditing, for DBAs and anyone accessing SQL Server as a sysadmin.

Posted by David.Poole on 23 November 2009

I would say train the DBAs up on Windows security and make them responsible for that server, assuming it is purely a DB server.

If it is something other than a pure DB server then perhaps not, depending on SLA and support agreements.

The last thing you want with a 3am support call is to play support ping pong trying to get an incident resolved.

Posted by kgayda on 23 November 2009

DBAs should know Windows security as part of their jobs.  If you have a MCDBA, knowing Windows security is a requirement for the certification.  

SQL Server should always be on its own server as well.  Too many resources needed by a DB engine to be sharing duties with other apps.

Lastly, if a DBA or anyone else makes a bonehead mistake of giving any user read access to sensitive data, they should be formally reprimanded.  This is a serious issue and has nothing to do with job function.  Everyone who can create a share and who has custody to confidential data should be trained in how to securely manage it.

Posted by kgayda on 23 November 2009

DBAs should know Windows security as part of their jobs.  If you have a MCDBA, knowing Windows security is a requirement for the certification.  

SQL Server should always be on its own server as well.  Too many resources needed by a DB engine to be sharing duties with other apps.

Lastly, if a DBA or anyone else makes a bonehead mistake of giving any user read access to sensitive data, they should be formally reprimanded.  This is a serious issue and has nothing to do with job function.  Everyone who can create a share and who has custody to confidential data should be trained in how to securely manage it.

Posted by Atul on 24 November 2009

DBA should have admin access to servers. In small to medium organisations, there are not many DBAs and SysAdmin people around. If the DBA does not have admin access, there are lots of dependency on SysAdmin person and can create real problem in case of emergency.

The DBA team is provided little more than basic of System Administrations so that they should know what they are doing. Proper audit is always conducted by the senior DBAs to make sure everything is in cotrol.

In most of the organisations, they have dedicated shared database server. So allowing the DBA to have admin access to the database server will be good for the DBA and SysAdmin team as they can work independently. The DBA team should be responsible for the server as full owner. The SysAdmin team works as support team to ensure that the OS is patched properly, managing user access list and permissions. This is how my team work here.

Posted by Silverfox on 25 November 2009

I can honestly say without a shadow of doubt in my mind, that dba's should have local server admin access, it is not as simple as saying it makes the job easier, for the clients i have worked for, working as a dba I have had to do server monitoring, writing scripts that access the server and you cannot do parts of your job without local admin server access. In the past I have done wintel duties and even been part of the domain admins group. and this is apart from that fact there are bugs in sql server that can only be bypassed by having local server admin access, bugs that are still even in sql 2008. such as remote activation/management for services and dcom/SSIS from your local machine to a remote server.

Posted by EdVassie on 25 November 2009

I agree with K Brian Kelly that SQL DBAs should not have local admin rights, and neither should any SQL Server service accounts.

The only time local admin is needed is when installing software, and this should be done using a different account to the DBA logins.

To me, the biggest issue in achieving no local admin is knowing what rights are needed for the DBA accounts and the service accounts.  Even with SQL 2008, you have to reference KB articles as well as multiple BOL sections to get a full picture of the service account requirements, and these are easy to find compared to DBA account requirements.  Just to add a bit of spice, some of the ground rules that DBAs have been using since NT4 have changed with Windows 2008 and above, and forum posting show many people have not caught up with this.

One of the design criteria for SQL FineBuild is that no local admin access is needed for any account, and I think the FineBuild Reference document contains one of the most comprehensive list of rights and privileges available.  But even after a lot of research I would not be surprised if I have missed something.

Posted by vinizio on 25 November 2009

I think it is a matter of the size of your organization. In medium to small companies in where you might be performing more than one specific task, let's say you have a Sysadmin and a DBA, these two individual have to work closely in full comunication. It is a work of a TEAM. When you are Sysadmin/DBA this is out of the equation due to de fact that you are performing both. Of course, it would make the DBA job easier to have local admin rights, in addition if you are a DBA, and having not only the right to access your company's information, you certainly know what you are doing. So having local rights when you are in a medium/small organization helps a lot. It's all depend on size and protocols of your organization.

Posted by jetgunner on 25 November 2009

I believe DBAs should have sever admin authority.  Database servers are a different breed of server.  The database software is tied in so closely with the OS and hardware that it only makes sense to administer them both concurrently.  Plus, I don't think a DBA can be successful without a proficient understanding of operating system duties (memory management, storage management, networking, security, etc) since they go hand-in-hand.  

In addition, I also believe the separation of powers idea can have a severe negative effect on database servers.  It's the "too many cooks spoil the broth" idea.  These servers and the data they contain are the most critically important part of the company's systems.  Having multiple people making changes to a server that could affect the entire server, OS and/or database, is a recipe for security holes, performance problems, etc.  It all leads to increased liability, and wasted time and money trying to troubleshoot a problem that is the result of a change on "the other side."  Having one person or one group of people manage the entire server helps mitigate this, and this one group should be the DBAs for the reasons mentioned above.

Posted by s.c.simmons on 25 November 2009

Many years ago, I noticed that the more I learned about Windows network security, the less total happiness there was in my life. So I'm perfectly satisfied with the user account I have on my SQL Server box. I have a close working relationship with the sysadmins; if I need something done, I tell them what & why, and they take care of it, and they don't make changes to the configuration without talking to me about it first. Then, I can focus my time & energy on things I'm better at and enjoy more!

Posted by K. Brian Kelley on 25 November 2009

s.c.simmons, amen! This is the big revelation I have had in the last 10 months of life.

jetgunner, so let's follow that logic out. If you're trying to reduce the number of folks making changes, that means the DBAs are handling changes to the server, right? So you're advocating the DBAs keep up with the security patches, latest security vulnerabilities, keeping up with issues like TCP chimney offload issues, driver and firmware updates, etc.? I mean, that's where that line of thinking logically leads. So considering that is a full time job, in and of itself, when do the DBAs have time to be DBAs?

Posted by 2 Tim 3:16 on 25 November 2009

First off, I have great respect for K. Brian Kelley but must strongly disagree with him here.

A DBA without local Admin rights on a database server is called a "Developer".  

There are simply too many DBA activities that require local admin rights.  ie.  installations, SP's, Hot fixes, monitoring, tuning, configurations, configure "lock Pages" and  Remote MSdtsServer for SSIS, SSAS config, Kill sessions, create jobs in the os scheduler if needed, Cluster Admin (a biggie - how else do you stop/start your cluster services?)...  I can list a lot more.

Unlike a Unix/Linux server, MS SQL Server is very closely integrated with the MS operating system.  In the end, it is the “database” server, built for your database experts to administer.  An os (while it is the foundation of the system) all by itself does not perform a business function.  If something goes wrong, the DBA’s are the first line of support.  The Database is the house that sits on the foundation.  imho.  thanks.

Posted by robertmcook on 25 November 2009

For Windows Server 2008 you are required to be a local administrator to launch Failover Cluster Manager and Server Manager so regardless of debatable reasons Microsoft is forcing choices through requirements to use those tools.

Posted by sjsubscribe on 25 November 2009

I'm glad Brian's thinking along these lines because they can lead to better lives for both DBAs and sysadmins. Both should have enough rights to handle routine situations, such as applying patches, fixes, recovery, etc. Both can help each other out as remote hands when needed, but the actual responsibilities must reside in the appropriate place. DBAs managing their own servers is like a user splitting part of their database between the server and some excel files. With virtualization, clustering, streaming, and many other OS services that databases are expanding into, its best to have relevant expertise instead of making DBA a jack of all trades.

Posted by Jack Corbett on 25 November 2009

I think a key point that s.c. simmons brings out is that you need to have a good working relationship with your sysadmins.  I've been fortunate to have that at both my jobs, so being a local admin, which I am, has not really been necessary.

Posted by K. Brian Kelley on 25 November 2009

See my follow-on here: www.sqlservercentral.com/.../more-on-dbas-and-local-administrator-rights.aspx

And I'm trying to further the discussion here, so none of this is a personal attack. However, it's the types of things that have to be considered from a security perspective.

As far as monitoring and tuning, I cover it in that post.

Lock Pages in Memory is a local security policy configuration. Our server admins handle this because it is in the local security policy and DBAs don't handle the rest of the local security policy... because of what can be done that would be very, very bad from a security configuration side like change who can logon from the network, who can logon locally, enable the guest user, etc. See the issue? Should a DBA have this level of control over the OS? If you were a server admin and you had to let a DBA have this level of control but you bore ultimate responsibility for the security of the server, what would you think? Because ultimately, that's what is being asked of a server administrator.

Kill sessions? That's through SQL Server and doesn't involve the OS.

Jobs in the OS scheduler? Why would you use Task Scheduler when you have a more robust scheduler in SQL Server Agent? You have no alerting in Task Scheduler. You can't tie job steps together. So why Task Scheduler?

Clusters always represent an exception, but speaking of that, if you're going to be doing something on the cluster side, why AREN'T you involving your system administrators? I know in our shop you touch a cluster service and we get a slew of alerts from different monitoring systems. That should be in place if you've got clusters. So if they don't know you're intentionally working on the cluster, they get that alert and assume server down issue and respond accordingly (or at least, they should). So that harkens back to working with the system administrators.

And keep in mind, there's nothing that says from a change control perspective that you temporarily put a DBA in administrators for a particular install. And then take them back out afterwards. Think about it. Do DBAs need local Administrator rights on a day-to-day basis? That should be the criteria used, based on the principle of least privilege.

Posted by Robert Eder on 25 November 2009

In an idealistic world, there should be complete separation of duties to promote a more secure environment.  With anything related to security, the one question to ask to determine is what is acceptable risk.  In your environment, it may be acceptable risk.  It may be acceptable risk for some servers in your environment, but not others.

The better solution is to have the system admin team to work well with the DBA team.  In a previous position, between my team (system admin) and the SQL team, there were many instances that I had to work with the SQL team to resolve a problem.  Sometimes I went to the SQL team and sometime they came to me.  In this environment, the SQL team did have admin rights, but out of professional courtesy, the SQL team would get the system admin team involved.  I had SA rights to SQL, but I would return the same courtesy.  Though working with the SQL team, I learned a great deal about SQL.  I am sure that any member of the SQL team would state the same thing.

In my current postion, I am an "accidental DBA." Without interaction with the SQL team members in my previous position, I am not sure that I would be doing as well as I am doing.

Posted by jetgunner on 25 November 2009

Brian, while I agree that it is a drastic increase in work load for the DBA, you are going on the assumption that the DBAs and Sys Admins have a good relationship and / or time to constantly coordinate their efforts.  

Suppose the sys admin, without notifying the DBA, installs Windows updates that break SQL Server, or upgrades firmware for the fiber cards going to the SAN that degrades performance.  Now, the DBA is probably going to waste time looking at SQL Server first for the problem.

Now, for the real deep down dirty sys admin stuff, I think you would definitely need to pull in a network admin or security admin to give a consult, much like one doctor calls another who has a specialty in that area.  But, like I said before, for the sys admin tasks that get performed 360 out of 365 days of the year, any DBA should be more than proficient at these.

Now, I'm speaking from experience in my shop and I'm sure everyone else is doing the same.  In my shop, the DBAs are part of the development group and the system and network admins are part of either the system group and/or the operations group, and communication is often lacking.  I think the bottom line is that each shop is different and the paradigm for server management needs to be based on the resources available including phycisal resources, staff resources, and knowledge resources.

Posted by Mauricio Morales on 25 November 2009

My respectful opinion is DBAs should have access level of sysadmins, specially in those small and medium companies with few DBAs. A good DBA should have knowledge about Networks, Domains, NTFS, AD and other issues of the sysadmin.

If I wouldn't have access of sysadmin, this wouldn't create a lot of dependency on my sysadmin.

Posted by Silverfox on 25 November 2009

Got to agree with some of the recent comments, it is pretty much down to communication and good will between teams. server/infrastructure and DBA. I have seen major problems with segregation of duties and strongly believe that sql DBA's should be part of the local server admin group. there should be trust between the teams. No DBA worth his/her salt will make non-sql server changes, they will engage infrastructure team, however there needs to be the flexibility to make changes/investidate server problems, clustering in particular. you cannot even use cluster administrator unless you are a local server admin. that was a major sticking point in waiting for wintel resource to become available to handle critical calls for a previous client. the last thing you need in any environment is for resource management to become a bottleneck. how many people have sat around while servers are down while waiting for someone with local server access to join either the call or get on site.

Posted by Anonymous on 25 November 2009

This post was mentioned on Twitter by SQLChicken: RT @kbriankelley: New SSC blog post - Should DBAs have local administrator rights? - http://tinyurl.com/yc48era - Yes, @onpnt, I went there.

Posted by Igor Zaychik on 25 November 2009

I'd like to bring SSAS to the discussion. I had situation then SSAS coexists with DB and SQL engine. I had trouble to develop cubes without local admin rights. BI simply does not work like EM on a local station.

Posted by mcvilbar on 26 November 2009

Actually, it depends on the company that you're working with. Right now, my job and function here as Systems Engineer can be both DBA and System Admin. So therefore, I have both access in our database servers and also to the server itself.

Posted by jnp_swiss on 26 November 2009

My experience is that at times you(as a DBA) spend more time in redtapism of back and forth discussion for getting things done, if you are not local admin. But as long as the specialist windows administators are quickly approachable, DBA can stay away from being admin on the windows box.

Posted by red_serene on 26 November 2009

I believe that Microsoft have a lot to answer for in this arena.  Let me first state that I am in favor of SENIOR DBA's having local admin rights to servers.  Microsoft has touted for years that DBA's do not need local admin rights.  

1)Have you ever called Premier Support for assistance?  Their expectation is that the DBA's have SA to assist them while they are doing troubleshooting at both the Windows and SQL level.

2)Why has Microsoft not come out to describe the steps it takes to give DBA's a lesser degree of permission on Windows which can satisfy the job requirements?

3)Why does the Windows operating system not have a broader scope of those activities which can be viewed - rather than updated?

I don't want to administer Windows, but I want to be able to view settings there so that my discussions with my local system administrators can be from a knowledgeable perspective.  They *are* the experts in Windows and I am the expert in SQL.  In many situations I have had 'rouge' local admins not only install SQL, but create and manage (after a fashion) their own databases - only to come to me when it falls over.  What about that database that sits underneath all of those VMWare instances?  Oh, you built a Sharepoint site on your own?  etc.

As stated previously, communication is the key - but so is education.  Junior Windows Admins and junior DBA's should both be cross-trained after a fashion to learn what they need to know about the other specialty, and have it ingrained into them where the boundaries lie in your particular shop as to where responsibilities lie.  Management agreements can facilitate this.

Posted by mjreeves on 26 November 2009

I agree with K Brian Kelly that SQL DBAs should not have local admin rights.  I think that jnp_swiss wrote that as long as the windows administrators are quickly approachable, then DBAs can stay away from being admin on the windows box.

DBAs do not get the classes the System Administrators get and likewise the Syst Admins do not receive the training and classes that the DBAs receive.  

That said; I trust 'most' DBAs with Admin Rights not to abuse them, but there are some out there that I would NOT trust with Syst Admin rights!

Posted by bgdjwoods on 26 November 2009

For those of us who still prefer to use the Performance Monitoring Tool, If memory serves me right, you need to have Admin rights on the server that you use the monitor against.   If DBA's are not allowed to be the local administrator, this tool is no longer useful.

Posted by Donald Denney on 18 December 2009

I think that one of the key points of contention is the Separation of Duties.

This is an acknowledged and accepted requirement within any environment to keep any one individual from having too much access to data and too much privilege over that data.

The big question is really in the definition of where that separation lies and which duties belong to whom, i.e. who's job is it anyway?

I agree that DBAs can be restricted from server access and admin access it really is a matter of how much of our Job do the windows admins want to do? I've seen shops that basically relegated the DBAs to being nothing more than Application DBAs, i.e. just glorified developers with special database knowledge. Not an operational DBA at all.  

So much of the DBAs job requires access to the server (starting/stopping/managing database services and features, running server side traces, managing disk space. Moving database files and backup files around, etc.) that if the DBAs are not allowed access then they can't adequately perform those job functions. Someone has to perform it though and if the Windows Admins want to do it and are qualified to do it, then they really don't need a highly paid professional DBA to sit there and twiddle his or her thumbs.  

I really blame this on the fact that Windows doesn't allow the DBA to be granted access to the machine and to manage certain services, files, etc. without having to make them a local administrator. I'll use the dirty word here and point out that most other operating systems, for instance Unix, VMS and all mainframe operating systems, do provide that capability. It's the reason you don't see Oracle DBAs and Unix Administrators sitting around having this discussion.

One compromise that I have seen is for the DBA to have unrestricted, non-adminstrative access to the servers, but have a special account that is normally locked but that can be "activated" when needed in a controlled manner. The service accounts don't have to be allowed to interactively logon at all, and for that matter the DBA doesn't have to even know the password to them provided the windows admin is available on those rare occasions that it may need to be entered.  

Posted by Jeff Moden on 29 January 2010

>>Now, realistically this doesn't work, but it's a good argument.

Heh... if it's not realistic, why is it a good argument?

In truth, I have no Windows Server privs on most of the SQL Servers that I work on.  When there's a good Windows Server SysAdmin with the correct attitude, life's a joy.  When there's not, life is a huge and very slow PITA.

So, I'll say it with you... "It Depends". ;-)

Posted by terryj30 on 29 July 2012

I agree DBA should have Local Admin, However, in my org we dont even have access to our production servers unless we elevate our rights.

For security purpose and also client information. DBA's Sysadmins are all locked out of our systems. If a system goes down we can run a command that will add our access to a server and the local admin where by we can then access the servers and do what is needed. This is very hard for a DBA esp when you need to be proactive and monitor servers all we have now become is reactive but sec admin say its for a good cause.

Posted by tedo on 13 January 2013

You might want to also think about privcy and what a DBA can do at server level and Database level. I think these days DBAs need to protect themselves, I think DBAs should no longer have SA rights to server level or database level all the time but to elevate access when they need it on a Change ticket or Incident. However, this would only go for a large or medium size org, Small companies would usually have a DBA / SA doing the same job.

Leave a Comment

Please register or log in to leave a comment.