October 28, 2020 at 2:53 pm
Our MS SQL Server is on another trusted domain (Trusted Domain T). We created a Universal Group within Domain A with users from different forest domains and added the AD group onto the SQL Server. When the user from Child Domain B tries to connect, they get the error: "Login failed for user".
If I add the user directly instead of the AD Group in SQL Server, they can login without problems. Furthermore, if I create a AD group in Child Domain B, include all the users from that domain and add it to SQL Server, they have access.
Domain Forrest
Domain A
- Child Domain B (Child Transitive - Relationship)
- Child Domain C (Child Transitive - Relationship)
Trusted Domain T (External - Non Transitive Relationship)
I'm on SQL Server 2016 (13.0.4001.0)
SQL Server error logs displays the error for that particular user:
Reason: SQL Server error logs displays the error for that particular user: Reason: Could not find a login matching the name provided.
We created a new AD Group within Child Domain B and added the users from that domain and added the group to SQL Server and it worked. So the problem is domain cross referencing in SQL Server.
October 29, 2020 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 2, 2020 at 11:26 am
Interesting scenario! Sounds like a Microsoft exam question 🙂
My first thought would be to use a Domain Local Group in the Trusted Domain, containing the Universal Group.
I suspect the problem lies with the MSSQL Service Account (i.e. the account which is running the MSSQL Windows Service). It will require access to the trusted domain plus the various forest relationships and child domains. Is the svc account trusted for delegation? How have you configured the SPNs, and other areas of the security infra?
November 2, 2020 at 2:06 pm
I checked the user that is logged into the MS SQL service (MSSQLSERVER) and it shows as NT Service\MSSQLSERVER. This in on a cloud server infrastructure (Trusted Domain T). Since it is a cloud domain.
I don't think I have access to that AD. I mean there's a trusted relationship, but I don't think I can create users/groups on it.
November 2, 2020 at 2:45 pm
I think you will have problems if the cloud MSSQL is using the default built-in virtual service account. "NT SERVICE\MSSQLSERVER" will appear on the network as "DOMAIN\servername$", effectively using the credentials of the local computer account. This is difficult enough to manage in a single domain environment. In a multi-domain environment, I'm not sure it will work (certainly won't work securely). Somehow that service account has to figure out who the remote user is, which domain they are in, their group membership, and then translate and map that to a local Login (server level principle).
The fact that you are able to add the remote user directly as a local Login, suggests that the trust relationships do work, and the service account can identify the remote user. So maybe you can get this to work.
Are you able to trace the Active Directory queries which the service account submits to your domain? Or dig into the Kerberos ticket which the remote user will submit to the MSSQL service account?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply