1

For the setup of the security of the databases we have created a number of nested Active Directory groups:

Group Members Group type
RGP-EA_PROD-RW RGP-SparxEA-RW Domain Local Security
RGP-SparxEA-RW BGP-FunctionalAnalyst Domain Local Security
BGP-FunctionalAnalyst User1 Global Security

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles.

When User1 tries to login he gets an Access Denied error.

User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So User1 is ultimately a member of RGP-EA_PROD-RW.

enter image description here

The SQL Server Logs record two events when the login fails:

Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Error: 18456, Severity: 14, State: 38.

and

Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Login failed for user 'MYDOMAIN\User1'. Reason: Failed to open the explicitly specified database 'AE_PROD'. [CLIENT: 100.74.0.4]

The database is online, and the AD group has been setup to have access to it. It works just fine with the other groups, just not with the group nested at the third level.

I did a little bit of experimenting and found that when setting up logins:

  • Login User1 directly works
  • Login BGP-FunctionalAnalyst works
  • Login RGP-SparxEA-RW works
  • Login RGP-EA_PROD-RW does not work

I'm not sure what is causing this problem:

  • Is there a maximum level of nesting supported by SQL Server?
  • Is there a certain property I should look for in AD? (both Domain Local groups look similar to me)
  • Something else?

This is a completely new Active Directory with only a handful of users and groups.

We are trying to apply the AGDLP guideline in a single AD domain.

  • RGxxx groups are Resource Groups, defined as Local Security Groups. They are used to assign access rights to resources, and they only have other groups as members, no users.
  • BGxxx groups are Business Groups defined as Global Security Groups. They have only users as members and are not used to assign access rights.
3
  • Might be Kerberos token size that grows too large. – vonPryz May 3 at 11:48
  • 1
    Maybe related: dba.stackexchange.com/q/35631/170408 – Doug Deden May 3 at 19:02
  • Have you tried EXEC xp_logininfo 'YOURDOMAIN\User1','all'; as the answer link in the comment by @DougDeden suggested? That should list all the permission paths SQL Server uses. – Dan Guzman May 8 at 12:25
-1

Sorry I don't get it. You write:

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles

Okay. So that means RGP-SparxEA-RW ought to be able to login and do stuff.

User1 is not a member of RGP-EA_PROD-RW so why do you expect for User1 to be able to login?

User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So yeah, User1 is ultimately a member of RGP-EA_PROD-RW.

MYDOMAIN\User1 suggests when you login you the username needs to be prefixed with a real domain. There is a weirdness that the groups that work are all Domain Local Security Groups. Maybe the Global Security Group only exists in Azure - not on the local domain controllers. Next step is to query group membership with VBscript or Powershell and confirm whether the group membership is what you think it is.

Standard practice is to make local groups inherit from global groups. In your case it's the other way around.

6
  • with RGP-SparxEA-RW it works. I have verified the group membership using the tool ADExplorer. This is an on-prem SQL server, no Azure. – Geert Bellekens May 5 at 4:49
  • What happens when you added the group BGP-FunctionalAnalyst user login and give it db_datareader and db_datawriter roles? Um. I recall configuring hierarchical relationships between local groups and global groups has a few gottchas but I don't know what they are. I've always avoided that, Do all your domain controllers have the same data? Or are some restricting access to some attributes and groups? – Emmett May 5 at 4:57
  • @Emmett it works just fine with the other user groups, just not with the one on the third level. Your answer should probably better be a comment asking for more info, than a real answer. I appreciate your effort, but this is not how this site is supposed to work. – Geert Bellekens May 5 at 5:46
  • 1
    @Greet. Oh. I see. Yes. I hadn't noticed the distinction between comments and answers. You may have missed this - I think the inheritance should be from global to local, not the other way around. – Emmett May 5 at 5:55
  • 2
    @Emmett I'm not sure what your standard practice refers to, but we have followed the Microsoft AGDLP guideline, which says to create global groups for users, and local groups for resource access. – Geert Bellekens May 5 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.