SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Why I Say Something about Running as Administrator

On a couple of recent webcasts, I pointed out the folks were running with the local Administrator account. To start this out, I'm not a big fan of security by obfuscation. Security by obfuscation (not code obfuscation, but security by obscurity, if you prefer, I'm using the terms obfuscation/obscurity interchangeably in this sense) is where you hide something and hope no one finds it. For instance, you move the port of your web server from port 80 to another port, thinking you're safe. You aren't, as anyone with nmap or another port scanning tool can find your web server. But when it comes to the Administrator account, I prefer to rename it. The reason is fairly simple: you know that scripts, worms, etc., will target the administrator account and by renaming it, you immediately stop those attacks in their tracks. Sure, you'll get the hits in your security event log, but that particular account has no chance of being compromised by such attacks. Yes, the SID is well known, but to identify the right account requires a bit more sophistication in the attack.

Now I must admit, in the cases where I saw the use of Administrator, the presenters were running in a VM with no sensitive data on board. And given that it's not exactly hard to locate the Administrator account, why say anything? It goes back to something I believe Steve Jones said a while back. Basically it amounted to, "Don't tell me not to do something and then do it." If I remember right, he was speaking with regards to presentations. Part of the point is that people will remember what you did. And if you're a parent, you understand that "Do as I say, not as I do," doesn't work so well with kids. I don't think it works very well with anyone.

So the issue is someone who doesn't know better will see the presentation and either not hear or not remember the warning, if one is given. And they see the presenter running as Administrator and maybe the next time they do something, they do so, too. They're in charge of administering a server that doesn't have the administrator account renamed. And they don't think twice about it. Or they've got their own presentation and they repeat the behavior, not seeing it as a big deal because so-and-so did it, so it must not be that important. And a bad practice gets repeated and passed on.

But what about a different account, one that is a member of the Administrators group, just not named Administrator (even the renamed Administrator account itself)? I know there's a lot of folks who say this is a no-no, too. But there are still some key tools (Visual Studio 2005, for instance) that require administrator level privileges. With Vista, UAC gives you some protection, provided you leave it on. Also, with Vista Internet Explorer can (and should) be run with Protected Mode on and with Firefox you can use plug-ins like AdBlock Plus and NoScript to provide additional protection. So I'm not one of those who has as big an issue with that. I still don't like end users with Administrator level privileges, but when it comes to developers, system administrators, and DBAs, a lot of the tools we use just don't give us much of a choice. So it's something we live in and just try to be "smarter tha the average bear (to quote Yogi the Bear).


K. Brian Kelley - Databases, Infrastructure, and Security

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


Posted by Brent Ozar on 29 May 2009

Great point.  To atone for my sins, I'm going to do a Pain of the Week presentation on overlooked security risks - simple things you can do to firm up the security in your environment that don't cost anything and don't make your workday longer.  I'm almost thinking we should just schedule two a year.  It's not sexy, but neither is seeing your company's name in the newspaper when your data's been stolen.

Posted by Steve Jones on 29 May 2009

Thanks for the ref, and yes, that's the point. People copy your code and behavior as a presenter and using Administrator or sa, or BKelley with no password, sends a subtle, bad message.

When things don't work, what does a developer do? He/she will go back to what they saw. They'll make their account an administrator, or (and I've seen this), remove the password to "make it work". That poor choice never gets undone because the expedient thing works, and they see no reason to go chase down why it was needed. It's working now.

Posted by EdVassie on 2 June 2009

Part of this may be laziness - the dministrator account exists and changing it takes time.  

Some of this is likely to be lack of knowlege.  How many DBAs really do know what Windows rights they need to do their job if they do not have local Administrator access(1).  And how many really know what SQL Server rights are needed to do maybe 80% of their tasks without needing Sysadmin access(2).

IMHO, this is something that could usefully be put into BOL.  In the meantime, we have to look at sites like this to find out some of these gems.

(1) You need: 'Access this computer from the network', 'Allow log on locally', 'Allow log on through Terminal Services', 'Bypass traverse checking', 'Force shutdown from a remote system', 'Perform volume maintenance tasks', 'Profile single process', 'Profile system performance', 'Shut down the system'

(2) You need: 'db_datareader' in all databases, 'db_ssisoperator' 'SQLAgentOperatorRole' 'ServerGroupReaderRole' in msdb, 'mdw_reader' in your Management Data Warehouse, and server permissions for 'Alter trace' 'View any database' 'View any definition', 'View server state' (For SQL 2005 substitute 'db_dtsoperator' for 'db_ssisoperator' and leave out 'ServerGroupReaderRole')

You also need to set the Windows DBA group to be System User in SSRS.

Both the above 'simple' lists taken from SQL Server FineBuild Reference manual available at CodePlex

Posted by paul.knibbs on 2 June 2009

It would help if software vendors were on the same page as everyone else. For example, our company makes heavy use of a certain popular payroll application which shall remain nameless. This application requires that the person using it be an administrator. Why? What earthly reason do they have for requiring an application like this to run as administrator?

Posted by AndyD on 3 June 2009

Demos using the sa account seem to be universal, and I feel this helps feed the universal problems we have with security holes. Even major problems like SQL Injection can usually be prevented via decent security (naturally, input validation should also be done, etc).

But demo after demo just ignores this. Even at the recent TechDays 2009, there were Microsoft employees running everything as local admin. I know that the point of their demos was not security, and security would "just get in the way", but but but....

Leave a Comment

Please register or log in to leave a comment.