使用 Windows 身份验证的跨域 SQL Server 登录

2022-01-01 00:00:00 sql-server-2005 sql-server cross-domain windows-authentication

我有一个 SQL Server 2005 命名实例，它使用 Windows 身份验证，域组用作登录名.域结构如下:

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

对象组织在以下域中:

Forest1.Domain1

  • 用户
  • 全球组

Forest1.Domain2

  • SQL Server 实例
  • 域本地组(用作登录)

Forest2.Domain3

  • 用户
  • 全球组

我的所有用户都存在于 Domain1 和 Domain3 中，但 SQL Server 框存在于 Domain2 中.因此，我的登录名是 Domain2 中的域组.当 Domain1 中的用户被添加到 Domain2 中的域本地组并尝试使用 TCP/IP 协议连接到 SQL Server 实例时，他会收到以下错误消息:

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:

无法连接到<实例>.用户 'Domain1userName' 登录失败.(Microsoft SQL Server，错误:18456)

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

我尝试过的其他事情:

  • 如果我将用户添加为登录名显然，他可以连接.

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

如果我添加一个 Domain1 全局组用户是作为登录名的成员显然，他可以连接.

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

如果我添加一个 Domain1 全局组用户是其中的成员Domain2 域本地成员用作登录名的组，他不能连接.

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.

如果我将 Domain2 域本地组添加到托管 SQL 的 Domain2 服务器上的降级桌面用户组服务器实例，Domain1 用户可以成功连接到服务器——我也可以以Domain1 用户的身份在本地连接到该实例(只是不是远程).

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).

如果我将 Domain2 域本地组添加到本地服务器组并为该本地服务器组创建 SQL Server 登录，Domain1 用户仍然无法远程连接到实例.

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.

如果我将连接网络协议更改为命名管道"，Domain1 用户可以成功远程连接.

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

据我所知(参考这些 TechNet 文章:组范围和嵌套组)，域组必须是域本地组，以便包含来自 Domain1 和 Domain3 的用户.

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.

如何使用域组作为使用 Windows 身份验证的 SQL Server 登录，以便域组可以包含来自 Domain1 和 Domain3 的用户，并且用户可以远程连接通过 TCP/IP?

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?

更多注意事项

  • SQL Server 命名实例的服务帐户是 Domain1
    • 中的用户帐户
  • 已为服务帐户添加了 SPN(包括服务器名称和别名)

更新

将 SQL 服务实例服务帐户更改为 Domain2 似乎已解决该问题.我会进一步调查并发回我的发现！

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!

推荐答案

如我的问题更新中所述，将服务帐户更改为 Domain2 解决了该问题.那到底是怎么回事?

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

问题 - 已解释

据我所知(也在 Microsoft 代表的帮助下)，由于服务帐户最初是 Domain1 用户，因此无法确定连接用户是哪个域本地组的成员当用户通过 Kerberos 进行身份验证时.这是 Kerberos 问题的主要线索是当我使用命名管道"成功连接时，因为它使用 NTLM 身份验证.

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.

整体解决方案

综合起来，成功地将来自Domain1和Domain3的用户添加为Domain2中的组成员，以便这些组可以用作使用 Windows 身份验证的 SQL Server 登录名，以下是要求列表(或至少强烈建议):

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. 在域之间建立信任关系
  1. Established trust relationships between the domains
  1. 至少必须设置 1 路信任，以便 Domain2 信任 Domain1 和 Domain3
  1. At a minimum, 1 way trusts must be set up so that Domain2 trusts Domain1 and Domain3

  • Domain2 中的组必须在域本地"范围内

  • Groups in Domain2 must be scoped "Domain Local"

    1. 这样您就可以从 Domain1 和 Domain3
      2. 添加用户和组
    2. 请参阅此处了解更多信息
    1. This is so you can add users and groups from Domain1 and Domain3
    2. See here for more info

  • 使用 SQL Server 配置管理器将非管理 Domain2 用户指定为服务帐户标识

  • Use SQL Server Configuration Manager to designate a non-administrative Domain2 user as the service account identity

    1. MSDN 说明为什么使用域用户帐户可能是首选
    2. 即使配置管理器应该为您将用户添加到本地 SQL Server 2005 特定组(即 SQLServer2005MSSQLUser$MY_MACHINE$MY_INSTANCE)，但我遇到了一些情况并非如此.因此，只需检查您的本地组，确保它们已使用您的 Domain2 用户帐户进行了适当的更新.
    3. 虽然 SQL Server 设置应该自动为其本地组分配适当的权限，但我还是遇到了一些情况并非如此.如果您遇到这种情况，您可以参考此 MSDN 文章以及前面提到的关于权限要求的文章.
    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.

  • 为 SQL Server 实例主机(包括任何别名)和 Domain2 服务帐户配置服务主体名称 (SPN)

  • Configure a Service Principal Name (SPN) for the SQL Server instance host (including any aliases) and the Domain2 service account

    1. 客户端和服务器主机之间的相互身份验证需要 SPN
    2. 查看这篇 TechNet 文章了解更多信息信息
    1. The SPN is required for mutual authentication between the client and the server host
    2. See this TechNet article for more info

  • 根据您打算如何使用模拟，您可能希望启用 Domain2 服务帐户以进行委托

    1. 查看这篇 TechNet 文章了解更多信息信息
    1. See this TechNet article for more info

  • 为 SQL 服务实例启用远程连接
  • 最后，为所需的 Domain2 组创建登录名，并且任何 Domain1 或 Domain3 成员都应该能够远程连接！

  • Enable remote connections for the SQL Service instance
  • Finally, create logins for desired Domain2 groups and any Domain1 or Domain3 members should be able to connect remotely!

    • 注意

    与任何远程网络活动一样，请检查您的防火墙以确保您的 SQL Server 端口未被阻止.尽管默认端口是 1433，但请检查以确保您的端口畅通无阻.

    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.

    相关文章