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

SQL Server Security: Security Admins

By Brian Kelley,

Every so often I'll see a question in the forums about restricting system administrator access to SQL Server. The topic specifically revolves around the BUILTIN\Administrators login, a Windows group that corresponds to the local Administrators group for that server. By default, the BUILTIN\Administrators login is added to SQL Server during the install and made a member of the sysadmin fixed server role. That means the administrators on the system have the ability to do whatever they want to within SQL Server.

Needless to say, this gives some DBAs fits (myself included), especially if there is sensitive data, such as payroll information or customer credit card numbers, within the SQL Server in question. The DBA would like nothing more than to ensure only a few personnel have the ability to access the system in question and even fewer still with complete control over SQL Server. However, there are a few critical "gotchas" with removing the BUILTIN\Administrators group, especially if you have SQL Server installed in a clustered environment or are using Full Text Search capabilities. Since most of the technical difficulties can be overcome with prior planning, much of the discussion focuses on the pros and cons of removing access from the administrators, so I'll start there.

The Rationale for Removing BUILTIN\Administrators

In security there is the principle of least privilege, one I'll speak on a lot in these columns. This principle is simple: give people the rights to do their jobs and nothing more. If I'm talking about a payroll system, the average system administrator has no reason to be able to query the SQL Server and find out the salary of her co-workers. Nor does the system administrator have any reason to download the credit card numbers from the customer database on one of his servers. System administrators with such rights violate the principle of least privilege.

The Rationale for Keeping BUILTIN\Administrators

First there's the concept of trust. System administrators are entrusted with a lot of power. After all, what's to stop an administrator with control over the mail server from sending a message to all employees as the CEO? What's to stop the system administrator from deleting a bunch of critical research documents? What's to stop an administrator from installing a keystroke logger on the head of HR's workstation? The answer to all of those questions is one word: trust. Just as the DBAs are entrusted with the data, so the system administrators are entrusted with the servers and the infrastructure. These are positions where you must hire trustworthy individuals.

Let's not stop there. In the real world there is the question of "What good does it do?" and it's a very valid question when it comes to SQL Server and the BUILTIN\Administrators login. After all, there is nothing stopping an administrator from resetting the password on a database administrator's account and then logging on as the DBA. Also, there's nothing to stop an administrator from simply stopping the SQL Server service and copying off the database files (though EFS may slow the admin down a bit). Similarly, there's nothing stopping the admin from grabbing a backup tape and, you guessed it, grabbing the data from it. Finally, if the DBAs are all contained in a group and that's how the DBAs gain access to their SQL Servers, there's nothing to stop an administrator from adding himself or herself to the group just long enough to get the data and them remove the group membership when the dirty deed is done. There's a lot the savvy system administrator can do to gain access.

Audit, Audit, Audit and Check those Logs!

Tom Clancy wrote a bestseller in the late 80's entitled Cardinal of the Kremlin which presented the scenario of a US spy buried deep within the Kremlin itself. Recent events within the US's Federal Bureau of Investigation has shown that foreign spies had penetrated to the highest levels of the organization. What does this prove? It proves that sometimes people you think you can trust turn out to be ones you can't. We could quickly devolve into saying, "Trust no one," and put up draconian measures to ensure our systems are secure. However, this will severely affect productivity and users will scream (and rightfully so). This puts every organization in a difficult situation: how does it allow its system administrators to do their jobs without putting the company unnecessarily at risk?

The key to bridge the gap is to audit well and have someone review those audit logs. In SQL Server this is a little harder to do since that means parsing log files and setting up traces or running Profiler interactively (keep in mind you're not just looking for logon/logoff). While difficult, it can be done (and I'll cover how in a later article). However, you can keep all the audit logs in the world but they do little good if no one is reviewing them. And here's where a lot of shops run into a big problem: who has the time? When it comes to security someone is going to have to "make the time." Remember, it's not just a savvy system administrator who may make off with the data. As hard as it is to think, it could be another DBA. Or it could just be a weakness in the system that's tripped upon accidentally. Therefore, proper auditing and proper review of that audit data is part of any successful security policy. Keep in mind that this auditing isn't just on the SQL Server itself. After all, a SQL Server trace isn't going to catch an administrator resetting a DBA's password and using it. Auditing must be a pervasive part of all areas of your infrastructure.

The Meat of the Matter: The Technical Details

You've carefully weighed all the pros and cons with removing BUILTIN\Administrators and you've finally decided that in keeping with the principle of least privilege, they have to go. What are the technical details? What can I do to try and recover if I make a mistake? What are the issues I need to be aware of when removing this group? First let's talk about recovery.

The DBA's backdoor: The SA account

The sa account is a known account. It exists on every SQL Server 2000 installation and it is always a sysadmin over the entire SQL Server. Therefore, if you know the password, you can always use the sa account (with one potential key exception: clusters). Yes, even if your SQL Server is set to use Windows (NT) Authentication, this is your magical key into the system. Changing a SQL Server to mixed mode is as simple as altering a registry key and then stopping and restarting the respective SQL Server service. So the very first thing to do is make sure you know the sa account password. If you don't know it (such as you only use Windows authentication), set it. The easiest way is with sp_password and a sysadmin level account, like so:

' A member of the sysadmin role doesn't need the old password
EXEC sp_password @loginame = 'sa', @new = '<your password here>'

If you remove BUILTIN\Administrators and suddenly realize you can't get into the system, the sa account is what you can use to add that account back in immediately. If your SQL Server isn't in Mixed Mode (best practice says not to unless you have no other choice), then you're going to have to edit the registry. You'll be looking for the following value:

Instance Type Registry Value
Default HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\LoginMode
Named HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\LoginMode

* HKLM is short for HKEY_LOCAL_MACHINE

To change the mode, change the data. You should have one of two values:

Mode Data Value
Windows Authentication 0x00000001 (1)
Mixed Mode 0x00000002 (2)

Changing to Mixed Mode is as easy as changing the value to 2 and stopping and restarting the SQL Server instance.

Note: If changing the authentication mode is this easy for you, the DBA, it'll be this easy for anyone else, too. This is why, even if you've set your SQL Server to Windows Authentication, that you should set a strong password for the sa account!

Copy the Master Database and Prepare Your Backups

Security settings such as who has logon rights and who is a member of what server role is contained in the master database, specifically in the sysxlogins table. Before making any changes, stop the SQL Server service and copy all files (.mdf, .ndf, .ldf) related to the master database to a protected location. Remember the original locations for the files. If something goes wrong, a possible roll-back is to simply replace the master database files. Alternately (and in addition, I'd say), take a backup of the master, model, and msdb databases and set them aside. If all else fails, you could potentially run rebuildm.exe, which will put in place generic system databases, and then restore over the top of them.  

Dealing with Clustered Instances: Proceed with Great Caution

Clustered instances of SQL Server are very delicate creatures. If you mistakenly remove the BUILTIN\Administrators account without taking the proper prior steps, you may be left with a SQL Server instance that refuses to come up. The "backdoor" I gave with the sa account won't matter because in order to log on to SQL Server, it must be running. Therefore, if you are going to remove the BUILTIN\Administrators group from a SQL Server cluster, do so with great caution. I cannot stress this enough. The last thing you want to have to do is rebuild a SQL Server clustered instance from scratch.

The first thing you'll need to do is find out what account the cluster service is running under. This account periodically checks to see if the SQL Server instance is alive. Though this doesn't seem like a whole lot, Microsoft says this account is going to need special privileges (sysadmin). The easiest way to check what account the cluster service is running under is to bring up Computer Management (Start | Run | CompMgmt.msc or right-click on My Computer and choose Manage), manage one of the nodes of the cluster, and then expand Services and Applications | Services and see what account the Cluster Service is configured to Log On As. Just to be safe (or if you don't have the rights), verify with your system administrator who handles the cluster as well. The account that runs the cluster is the one you want to give rights to.

The account in question needs a login to SQL Server and it needs sysadmin rights, as per Microsoft's documentation. Two short commands are required to make this happen if you're doing it via T-SQL. Otherwise, create the login normally using Enterprise Manager and grant it the appropriate server role. Here's the T-SQL:

'Grant the cluster account the ability to log on
EXEC sp_grantlogin [<Domain>\<Account>]
' Make it a member of the sysadmin role
EXEC sp_addsrvrolemember [<Domain>\<Account>], 'sysadmin'

Of course, if your system administrator knows the cluster account password, he or she can always use that account to come in to SQL Server with full rights. The Microsoft Knowledge Base article I cite in Additional Resources uses the word "impede" to describe the process of removing BUILTIN\Administrators for this reason. Of course, only a chosen few should know said password and that should assist greatly in narrowing down whodunit if ever an issue arises. 

Now, if you have Full Text installed as a clustered resource (even if you aren't using it), you have one more step before removing the BUILTIN\Administrators account. You'll need to follow the steps in the next section.

Full Text Search: It Needs LocalSystem

The Full Text Search service is also known as Microsoft Search if you're looking for it under your list of services. Microsoft has made it quite clear that the only way the Full Text Search service is supported is if it is running under LocalSystem. Whether or not you're running it on a clustered instance of SQL Server makes no difference. The Knowledge Base article I've cited as an additional resource only talks about a bug where the Full Text Search service would cause the CPU to go to 100% if it wasn't configured to run under LocalSystem. Microsoft states that only LocalSystem is supported. But they've fixed the bug so no big deal, right?

Not exactly. While LocalSystem is a pretty heavy set of privileges to give to a service which is just supposed to do Full Text Search, Microsoft hasn't left any choice here. Part of the problem is that Microsoft hasn't said why only LocalSystem is supported and it's not likely that they will do so any time soon. If you want support you have to leave the service alone and let it run under LocalSystem. If you change the service account, there's really no information available as to the consequences.

With that said, the LocalSystem account may be a little tricky to add if you've never seen how to properly refer to that account. It's not hard, but it is a bit unusual. Also, the LocalSystem account will need syadmin rights. Here's the T-SQL to do it:

' The LocalSystem is known as [NT Authority\System] for SQL Server
' and it needs logon rights
EXEC sp_grantlogin [NT Authority\System] 
' Make it a member of the sysadmin role
EXEC sp_addsrvrolemember [NT Authority\System] , 'sysadmin'

The SQL Server and SQL Server Agent Service Accounts

Last but certainly not least, take care of the SQL Server and SQL Server Agent service accounts. Explicitly grant these accounts the right to logon to SQL Server and add them to the sysadmin role. I do this as a general practice even if I don't remove BUILTIN\Administrators. The last thing I want is for a SQL Server to come down because someone modified a group and suddenly my service account loses the access it needs. This can be a tremendous problem to troubleshoot.

Concluding Thoughts

If we follow the principle of least privilege, the BUILTIN\Administrators login has to go. However, before I advise anyone to do this, I also advise them to be fully aware of the predecessor steps and the work-arounds available to the system administrators. There's nothing like breaking a SQL Server or promising management one is secure only for a system administrator to raid it.

If you go forward with removing BUILTIN\Administrators, prepare to do a little research first. Understanding the configuration is an absolute must. Know the service accounts. Know what pieces and parts you have installed. And review the documentation available. I've cited three relevant Knowledge Base articles from Microsoft that discuss the topics I've covered. Also check in Microsoft's Knowledge Base before proceeding in case they have a new article out covering a discovered bug or the like. Then test the change in a non-production environment, if at all possible. Only once you are comfortable with the steps and understand the consequences in your environment, proceed forward.

As always, if you have questions, feel free to ask them in the forums. There are quite a few DBAs who regularly participate who have tackled removing BUILTIN\Administrators and they've done so in just about every environment imaginable. Also, if you have comments or suggestions on this article or if you've spotted an error, please use the Your Opinion tab.  

Additional Resources

 © 2003 by K. Brian Kelley. http://www.truthsolutions.com/
 Author of Start to Finish Guide to SQL Server Performance Monitoring.

Total article views: 20580 | Views in the last 30 days: 10
 
Related Articles
FORUM

What's the different between sql server accounts NT AUTHORITY\SYSTEM and BUILTIN\Administrators?

What's the different between sql server accounts NT AUTHORITY\SYSTEM and BUILTIN\Administrators?

ARTICLE

Configuring Service Account Privileges for SQL Server

How to grant Windows privileges to the SQL Server's service account.

FORUM

SQL Service Account

SQL Service Account

FORUM

local system account and domain account

local system account in SQL Server 2000

FORUM

service account

Service account

Tags
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones