Cross Domain SQL Server Logins Using Windows Authentication

by

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.

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. What is the best way to create and populate a numbers table?
  3. Sql Server string to date conversion
  4. SQL Server query to find all permissions/access for all users in a database
  5. SQL Server IN vs. EXISTS Performance
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. PIVOT in sql 2005
  8. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  9. Atomic UPSERT in SQL Server 2005
  10. Do I really need to use “SET XACT_ABORT ON”?
  11. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  12. SQL Server ORDER BY date and nulls last
  13. How to determine total number of open/active connections in ms sql server 2005
  14. Connecting to MS SQL Server with Windows Authentication using Python?
  15. How to remove accents and all chars a..z in sql-server?
  16. Find non-ASCII characters in varchar columns using SQL Server
  17. How to query values from xml nodes?
  18. How to query for Xml values and attributes from table in SQL Server?
  19. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  20. What happens to the primary key Id when it goes over the limit?
  21. What is the point of “Initial Catalog” in a SQL Server connection string?
  22. How do you kill all current connections to a SQL Server 2005 database?
  23. SQL Server recursive query
  24. Milliseconds wrong when converting from XML to SQL Server datetime
  25. nvarchar(max) vs NText
  26. “Prevent saving changes that require the table to be re-created” negative effects
  27. Get row where datetime column = today
  28. Convert UTC Milliseconds to DATETIME in SQL server
  29. Table Valued Function Killing My Query Performance
  30. @@ERROR and/or TRY – CATCH

Leave a Comment