May 31, 2012 at 8:40 am
Hi there,
We've been trying to find out why one of our users can't log in through Windows Authentication. One of the problems we have run into is that this user is in more than one Active Directory group. Several of these groups have logins in our SQL Server, but each has it's own settings (default db, privileges). The error we've been getting is login failed state 16, i.e. default database not accessible.
What we want to know is, when the user logs in, what group does the SQL Server pick up? What privileges will the user have? Which default database will be checked? Is there any way to see which group the SQL Server has picked up for a login?
This might be easier to discuss using an example:
Windows login: DOMAIN/Danny
This login is the in Active Directory groups DOMAIN/TeamDBMS, DOMAIN/Ap1User and DOMAIN/Ap2User
The groups have the following privileges and settings in the SQL Server:
DOMAIN/TeamDBMS ;
- has the sysadmin server role
- has master as a default database
- master has a user for this login
DOMAIN/Ap1User ;
- has no server roles
- has UserDatabase1 as default database
- UserDatabase1 is an existing and online database with a user for this login
DOMAIN/Ap2User ;
- has the securityadmin server role
- has UserDatabase2 as default database
- UserDatabase2 no longer exists
When Danny (attempts to) log in, what will happen?
From what I've seen I get the feeling that all groups are picked up. Which I think would mean Danny would get the combined privileges (sysadmin and securityadmin), but also that Danny would not be able to log in because the last group's default database does not exist.
If this assumption is correct, is there any way to get information about which group(s) is (/are) causing problems when a login fails?
If this is not correct, what does happen?
Any tips for documentation about this whole mechanism would also be very welcome.
All help is very much appreciated!
Regards,
Debora
May 31, 2012 at 1:10 pm
ddonck (5/31/2012)
Hi there,We've been trying to find out why one of our users can't log in through Windows Authentication. One of the problems we have run into is that this user is in more than one Active Directory group. Several of these groups have logins in our SQL Server, but each has it's own settings (default db, privileges). The error we've been getting is login failed state 16, i.e. default database not accessible.
What we want to know is, when the user logs in, what group does the SQL Server pick up? What privileges will the user have? Which default database will be checked? Is there any way to see which group the SQL Server has picked up for a login?
This might be easier to discuss using an example:
Windows login: DOMAIN/Danny
This login is the in Active Directory groups DOMAIN/TeamDBMS, DOMAIN/Ap1User and DOMAIN/Ap2User
The groups have the following privileges and settings in the SQL Server:
DOMAIN/TeamDBMS ;
- has the sysadmin server role
- has master as a default database
- master has a user for this login
DOMAIN/Ap1User ;
- has no server roles
- has UserDatabase1 as default database
- UserDatabase1 is an existing and online database with a user for this login
DOMAIN/Ap2User ;
- has the securityadmin server role
- has UserDatabase2 as default database
- UserDatabase2 no longer exists
When Danny (attempts to) log in, what will happen?
From what I've seen I get the feeling that all groups are picked up. Which I think would mean Danny would get the combined privileges (sysadmin and securityadmin), but also that Danny would not be able to log in because the last group's default database does not exist.
If this assumption is correct, is there any way to get information about which group(s) is (/are) causing problems when a login fails?
Create UserDatabase2 and have them attempt to login. That will prove that one way or the other.
If this is not correct, what does happen?
Any tips for documentation about this whole mechanism would also be very welcome.
All help is very much appreciated!
Regards,
Debora
What does this return?
EXEC sys.xp_logininfo
@acctname = N'domain\accountname',
@option = 'all';
Where possible it is advisable to arrange your AD groups and SQL Logins such that each person can only enter SQL Server under a single login.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2012 at 6:54 am
Where possible it is advisable to arrange your AD groups and SQL Logins such that each person can only enter SQL Server under a single login.
That sound like shear heaven to me, but sadly I'm stuck with a security design where every application function had it's own Active Directory group, and every application user is in dozens of groups of which there are virtually always more than one with a login in the same SQL Server. :crying:
Anyways, thanks for the xp_logininfo tip.:-) Now at least I can see which groups the user is in, which should drastically simplify the search next time.
I did some testing and found that when logging in the default database of only one group is used. If this database has a problem, you're screwed. If any of the others have a problem, you're safe. It's always the same group, and if that group's login is dropped, it's always the same next group.
Example. Use DOMAIN\Jane is the the following groups that have logins in the SQL Server:
DOMAIN\GROUP_B ; default database Userdb1
DOMAIN\GROUP_1 ; default database Userdb2
DOMAIN\GROUP_$ ; default database Userdb3
GROUP_B is always used, so if Userdb1 is dropped Jane won't be able to log in. If Userdb2 or Userdb3 is dropped Jane can log in normally.
If GROUP_B's login is dropped, GROUP_1 is always used. If GROUP_B's login is then created again, this is used.
So, there is a specific order in the groups. What that order is, is a mystery. It's not alphabetical, not in order of SID, not based on privileges, not by order of create date, not the order of the output of xp_logininfo. My best guess is this order is defined somewhere in Active Directory.
The most annoying thing about this, is that looking at the database at login is the only way I've found to figure out which group is used. So if several groups have the same default database (which often is the case), there doesn't seem to be a way to find out which group is used.
As for privileges, the highest privileges of all groups seems to get used. Though I haven't tested much with this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply