For the setup of the security of the databases we have created a number of nested Active Directory groups:
|RGP-EA_PROD-RW||RGP-SparxEA-RW||Domain Local Security|
|RGP-SparxEA-RW||BGP-FunctionalAnalyst||Domain Local Security|
On my database I have added the group RGP-EA_PROD-RW as a user login and given it
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.
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.
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.