Sql-server – Cross Domain SQL Server Logins Using Windows Authentication

cross-domainsql serversql-server-2005windows-authentication

I have a SQL Server 2005 named instance using Windows Authentication with domain groups serving as logins. The domain structures are as follows:

      Forest1                Forest2
      /      \                  |
Domain1       Domain2        Domain3

Objects are organized in the following domains:

Forest1.Domain1

  • Users
  • Global Groups

Forest1.Domain2

  • SQL Server Instance
  • Domain Local Groups (serving as Logins)

Forest2.Domain3

  • Users
  • Global Groups

All my users exist in Domain1 and Domain3 but the SQL Server box exists in Domain2. As such, my logins are domain groups in Domain2. When a user in Domain1 is added to a domain local group in Domain2 and attempts to connect using TCP/IP protocol to the SQL Server instance, he receives the following error message:

Cannot connect to <instance>. Login failed for user 'Domain1\userName'. (Microsoft SQL Server, Error: 18456)

Other things I've tried:

  • If I add the user as a login
    explicitly, he can connect.

  • If I add a Domain1 global group of
    which the user is a member as a login
    explicitly, he can connect.

  • If I add a Domain1 global group of
    which the user is a member as a
    member of the Domain2 domain local
    group used as a login, he cannot
    connect.

  • EDIT: If I add the Domain2 domain local group to the Demote Desktop Users group on the Domain2 server hosting the SQL Server instance, the Domain1 user can successfully connect to the server – I can also connect to the instance locally as the Domain1 user (just not remotely).

  • EDIT: If I add the Domain2 domain local group to a local server group and create a SQL Server login for that local server group, the Domain1 user still cannot connect to the instance remotely.

  • EDIT: If I change the connection network protocol to "Named Pipes", the Domain1 user can successfully connect remotely.

From what I understand (referencing these TechNet articles: Group Scope and Nesting Groups), the domain group MUST be a domain local group in order to include users from both Domain1 and Domain3.

How can I use a domain group as a SQL Server login using Windows authentication such that the domain group can contain users from both Domain1 and Domain3 and users can connect remotely via TCP/IP?

MORE NOTES

  • The service account for the SQL Server named instance is a user account in Domain1
  • SPN's have been added for the service account (including server name and alias names)

UPDATE

Changing the SQL Service instance service account to be in Domain2 seems to have resolved the issue. I'll investigate further and post back my findings!

Best Answer

As mentioned in my question update, changing the service account to be in Domain2 resolved the issue. So what was going on?

The Problem - Explained

From what I can tell (also with help from a Microsoft representative), because the service account was originally a Domain1 user, it could not determine what domain local groups the connecting user is a member of when the user is authenticating via Kerberos. The primary lead that this was a Kerberos issue was when I successfully connected using "Named Pipes" as this uses NTLM authentication.

Overall Solution

To bring it all together, to successfully add users from Domain1 and Domain3 as members of groups in Domain2 so that the groups can be used as SQL Server logins with Windows authentication, here's a list of requirements (or at least strongly encouraged):

  1. Established trust relationships between the domains
    1. At a minimum, 1 way trusts must be set up so that Domain2 trusts Domain1 and Domain3
  2. Groups in Domain2 must be scoped "Domain Local"
    1. This is so you can add users and groups from Domain1 and Domain3
    2. See here for more info
  3. Use SQL Server Configuration Manager to designate a non-administrative Domain2 user as the service account identity
    1. MSDN documents why using a domain user account may be preferred
    2. Even though the configuration manager is supposed to add users to local SQL Server 2005 specific groups for you (i.e. SQLServer2005MSSQLUser$MY_MACHINE$MY_INSTANCE), I ran into a few instances where this wasn't the case. So just check your local groups to ensure they've been updated appropriately with your Domain2 user account.
    3. Although SQL Server set up should automatically assign appropriate permissions for their local groups, again, I ran into a few instances where this was not the case. If this happens to you, you can reference this MSDN article along with the previously mentioned article for permission requirements.
  4. Configure a Service Principal Name (SPN) for the SQL Server instance host (including any aliases) and the Domain2 service account
    1. The SPN is required for mutual authentication between the client and the server host
    2. See this TechNet article for more info
  5. Depending on how you intend to use impersonation, you may want to enable the Domain2 service account to be trusted for delegation
    1. See this TechNet article for more info
  6. Enable remote connections for the SQL Service instance
  7. Finally, create logins for desired Domain2 groups and any Domain1 or Domain3 members should be able to connect remotely!

Note

As always with any remote network activity, check your firewalls to ensure your SQL Server ports are not blocked. Although the default port is 1433, check to make sure your port is in the clear.

Related Topic