April 7, 2011 at 11:27 am
On one of our servers (2005) we have domain windows admins who have been access to the server and mapped into Local Administrators. Of course, this means that they have sysadmin rights through builtin\administrators.
We actually want these domain admins to be administrator on the box, but I do NOT want them as sysadmin.
Now I was just reading an article about how you can completely remove the builtin\administrator from sql server and drop the login. But that makes me nervous.
Is there a better way? Again, my goal is to have these domain admins as local administrator on the server but with little or no access to sql server.
Thx in advance...
April 7, 2011 at 11:33 am
If builtin\Admins is a login and sysadmin, all local and domain admins are sysadmin. Remove it, just make sure you have another administrative login first.
If it makes you feel better, SQL 2008 no longer has builtin\Admins as a default login at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2011 at 11:53 am
Keep in mind that while you can remove BUILTIN\Administrators from the sysadmin role, this is just low hanging fruit. NT AUTHORITY\SYSTEM is still present with sysadmin rights (the VSS writer connects in as that) and an administrator on the box always has options of stopping the SQL Server service and restarting it temporarily in single-user mode, thereby gaining sysadmin rights while it's running in that single user mode.
So if you are depending on this to be a satisfactory control, it is too easily bypassed. Hence the reason we say audit, audit, audit. Also, if you're running on Windows Server 2003, it's trivial for an administrator to dump the SQL Server service account password in plaintext. And there's not anything you can do about that except go to Windows Server 2008/2008R2.
K. Brian Kelley
@kbriankelley
April 7, 2011 at 12:02 pm
Wow! Thx for the summary. I didn't how "bad" it was.
What makes me nervous is that we are planning to migrate all of these servers into a cluster. And the description of what to do for the cluster was somewhat confusing.
What did you mean by "audit, audit, audit"? Do you just mean make sure that you know who can get to Builtin\Administrator? Or do you mean audit through profiler-type tools?
If you meant the latter, I'd love to know if there is a good article/link on the subject that you prefer.
April 7, 2011 at 12:03 pm
GilaMonster (4/7/2011)
If builtin\Admins is a login and sysadmin, all local and domain admins are sysadmin. Remove it, just make sure you have another administrative login first.If it makes you feel better, SQL 2008 no longer has builtin\Admins as a default login at all.
Thx. See response above.
April 7, 2011 at 12:18 pm
Whisper9999 (4/7/2011)
What makes me nervous is that we are planning to migrate all of these servers into a cluster. And the description of what to do for the cluster was somewhat confusing.What did you mean by "audit, audit, audit"? Do you just mean make sure that you know who can get to Builtin\Administrator? Or do you mean audit through profiler-type tools?
If you meant the latter, I'd love to know if there is a good article/link on the subject that you prefer.
Cluster isn't much different. The cluster service account needs rights into SQL Server, but just the public role. So that's really not a door into the system. By Audit I mean track logins to your SQL Server moreso than anything else. Also, if you have automated monitoring systems, monitor for the SQL Server service going up and down.
For instance, on our SOX critical servers, we actually monitor for interactive logins to those servers by monitoring the Windows security event log. If they log on in such a manner, they have to be able to explain why.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply