跨域SQL Server登录使用Windows身份验证 [英] Cross Domain SQL Server Logins Using Windows Authentication

问题描述

我有一个使用Windows身份验证的SQL Server 2005命名实例，其中域组用作登录。域结构如下：

  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实例，他收到以下错误消息：

无法连接到< instance> ;.用户Domain1\userName的登录失败。 （Microsoft SQL Server，错误：18456）

我尝试过的其他事情：

• 如果我明确将用户添加为登录名
  ，则可以连接。




• 如果我添加一个 Domain1
  的全局组，用户作为登录
  的成员，他可以连接。

  li>
  

• 如果我添加一个 Domain1 全局组
  ，用户是
  成员的 Domain2 domain local
  组用作登录名，他不能
  连接。




• EDIT：如果我将 Domain2 域本地组添加到 Domain2 托管SQL Server实例的服务器， Domain1 用户可以成功连接到服务器 - 我也可以本地连接到实例<$ c $ 编辑：如果我添加了<$ p>

  c $ c> Domain2 域本地组添加到本地服务器组并为该本地服务器组创建SQL Server登录名， Domain1 用户仍然无法




• 编辑：如果我将连接网络协议更改为命名管道，则 Domain1 用户可以成功远程连接。

---

根据我的理解（参考这些TechNet文章：组作用域和嵌套组），域组必须是以包括来自 Domain1 和 Domain3 的用户。

如何使用Windows身份验证将域组用作SQL Server登录，以便域组可以包含 Domain1 和 Domain3 ，用户可以通过TCP / IP远程连接？

b

• SQL Server命名实例的服务帐户是 Domain1


• 已为服务帐户添加SPN（包括服务器名称和别名）

UPDATE

更改SQL Server实例服务帐户位于 Domain2 中似乎已解决了此问题。

解决方案

如我在问题更新中所述，将服务帐户更改为 Domain2 解决了问题。那么发生了什么？

问题 - 解释

可以告诉（也可以从Microsoft代表的帮助），因为服务帐户最初是 Domain1 用户，它无法确定连接用户是什么域本地组是成员当用户通过Kerberos进行身份验证时。这是一个Kerberos问题的主要诱因是当我使用命名管道成功连接，因为这使用NTLM身份验证。

总体解决方案 / p>

要将所有内容整合在一起，可以从 Domain1 和 Domain3 作为 Domain2 中的组的成员，以便这些组可以用作具有Windows身份验证的SQL Server登录，下面是一个需求列表（或至少强烈鼓励） ：

1. 在域之间建立的信任关系
   
   
   
   1. 必须设置为 Domain2 trusts Domain1 和 Domain3
   
   



2. Domain2 中的群组必须包含Local Local
   
   
   
   1. 这是为了您可以从 Domain1 和 Domain3 / li>
      
   2. 有关详情，请参见此处
   作为服务帐户身份
   
   
   
   1. 
      
   2. 即使配置管理器应该为您将用户添加到本地SQL Server 2005特定组（即， SQLServer2005MSSQLUser $ MY_MACHINE $ MY_INSTANCE），我遇到了一些情况不是这样的情况。因此，只需检查您的本地组，确保它们已使用 Domain2 用户帐户进行了适当更新。
   
   
   3. 自动为其本地组分配适当的权限，再次，我遇到了一些情况不是这样的情况。如果发生这种情况，您可以参考此 MSDN 文章
   
   
   
   4. 为SQL Server实例主机配置服务主体名称（SPN）别名）和 Domain2 服务帐户
      
      
      
      1. 客户端和服务器主机之间的相互身份验证需要SPN
      2. 
         
      3. 请参阅此 TechNet 文章更多信息
      
      

   
   
   5. 根据您打算使用模拟的方式，您可能需要启用 Domain2 服务帐户信任委托
      
      
      
      1. 查看此
      
      
      2. 为SQL服务实例启用远程连接
      / li>
      
      3. 最后，为所需的 Domain2 组和任何 Domain1 c $ c> Domain3 会员应该能够远程连接！
      
      

      >

      
      
      

      与任何远程网络活动一样，检查您的防火墙以确保您的SQL Server端口不被阻止。虽然默认端口是1433，请检查以确保您的端口在清除。

      
      

      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!

      解决方案

      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.

      这篇关于跨域SQL Server登录使用Windows身份验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！