July 24, 2009 at 11:59 am
I was wondering if anyone has completely removed all the Windows AD groups from their Production SQL Servers.
Why I am considering this is to prevent the network services folks from having full access to the SQL servers. It is too easy to add themselves to a windows group and gain access to the server.
I also have this concern: If you do not have windows groups can you restore the master database?
July 24, 2009 at 12:12 pm
You certainly can remove them all, but I don't know that you should. You can manage your SQL Server so that you just use SQL Logins or just use individuals AD accounts, but then you have to be part of the entire process for hiring, promotions, job changes, etc... Most places have AD groups for departments and by default manage them as there are changes in the department. By using the AD Groups in SQL Server to manage access then you are removed from the equation.
If you have that much concern about your Network Adminstrators then there is a bigger problem. Also you will need to make sure you are creating the service accounts for your SQL Servers as well, since if the Admins know that password they will have access.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2009 at 12:27 pm
So if I remove sysadmin rights from the DBA user’s windows accounts and create SQL logins with sysadmin rights that should help prevent network services from having full access.
I am trying to develop a good plan that will satisfy the SOX auditors.
July 24, 2009 at 12:53 pm
Let me see if I understand the problem. You have an AD group, let's call it ProdDBAs, which has sysadmin rights on your SQL Servers and the concern is that your AD admin will put themself in the group?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 24, 2009 at 1:07 pm
Lee,
in short, you can't stop the system administrators. If I have admin rights to your server, I will get in to your SQL Server. So you've got to trust them. The best thing to do is to restrict the rights to the appropriate Windows groups. You don't want to use SQL Server logins if you can help it. That opens up a bigger can of worms. Imagine the case where you have DBA go rogue. Once the org finds out, they can disable his/her AD account. That immediately stops all access if all access is based upon that account. If you are creating SQL Server logins, now you've got to disable in two places. You see the issue, I hope.
With respect to the how they can get in...
1) They can stop the SQL Server service and copy off the database files. Nothing you do inside of SQL Server stops this. SQL Server 2008 Enterprise Edition has TDE, but you don't have that in 2005.
2) They can log on as the SQL Server service account. This is one Windows account that must have access to SQL Server. Even if you make it system, they can run a process as System using Task Scheduler and they are in. And if you use a Windows based account such as a local login or a domain login, unless your SQL Server is running on Windows Server 2008 they can use Cain and dump the password for that service account into clear text.
3) If it's SQL Server 2005 or 2008, they can simply stop SQL Server and start it up in single user mode. When SQL Server is started in this fashion, any member of the local administrators group of the operating system will automatically be treated as a sysadmin level login. This is designed as a back door in case you lock yourself out of your SQL Server. A lot of folks did in SQL Server 2000 and below. Hence the reason it exists. Otherwise, you'd be looking at doing a rebuilt to get back in. And that loses everything that was in master (like all the logins).
So you can't stop them. Restrict the permissions to the appropriate security groups and then turn on proper auditing. If it's that big of an issue, turn on auditing for successful logins, too. Parse that daily, and report on it. If you turn that on, the information will be written to both the Application event log on the operating system as well as the SQL Server error log. If that's going to a third party and they see Joe Schmoe logging onto a server when Joe Schmoe isn't supposed to... then Joe Schmoe gets busted.
K. Brian Kelley
@kbriankelley
July 24, 2009 at 1:51 pm
I am not concerned about the network services people that much. I just need to be able to say this is what I have in place to restrict unauthorized access to the sql server. I have the normal user population restricted but I have nothing to say about network services restriction. I hate to say they can do whatever they like!
The first question the SOX auditors ask is "Is the builtin\Administrators group active?" - to give you an idea of what I am facing.
July 24, 2009 at 2:02 pm
You should look at removing BUILTIN\Administrators. That's a given, because it means administrators have to jump through the other hoops to get access. You should not, however, look at removing all Windows groups. There should be controls in place at the AD level to prevent arbitrary group membership changes or those should be tracked. That's one of those areas that involves separation of duties for a reason (to speak auditor speak). Therefore, you should be using Windows groups because that also means there is a single system for authentication (Active Directory). And that permits the "gotta disable access and now" situations to actually be doable.
I can tell you that many auditors still do not realize that you can't completely block access. They forget one of those rules that says "If I have administrative access to your box, it's not your box anymore." I did a recent 4 hour class for my local ISACA chapter (SC Midlands ISACA[/url]) and this was very clear. So much so that we're going to do a few follow-on classes over the next year or so. If your auditors have questions as to what is doable and what is reasonable with respect to SQL Server, they can feel free to contact me through you. I'm carrying a CISA and I'm bound to the same Professional Code of Ethics they are. Send me a PM and I'll reply with my email.
K. Brian Kelley
@kbriankelley
July 27, 2009 at 6:48 am
Brian,
Thanks for the help!
Lee
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply