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):
- Established trust relationships between the domains
- At a minimum, 1 way trusts must be set up so that
Domain2
trustsDomain1
andDomain3
- At a minimum, 1 way trusts must be set up so that
- Groups in
Domain2
must be scoped “Domain Local”- This is so you can add users and groups from
Domain1
andDomain3
- See here for more info
- This is so you can add users and groups from
- Use SQL Server Configuration Manager to designate a non-administrative
Domain2
user as the service account identity- MSDN documents why using a domain user account may be preferred
- 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. - 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.
- Configure a Service Principal Name (SPN) for the SQL Server instance host (including any aliases) and the
Domain2
service account- The SPN is required for mutual authentication between the client and the server host
- See this TechNet article for more info
- Depending on how you intend to use impersonation, you may want to enable the
Domain2
service account to be trusted for delegation- See this TechNet article for more info
- Enable remote connections for the SQL Service instance
- Finally, create logins for desired
Domain2
groups and anyDomain1
orDomain3
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.