Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Domain Group Login Fails Expand / Collapse
Author
Message
Posted Wednesday, January 1, 2014 12:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49
On a SQL Server 2008 R2 (running on Win2003), we have an issue with logins via domain groups - login access is granted to the server instance, but cannot access certain databases on the server.

The SQL server machine is a member of our AD domain, but is not a domain controller. We are using Windows Authentication on the SQL Server.

We create SQL Server logins for domain groups. We set the user mappings to a database XXX on that server, we enable the roles datareader, datawriter and ddladmin. What happens then is - the domain users that are members of those groups can connect to the SQL Server, but not to this database XXX. If user "user1" tries to connect he gets the error "Cannot open database 'XXX' requested by the login. The login failed. Login failed for user 'domain\user1'."

If I manually add 'domain\user1' to the database XXX and enable datareader, datawriter roles, then user1 has no problems to connect.

Curiously, user1 can connect to 2 other databases on that same server - also here via an AD domain group, but a different group created earlier, and the user is NOT set up individually as user on those databases. Those databases are both copied over from an older SQL Server 2005 installation via detach-attach and were working fine on SQL-2005 and also work fine now on that SQL-2008 R2 server. (They show the same user account as owner in the DB properties as the problematic database XXX.)

Database XXX is a new database created on that R2 server and the AD domain group is also new.

I can add the users manually as workaround for now, but this will not work forever, and I need a hint as what goes wrong here. Additional data - SQL Server service runs on a domain admin account.

I found an article that describes this exact same problem for SQL server 2005: https://connect.microsoft.com/SQLServer/feedback/details/248615/login-fails-when-user-is-granted-access-via-a-domain-group But that's from 2006, contains no clue as to a solution, and one should assume it has been fixed.

I have queried via SELECT * FROM sys.database_principals, it does show the domain group, but otherwise this record looks exactly the same in the non-working database as the record for the same query on the 2 working databases (with the old login group).

I also know that for SIDs to be updated regarding the membership in a new group, the user has to logoff and log back in. I have done this, and also restarted both the server and the clients and it just does not work. The domain group is a universal security group. It works fine as a group (I can share folders using this domain group and the permissions are exactly what is expected).
Is this a bug in SQL Server as it was in SQL 2005 apparently? (even though my old and working databases are both SQL 2005...)
Post #1526936
Posted Thursday, January 2, 2014 2:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49
Additional data:

The error log shows: Error 18456 Severity 14 State 38

According to this site
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

error state 38 indicates:

"The database specified in the connection string, or selected in the Options > Connection Properties tab of the SSMS connection dialog, is no longer valid or online (it might be set to AutoClose or the user may simply not have permission)"

This is not the case in my setup. Database is valid, is online, permissions are set to the group.
Any data anyone?
Post #1527013
Posted Thursday, January 2, 2014 6:01 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 675, Visits: 6,814
Is it possible that the db was moved to this server?
Or you have had some AD domain changes / consolidation?
If the security was added under a different domain, the Group that you see is not the SID you think it is.
This would be similar to moving a db between servers, except there local logins are being affected.

If you can easily remove, then reapply the security, this would be an easy test.
Just be careful if you have other security applied based on this group.
Post #1527070
Posted Thursday, January 2, 2014 6:07 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 675, Visits: 6,814
Re-reading your description, it sounds like the older db's work, so you should try to compare the SID for that group to the SID for the newer group.
Post #1527074
Posted Thursday, January 2, 2014 6:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 433, Visits: 992
What happens when you try

setuser 'domain\member of group'
go
use xxxx
go
setuser
go

execute as login = 'domain\member of group';
go
use xxxx
go
revert
go



Post #1527092
Posted Thursday, January 2, 2014 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49
@GregEdwards - there has been no movement of this database nor any change in AD. The database in question was created on that server. The older databases did come from an earlier SQL2005 installation, but they do work.

What would I have to look for when comparing the SIDs? As mentioned, the SID seems fine as I can share folders within with proper behaviour for the group. Seems rather an SQL Server issue, isn't it?

@schleep:

When executing your commands with 'domain\user', they get executed without error.

If I execute the same commands with 'domain\MySecurityGroup', then I get the following error:
Msg 15157, Level 16, State 1, Line 1
Setuser failed because of one of the following reasons: the database principal 'domain\MySecurityGroup' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

I guess that's essentially the same error.
Post #1527251
Posted Thursday, January 2, 2014 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 12,927, Visits: 32,327
that error is normal, because you cannot execute as the context of a windows group, only as a windows user, who happens to be in that group.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1527255
Posted Thursday, January 2, 2014 12:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 433, Visits: 992
OK... Does it work for all the users that are members of that group?
If it does, then it's not access to database xxxxx that's the problem.

Does the sproc perhaps try to connect to another server having a DB of the same name?




Post #1527259
Posted Thursday, January 2, 2014 1:06 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 675, Visits: 6,814
Atradius (1/2/2014)
@GregEdwards - there has been no movement of this database nor any change in AD. The database in question was created on that server. The older databases did come from an earlier SQL2005 installation, but they do work.

What would I have to look for when comparing the SIDs? As mentioned, the SID seems fine as I can share folders within with proper behaviour for the group. Seems rather an SQL Server issue, isn't it?


In the db that works, and the one that does not -
go to views, system views, and look at the records in sys.login_token
see if the SID's match for the Windows Group in question.
If they do not match, my guess is that what is in msdb will match what's in the db's that work, not the one that does not.

Post #1527263
Posted Friday, January 3, 2014 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49
I checked the sys.login_token view but it won't help me because the Windows group in the working database is a different one than the group in the non-working one.

I managed to "solve" in the following way:

Created 4 new security groups in ActiveDirectory. Assign all members to each group.
Log off all client users, restart their computers, and restart the server for good measure.
Add the 4 groups to my database as user, one by one.
The first 3 groups did NOT work. The 4th group DID work.
Removed all non-working group users from my database.

So now I can run it with the 4th group I created ... It works but it is awkward that I need to create 4 groups before it works. I guess that's hard to answer unless Microsoft might be able to track this down. Sounds like there is some sort of internal Monte-Carlo system built in which randomly selects operational security groups ...
Post #1527559
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse