Local Groups or Global Groups?
-
Hi Folks,
I have a question that I have not been able to find an answer to.
Many articles on SQL Server security say that one should put Domain Users into Global Groups and put the Global Groups into Local Groups. Then grant SQL Server roles to the local groups.
Can anyone tell me why this is better (or more secure) than just granting the Global Groups direct access to SQL Server and skipping the local group step??
Thanks in advance for replies!
-
Because you can put multiple Global Groups into a Local Group. This means that you only have to add the Local Group to SQL Server as a login, instead of several Global Groups. This is also true of file and folder permission management.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
This means that you only have to add the Local Group to SQL Server as a login, instead of several Global Groups.Thanks, Brian. But if I plan to grant different levels of security (roles) to the different groups, then that won't really work out right? I mean, I'll have to create multiple local groups anyway. Maybe I'm missing something (which is very likely!) It seems to me that I don't gain anything by using local groups.
-
Another point is in some environments the DBA and the network admin(s) are not
necessarily the same person, and the domain admins are not always given rights
to the SQL Server. This separation of rights into logical groups helps in the
fact that if hackers gain access to one portion of your network security that
it does not immediately open the rest of your network wide open to them. I
typically remove the Domain Admins global group from the local Administrators
group when on a SQL Server set in mixed or Windows authentication mode. Then I
selectively add the domain groups / users that I want to impersonate as "sa".
See :
http://www.sqlservercentral.com/columnists/sjones/sqlserversecurityresources.asp
for more links to some good security articles.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
quote:
I typically remove the Domain Admins global group from the local Administratorsgroup when on a SQL Server
Do you mean the local Administrators group through the OS or do you mean removing BUILTIN\Administrators from within SQL Server?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Tim,
That URL is very good. I read through many of the articles to help fine tune my security policy for our SQL Servers.
And I agree with what you say about separating the network admins from being sa's on SQL Server.
However, I do this without the use of local groups. I just remove the BUILTIN/Administrators login (like Brian suggests) and I add whatever Domain Users or Domain Global groups as a login and grant the SYSADMIN fixed role that I want to have 'sa' access. And I don't need local groups to do this. So I'm still wondering why local groups are recommended? Any thoughts?
Thanks!!
-
quote:
Do you mean the local Administrators group through the OS or do you mean removing BUILTIN\Administrators from within SQL Server?I typically remove the domain admins from the local security group on the local
SQL Server OS and leave the BUILTIN\Administrators in SQL Server, that way I can
add domain users (NO GROUPS) to the local Administrators to grant them admin
access over that machine and SQL Server admin. For other security groups you can
easily use global or local groups, although using local keeps the security
reigns within the DBA's hands. Typically if a hacker is attacking the network,
the hacker will try to elevate their privileges so that they can either glean as
much data / information as they can or leave a worm / trojan horse, for your
computing pleasure. Once the hacker has elevated their privileges, and you can
guarantee he will make himself a domain admin, then he has access to your sql
server as well is the domain admins are members of the local administrators. I
am not saying what I do is foolproof, just another roadblock for them to hurdle.
The longer that you can keep them occupied, the better chance you have of
hopefully discovering their breach.
Groups I typically see at large companies (there are many variations of this...):
NT Admins
Desktop Admins / Support
Exchange Admins
DBMS Admins
Developers
Quite often people from the other groups get added into the "Domain Admins" and
quite often these are people that you do not wish operating your SQL Server from
and administrative standpoint.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Tim, I'm surprised your domain admins allow you to do this. This is definitely not a good security practice. Here's why:
When you remove Domain Admins and then define who you believe should be in the local Administrators group, here's what you do to Domain Admins:
- You disable their ability to check the security event log (meaning they can't audit for suspicious logins, etc.).
- You disable their ability to run scanning software such as BindView, Microsoft Baseline Security Analyzer, and HFNetChk against the server
- You disable their ability to apply critical service packs and hotfixes.
- You disable their ability performance monitor and baseline the server.
- You disable their ability to verify critical services are running.
This is a short list.
As far as worrying about hackers, if I gain domain admin rights, I can quickly reset all the user accounts to the password I have set and I have control of all accounts, including yours.
Also, if a domain admin wanted to push this issue, all he or she would have to do is create a login script that automatically re-adds Domain Admins to the local Administrators group every time you log on.
A common approach is to send out a blanket warning to add 'em back and whatever servers aren't put in this configuration immediately are suddenly dropped from the domain.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Reason I typically do this Brian is that I am a NT Admin as well, have been
administering NT since NT 3.1. You are right on all accounts, I would not
recommend my strategy for the typical DBA, only one that is highly skilled in NT
admin as well. Your rebuttal leads me to believe you have had quite some
experience in the area as well. If it became an issue, an alternative solution
would be to remove the BUILTIN\Administrators from SQL Server security, and use
a different local group to grant administrative rights to the server. But I like
to follow the Titanic approach to security, keeping all of my important areas
compartmentalized. (Of course history bears how well that worked for the boat.)
quote:
You disable their ability to check the security event log (meaning theycan't audit for suspicious logins, etc.).
- Can be automated, and alerts sent out without needing security rights,
typically most admins don't monitor their logs nearly as much as they should.
This usually ends up getting automated anyway.
quote:
You disable their ability to run scanning software such as BindView,Microsoft Baseline Security Analyzer, and HFNetChk against the server
- Nice products, but the MBSA, and HFNetChk can be run from the local system.
quote:
You disable their ability to apply critical service packs andhotfixes.
- Every NT Admin you have ever met is qualified to test the effect of those
hotfixes upon your SQL Server?
quote:
You disable their ability performance monitor and baseline theserver.
- Performance monitoring of a SQL Server usually falls in the duties of a DBA.
quote:
You disable their ability to verify critical services arerunning.
- Packages like Whatsup Gold do not need admin rights to verify services up time.
***All of the things that you mentioned can be alternatively granted through
security rights in the seurity policies or granted via membership in the local
server operators group.***
IMPORTANT NOTE This is the way I do things on my network, and like most
anything I believe there is no ONE RIGHT WAY to accomplish the same task in
the computer industry. IMHO most "Guidelines" are just that - guidelines.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
I've been doing administration for a long time as well. I've worn a system administrator hat, a DBA hat, a developer hat, and hats where my roles were combined. Division of responsibilities isn't just good from a work perspective, it's good from a security perspective as well. When I was a developer, though I had been a DBA, I let the DBAs administer their systems and when the opportunity presented itself, I offered suggestions. The same was true when I was a DBA and dealt with the administering of systems. Now that I'm more back in a system administration role, I offer advice to the DBAs, I administer the SQL Servers that are within my group and contain enterprise information not for normal DBA eyes, but I don't do their jobs for them. If I see an issue, I do alert them, but ultimately that's their responsibility.
The same is true now when I run some IDS and security scanning software. Before I do such actions, I check with our communications group, where that role ultimately resides in my organization. It's my job to ensure the security on my servers, but I need to do so in cooperation with communications.
Event Logs:
Yes, event logs can be automated and alerts sent out. However, part of the responsibility of most system administrators is to ensure their systems are secure. Whether or not they do a good job of that is not the point. What is the point is the access they typically require to do their jobs responsibly. There are time when real time monitoring is required.
Security Scanning
True, MBSA and HFNetChk can run from the local box. But now you've taken an automated scan across multiple systems (with appropriate parsing afterwards) and turned it into one system at a time. Or if they enlist you to do the scan, now you have additional personnel and resources committed that don't need to be. Worse yet, there has to be some way to combine the scan data to get an overall picture.
NT Admin Qualifications
No, not every NT admin is qualified to apply hot fixes to a SQL Server. Then again, not every NT admin is qualified to administer hot fixes on a file server, either. For that matter, not every DBA is qualified to validate whether or not a SQL Server hotfix is applicable for a given environment. This isn't the point. What is the point is that if after I've tested a hot fix or a patch, I'm more likely going to push it across the entire environment and use tools like HFNetChk or UpdateExpert (or SUS since that's Microsoft's push) to deploy hotfixes and service packs (provided I've not seen issues with such a deployment in testing) and then use these same tools to verify that the hot fixes are in place. If I have to go to every group to get them to administer their individual systems, some groups are going to be more lax than others. When it comes to crunch time, these delays could cause unnecessary system compromises.
Case in point: we did find a box that the developers had removed domain admins from the local administrators group. To make matters worse, it wasn't patched and was vulnerable to SQL Slammer. Over a day and a half emails, conversations with their management, etc., happened and no movement occurred on the server in question (converted workstation). They finally got the point when I threatened to revoke it from the domain and communications threatened to disable the port.
Performance Monitoring
I never make this assumption, especially with packages like Compaq Insight Manager, HP Openview, etc. You may be doing your due diligence and baselining your servers, but it is also often the responsibility of the server team to identify potential bottlenecks and resolve them before they start impairing business. Does this always happen? No. Quite often the system administrators are overloaded (as are most DBAs), but it is typically in their roles and responsibilities.
Services
WhatsUp Gold monitors services, but if I remember right, more from a "is the server responding on this port" type of scenario. So services like antivirus software, SQL Agent, and others like it aren't covered.
System Compromise and Unavailability:
Here's a big one. Consider the case where one of your SQL Servers is compromised. It wasn't due to a lack of diligence on your part. Rather, someone found an exploit before the security researchers did and attacked it immediately. To make matters worse, you are unavailable. Which would you prefer: system administrators bringing the system down gracefully allowing your databases to be closed properly or would you rather them just pull the plug?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Back to local groups vs domain groups.
When granting file level security it is better to grant to local groups then add domain groups. This is because re-orgs and other changes occur frequently and re-applying security to a directory tree usually messes up permissions somewhere down the tree path.
But SQL Server does not not have nested security like a file system. In addition, DBAs are often called to move or copy databases from one server to another as systems grow or when QA needs to be refreshed from production. By using global groups, security simply moves with the database. If you used local groups the SIDs would all have to be fixed every time you restored a database to a different server.
-
This is very true. If you are using local groups, look at using sp_change_users_login to get the database users in sync with server logins. This also works if you are using SQL Server logins as the SID is generated by SQL Server and probably won't match from server to server.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Very good points, Tom. I think I'll recommend that our organization continue to use Global Groups as a standard. It just seems easier and there doesn't seem to be any security impact that is negative. Thank you.
And Brian, I agree with your points above about allowing Domain Admins rights to administer the servers. We do the same, except I think we have too many users in that group altogther, but that's another story. [:-)]
Cheers.
Enrique
-
I know very few companies that have a very small domain admins group. This seems to be a perpetual nightmare.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
On the "why use Local group instead of Global groups" issue:
I once attended a local Microsoft seminar on SQL something or other, and the question came up. It wasn't discussed much, but the general impression the MS rep gave was that the "Local group methodology" was devised during the (early?) days of NT 4.0, and various and sundry changes that came along with Windows 2000 obviated whatever those earlier requirements were. I think the subject of Trusted Domains came up briefly as well.
So, it might be a legacy thing--they determined "do it this way", then the code changed, and they haven't gotten around to updating their documentation yet. Does this sound likely, or am I way off base?
(Truth is, based on the discussion to date it looks like another clear-cut case of "It Depends".)
Philip
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply