January 1, 2014 at 12:16 pm
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...)
January 2, 2014 at 2:42 am
Additional data:
The error log shows: Error 18456 Severity 14 State 38
According to this site
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?
January 2, 2014 at 6:01 am
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.
January 2, 2014 at 6:07 am
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.
January 2, 2014 at 6:46 am
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
January 2, 2014 at 12:06 pm
@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?
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.
January 2, 2014 at 12:12 pm
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
January 2, 2014 at 12:49 pm
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?
January 2, 2014 at 1:06 pm
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.
January 3, 2014 at 8:09 am
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 ...
January 3, 2014 at 2:18 pm
Atradius (1/3/2014)
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 ...
That does sound odd. I do recall something similar when using Kerberos. Had to force it to use TCPIP vs UDP as packets would occasionally time out. Also had an issue with Groups in the tickets (ticket size issue due to truncation).
And depending on the domain, you may have a delay in replication. Unless you know for sure you are authenticating against the same DC, you could be creating on one controller, but hitting another to auth. They can force a refresh of this.
So if you have time, you might want to test a bit further. AD should not be a seemingly random event.
July 18, 2018 at 11:48 pm
I recently had a similar issue. Trying to fathom the output of sys.login_token (in concert with EXEC AS LOGIN = 'foo') helped. I had one domain group which was working ... but it was part of the 'sysadmin' server role, and was therefore cheating. The other domain group was not working (could not connect to the server at all ... login failed). Not even directly granting the 'connect sql' server-level permission worked.
The problem was that I had renamed the host computer since the SQL Server instance had been installed (in this case, Express edition, as part of an app). And I hadn't yet performed this: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-2017
After renaming (and restarting) the server, the domain group login 'worked as expected'.
Whomever is reading this probably owes me a case of beer.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply