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:

* HKLM is short for HKEY_LOCAL_MACHINE

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

values:

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

• INFO:

  MSDE Security and Authentication - covers the Registry keys for SQL

  Server (it applies to the full SQL Server engine as well)

• INF:

  How to Impede Windows NT Administrators from Administering a Clustered SQL

  Server - The How-To for Clusters

• FIX:

  Microsoft Search Service May Cause 100% CPU Usage if BUILTIN\Administrators

  Login is Removed - What is needed if you run Full-Text Search (this

  includes for clusters)

• PRB:

  Use BUILTIN\Group to Grant Access to Predefined Windows NT Groups -

  While not exactly germane to the discussion of this article, if you have

  need to grant rights to some of the predefined Windows groups, this article

  discusses how to do so.