If your DBA is still adding Windows logins to your database servers then they are doing it wrong.
Using logins (versus Windows groups) is an inefficient method that hasn’t been considered a favorable practice for almost ten years. Oh…sure…it works. I won’t argue that point. But if you are administering hundreds of database servers and you are using logins instead of groups then you are making your environment more complex than it needs to be. When shops exploded with exponential growth in the past decade Microsoft recognized this by publishing guidelines around security best practices that reflect the preference for using groups. Here’s one from 2005 that states simply at the top:
“Because it is inefficient to maintain user accounts directly, assigning permissions on a user basis should be the exception.”
Exception. As in “not the norm”. And say what you want about SQL 2000, but even they knew that using groups was the way to a simpler life.
Here are three reasons why you should be using Windows groups.
Audit compliance
Since those best practices guidelines were published there has been an ever tightening of a grip on systems with regards to security and audits. Most companies that have to be in compliance with government legislation of some type will prefer to use Windows groups to access there database servers. Why? Three words: separation of duties.
The DBA should not be the person that is adding or removing individuals from groups within Active Directory. Those actions should be handled by a security team, and only after being given an approval by the IT custodian (or manager) responsible for the application (also, not the DBA). In other words, security in your company should have two parts. One part is handled by the DBA who, working with the database developers, creates the necessary roles, schemas, and Windows group logins on the database instance. The other part is the security team who controls the people added (or removed) from the Active Directory group(s) so that the users can access the applications as needed.
That is the separation: the DBA doesn’t control the users in the groups, just the structure the groups are allowed to access. Likewise the security team has no say in how the structure inside the database instance is built, they just fulfill the requests for access after the IT custodian has approved.
Reduce your administrative overhead
Besides the likely compliance issues you will face, if you are adding in Windows logins by hand your actions will not be able to scale to hundreds of instances. I’m certain that someone will leave a comment telling me that Powershell can get the job done with just a few lines of code. This would be where I remind people that automation is a wonderful way to make mistakes faster than ever before.
Let’s consider the case of a developer who needs different levels of access for development, test, QA, and production servers. That would mean you need to keep four copies of your scripts for each environment, and server, and person. That is a lot of scripts to maintain and troubleshoot. I can’t even imagine trying to add or modify those scripts as needed.
Even with just a handful of servers the idea of trying to maintain the correct permissions on a per-user level is more overhead than I would want. Every time a new person joins the company I would need to recreate all the permissions necessary for that person to access the databases they need. And the minute a person gets an ‘access denied’ error message returned it would be in my lap to figure out what was missed.
I can’t imagine why anyone would prefer to spend their time making things more difficult than they need to be. With Windows groups your could have all the permissions, roles, and groups defined in advance and then the security team (remember them?) could add and remove users as needed.
Less mess to clean up afterwards
One area I see customers and clients struggle with centers around the removal of logins after a person has left the company or even changed jobs within the company. When there is no defined removal process the end result is a glut of logins defined on an instance of SQL Server and most DBAs have no idea who should stay or who should go.
When companies have a defined access process that is tied to Windows groups it helps to avoid the issue of having logins lingering around for months and possibly years. We’re talking about one step versus hundreds of steps. Which would you prefer?
If you think not having separation of duties would make an auditor have cause for concern just sit across from the table and look them in the eye and say “those logins have been there for years, we rarely go back to remove them, we wouldn’t know which ones are no longer valid”. Good times.
Exceptions are not the rule
There are always going to be exceptions to using Windows groups. And for those exceptions you will have the extra overhead, you will need a defined cleanup process, and you will likely need to file for an audit exception. But they are exceptions, not the rule. Exceptions always introduce complexity into your environment. Unless you enjoy having an environment more complex than necessary, unless you enjoy having failed audits, you should start using Windows groups as your standard.
If you only have a handful of servers, and your shop doesn’t need to comply with audit guidelines, then you can get away with continuing to use Windows logins versus groups. But at some point you will cross a threshold where the overhead for maintaining your environment outweighs the usefulness. And it is then, at the precise moment, when you will look back and say “crap, I should have listened.”
You’ll thank me then. And you’re welcome.
“This would be where I remind people that automation is a wonderful way to make mistakes faster than ever before. ”
Too true…
I agree with using groups over user accounts whole heartedly. However, I disagree with having a seperate group from the DBA group manage the membership of those groups. As a DBA or application owner it is your responsibility to maintain security and access to your data. I work for the company that caused SOX to be created and have been through enough audits to know that you can’t blame another group. If they ask how a certain account gained access to your data you better have an answer.
You also have to have some delegation. A single organization to manage all AD security groups in a large corporation is not feasible. You have to delegate the management of those groups to the application owners.
I’m a firm a believer that as a DBA you should at least know the basics of any platform your database sits on. If it’s on a cluster then you should know the basics of Windows Clustering and SQL Clustering. If it’s in a domain then you should know the forest architecture and the groups you are using. You should also know the types of groups you are using and how that could affect access to your data. There is a big difference between Global Groups, Domain Local Groups, and Universal Groups.
Ryan,
My experience says otherwise, but my company had more regulations than just SOX, as do many of the companies I work with currently.
The line that would be drawn was at the instance level. Anything inside the instance was our responsibility. Outside the instance (i.e., Active Directory) was for a different group. This reduces your risk because it is less likely that someone can gain inappropriate access through something like social engineering. They would need to talk their way past two groups, and not just one person.
As a DBA I was responsible for over 3,000 databases and the idea of my small group being able to maintain the “who should have access to what specific systems” is just silly. It’s much more efficient to have a security team add/remove users from AD and let the DBAs focus on the groups and role based security inside the instance itself.
That doesn’t mean the DBAs don’t have work to do, but it does mean that at the end of the day we are not the ones with our hands in AD, and we are not the ones approving requests for access.
HTH,
Tom
I’ve worked both sides of the fence (AD and DBA) and I agree that the DBA should not have his hand in AD and the AD guy should not have his hand in SQL. Reading over everything again I make it sound like the DBA should be handling the AD group by adding and removing members himself (not what I meant). When I read your comments it sounds like someone could just ask the AD guy to add them to a group because he manages it (I won’t speculate as to your meaning).
I think what we are missing to articulate here (or at least me) is that there needs to be a process in the middle where a user requests the access, the application owner approves it, the AD guy adds the user to the group, and the DBA already has that group added to the proper roles.
Roles are another good thing where the same principal should be applied. Grant access through roles instead of individual accounts or groups. Of course if you are already using groups like you should be, that becomes much easier to manage.
Ryan,
That is the exact process that I would advocate, thanks for the reply.
Tom
No mention of application roles, interesting.
Mpaine,
My experience is that application roles can be limiting in their usefulness. You could certainly still use Windows groups in order to control access to the application itself, and then use an app role to connect to a database. But you will need to perform some extra steps if the app uses certain features such as linked servers.
Tom
We’ve also been burnt by going down the “grant login for each user” path for all the reasons mentioned: multiple environments, staff coming/going/changing roles. This has gotten far worse over time as the number of databases in our organisation has increased.
So we got wise and decided to rationalise permissions throughout all of our environments. We added groups to Active Directory and granted only those groups access to the databases. What complicated things however was that different people required different levels of access to our Dev/Test/Stg/Trn/Prod environments. So we had to create a different set of groups for each of those environments.
The problem then arose that our database deployment tool had only a single source of information for database permissions. That meant whatever permission scheme we defined during development had to be universally applied to all environments, because any manual changes to the permissions in a database (outside source control) would be undone during the next deployment.
As you can imagine this caused our DBAs much consternation because every time they would add a group in a test environment database, the permission would suddenly disappear each time TeamCity (our Continuous Integration server) did a deploy!
Our solution was to first de-couple users from the permissions by assigning the permissions to specific roles within the database. Then we would use a naming convention for our groups, eg. CRM Administrators (TEST), CRM Users (PROD) etc, and do a post-checkout string replace on the source-controlled user files so that the appropriate group got granted permission to the roles in each database environment. This is far from ideal and is a pain whenever a new group needs to be created, but it works.
Despite all this trouble I still believe deployment automation for databases is the way forward for us, but my current thinking is to find a way to segregate security administration in databases from the deployment of the database itself. This way groups could be added or removed manually in the target environment databases without being impacted by the deployment automation. I would be interested to hear if anyone has a more effective solution to this problem.
We’re also looking at ways to agree on ways to consolidate the roles to reduce this administration burden (right now we’re still in a bit of a reactive mode).
Daniel,
That sounds familiar to me as well. The end result was that a few years ago we found that Kerberos only allowed for people to be members of a certain number of groups in AD (I think about 200 or something, probably based on the length of the string which meant we wanted shorter group names and I think MSFT fixed this eventually).
Anyway, the idea was the same. We had different permissions for people based upon environments and it was a jumbled mess. But it was a lot better than trying to manage permissions for specific user logins by hand.
Tom
So, who creates the groups if the AD people control AD?
This is my dilemma, my AD folks want only groups on the databases, and they want a group created for every every permission level on every database on every server on every environment, and then they want to add AD groups into those groups.
The only problem is that they want all these groups, but they not only control membership but the creation of the groups themselves, and this ends up putting enormous delays into processes which should be taking me moments to do.
If I have a group that currently has execute privileges on a given database and I want to add write privileges, then I am supposed to put in a request for the group to be created, wait for it to be done, then add it to the database and assign the privilege (hoping they put the right folks into the group), all while the customer is pinging how they can’t do their job. Multiply this by hundreds of databases and you have my current world.
Dave,
I used to live in that world, and I believe that is when I started my bacon addiction because adding bacon to anything just makes it better.
Not only does the customer get frustrated at you for something that is not in your control, but when they are added to groups and things still don’t work right it is up to you to track down the issue. At the end of the day you bear the burden of making things work, no matter how far outside the db engine the problem truly lies.
Tom
I agree that having Windows group instead of windows login is an excellent idea, but I’ve run into a problem that I haven’t been able to figure out.
Let’s say that we have a group of ADMINs that have access to a SQL instance based on AD group memebership.
Let’s call that ADMIN_DBA group.
Now, our company has a policy that all Maintenance Plan are to be owned by the SA account. So every now and then when auditing SQl instances, I run this query:
select a.name, a.ownersid, b.name
from msdb.dbo.sysdtspackages90 a
left join master.sys.syslogins b on b.sid = a.ownersid
The problem that I have is when naughty DBA forgets to change the maintenance plan owner over to SA, I can’t tell who it was with the above query, it will just show up b.name as NULL, since they do not have a Windows login stored on the syslogin tables.
Anybody knows a way around this? How can I tell what group membership a given SID has?
Thank you for your help.