Windows AD groups may not be your answer

Be careful when using AD groups to manage your database security.

Managing SQL logins in AD groups is the answer MS has been pushing for a long time now but I'm really not a huge fan overall. I get all the arguments, right? It's easier to manage users cause you don't have to add someone to all the DB boxes every time they're hired. The problem is that you have no visibility into who's in that AD group. You could easily get lots of people in that group who don't belong simply because the AD group in charge of it isn't as diligent as you'd like. A simple case comes from my last job. I had always suspected that our AD group just answered tickets as they came in without really doing any checking into what was being asked. Whenever someone would submit a ticket to me for permissions to the DW, I would always track it down and get approval from someone in the know. And over half of them ended up not needing the access. As it turns out they were just wanting to get in and poke around to see what the DW was all about. So there came a day when we switched domains and we weren't given access to some of our own boxes. So we talked to the AD group and asked them to give us the access. They said they'd be glad to if we turned in a ticket. So my boss told me to put together a list of boxes and perms, which I did. Only I included 3 boxes from the really sensitive OLTP app that we weren't allowed to have acces to. It's not that they didn't trust me personally, it's that they didn't trust anybody not in their direct group. So they guarded the DB access like a hawk. So I requested Windows admin rights on those 3 boxes just to see if they'd give it to me... and they did. When we got the email that the ticket was complete I tried to log into those boxes and got in with no problem. And since they had builtin\admins as SQL admins then I was instantly an sa too. Ya just gotta love idiots when they try to guard security. This is the same thing as locking your door and leaving the key taped to the keyhole. And for a very short time I seriously contemplated causing some minor mischief just to prove a point, but in the end decided that sort of thing isn't looked on very highly so I ended up just taking the problem to my boss. Now, this is already after yrs of me preaching against using AD groups to manage your SQL security in the first place, it just gave me fuel. However, I did get to use this as a jumping point to get our own security audited by the AD group. They finally provided us with a list of those in the groups on our boxes. And between the boxes we managed, there were 239 unauthorized users. I specifically remember that number because I said it so many times in meetings with VPs. The biggest impact was when I was in a meeting with the mgr for that OLTP group. I asked her a specific question about their DB and she said she didn't know that she'd have to look it up. I said that's ok, we can do it here. I'll just login and take a look. She said, nobody has rights. But when she saw me connect w/o any problem her jaw dropped. HOW DID YOU GET ACCESS TO THIS BOX? Oh, I'll tell you, it was actually not easy. I had to turn in a helpdesk ticket and ask for it. So let's just say she was unhappy. But this is why I stayed away from AD groups in most of my gigs. You just can't trust that the AD group is going to do their diligence to make sure requests are verified. And I realize that there are shops that are really good about this, and it's not any slam on the theory of the security model. It's just that it's one of those things that's really hard for companies to practice for real. So if you're considering this type of thing in your company, seriously, make sure your processes can support it. Have someone once a month or so turn in a request for access just to see if they'll verify it. And get regular reports on the users in those groups so you can audit them. Hey, it's not just about CYA. It's about actually protecting the data and the performance of the system.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2010 IDG Communications, Inc.