Sql-server – Nested AD group access not working when nested more than 2 levels deep

active-directoryauthenticationsql serversql-server-2019

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.

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:

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.

Best Answer

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.

Related Solutions

SQL Server 2008 R2 – User/Schema Creation When Windows User Creates Tables

This has always happened, back to SQL Server 2000.
Without schema, how does SQL Server know you want to put it in the dbo schema?

The only way to specify a default schema is to :

use SQL logins (not Windows)
run as "sysadmin"

Neither of these is acceptable

Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.

Also, deliberate schema use is better for SQL Server 2005:

tables in Data
other tables in Archive, Staging etc
code in schemas per client permissions: Desktop, WebGUI etc

Using the dbo schema is so last millenium :-) Links:

Sql-server – How to obtain SIDs of Sql Server installed NT Service Accounts

From the WMI documentation, I couldn't find any way to directly obtain a service SID, probably because as far as I've seen, service SIDs aren't physical directory objects, and thus couldn't be queried except where they appear as part of ACLs. This would also explain why the values aren't persisted in the registry.

Having said that, the service SIDs definitely do not appear in Win32_SystemAccount. Using PowerShell on a sample machine, you can verify this by running Get-WmiObject -Query "SELECT * FROM Win32_SystemAccount".

Luckily, however, there are alternatives:

Per this TechNet blog post, the non-well-known portion of a service SID is generated by taking a SHA1 hash of the uppercase service name, and then splitting apart the results into five unsigned 4-byte numbers. The following code performs this computation and returns the SID as displayed in Windows:

DECLARE @serviceName nvarchar(128) = 'MSSQL$SQL2008R2DEV';

SELECT
    'S-1-5-80' +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 17, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 13, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 9, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 5, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 1, 4), 2)))
    FROM
    (
        SELECT
            CONVERT(varbinary(20), REVERSE(HASHBYTES('SHA1', UPPER(@serviceName)))) AS BinSid
    ) a;

The binary SID can also be obtained by using the SUSER_SID function (with some extra processing to handle the fixed portion) in lieu of HASHBYTES. (Note that the raw binary SID must have its endianness reversed before conversion and output. Also, the @serviceName variable being nvarchar is important.)

If you have .NET available, doing the hash calculation is trivial. Here's the C# code to do it:

using System.Security.Cryptography;

...

string serviceName = "MSSQL$SQL2008R2DEV";

HashAlgorithm ha = HashAlgorithm.Create("SHA1");
byte[] hash = ha.ComputeHash(serviceName.ToUpper().SelectMany(c => BitConverter.GetBytes(c)).ToArray());

string sid = "S-1-5-80";

for (int i = 0; i < 5; i++)
    sid += "-" + BitConverter.ToUInt32(hash, i * 4);

I suspect the SIDs could be found using classes in the .NET System.DirectoryServices namespace, either by inspecting group membership (fragile), or by some other method. Unfortunately, the methods I tested to directly translate a principal name into a SID failed, so I'm not sure how SQL Server does it (PInvoke?). In any event, computing the hash isn't a big deal if .NET is available (in fairness, the code will be longer without the use of LINQ).
Capture the relevant part of the output of the sc utility as used below, or run this manually if you don't care about being able to automate the solution:
```
sc showsid MSSQL$SQL2008R2DEV
```

Best Answer

Related Solutions

SQL Server 2008 R2 – User/Schema Creation When Windows User Creates Tables

Sql-server – How to obtain SIDs of Sql Server installed NT Service Accounts

Related Question