组托管服务帐户（Group Managed Service Accounts，即gMSAs）

组托管服务帐户（Group Managed Service Accounts，即gMSAs）

组托管服务帐户是针对多个服务器的 MSA。 windows 为在一组服务器上运行的服务管理服务帐户。 Active Directory 自动更新组托管服务帐户密码，而不重启服务。 你可以配置 SQL Server 服务以使用组托管服务帐户主体。 从 sql Server 2014 开始，SQL Server 针对独立实例、故障转移群集实例和可用性组，在 Windows Server 2012 R2 和更高版本上支持组托管服务帐户。

若要使用 SQL Server 2014 或更高版本的组托管服务帐户，操作系统必须是 Windows Server 2012 R2 或更高版本。 装有 Windows Server 2012 R2 的服务器需要应用 KB 2998082 ，以便服务可以在密码更改后立即登录而不中断。

组托管服务帐户有注册SPN的权限。

（备注：在实际部署中，看到有Write servicePrincipalName权限，但是没有Read servicePrincipalName权限。按照Http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/01/19/Got-cannot-generate-sspi-context-error-message-after-changing-sql-service-account/步骤手工添加后，重启服务，查看错误日志，发现SPN注册成功）

备注：域管理员必须先在 Active Directory 中创建组托管服务帐户，然后 SQL Server 安装程序才能将其用于 SQL Server 服务。

配置步骤

1. 如果是首次创建组托管服务帐户，需要创建KDS根秘钥。

Add-KdsRooTKEy -EffectiveTime ((Get-Date).addhours(-10))

2. 先在域控上创建计算机组SQLServers，将需要使用组托管服务帐户的主机添加进来。

3. 在域控上创建这些组托管服务帐户。

在域控上创建SSAS服务帐户gMSAsqlssas、×××S服务帐户gMSAsql***s、SQL Server服务帐户gMSAsqldbe、SQL Server Agent服务帐户gMSAsqlagt：

New-ADServiceAccount -name gMSAsqlssas -DNSHostName dc.jztest.com -PrincipalsAllowedToRetrieveManagedPassWord SQLServers
New-ADServiceAccount -name gMSAsql***s -DNSHostName dc.jztest.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers
New-ADServiceAccount -name gMSAsqldbe -DNSHostName dc.jztest.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers
New-ADServiceAccount -name gMSAsqlagt -DNSHostName dc.jztest.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers

4. 查看帐户状态。

Get-ADServiceAccount gMSAsqlssas -Properties msDS-GroupMsaMembership | Select -Expand msDS-GroupMsaMembership | Select -Expand Access | Select -Expand IdentityReference

5. 重启成员服务器，并安装和验证组托管服务帐户。

在各成员服务器执行以下脚本：

Import-Module ServerManager
Add-WindowsFeature RSAT-AD-Powershell
Import-Module ActiveDirectory
Install-ADServiceAccount gMSAsqlssas
Install-ADServiceAccount gMSAsql***s
Install-ADServiceAccount gMSAsqldbe
Install-ADServiceAccount gMSAsqlagt
Test-ADServiceAccount gMSAsqlssas
Test-ADServiceAccount gMSAsql***s
Test-ADServiceAccount gMSAsqldbe
Test-ADServiceAccount gMSAsqlagt

6. 为了使用UNC远程备份，如果你习惯使用默认共享如c$去做备份，需要将这些账号添加到每台成员服务器的Backup Operators组中。但是，这是一个很不好的习惯，管理共享都是给与具有管理员权限的用户使用的，在日常运维过程中，务必使用共享文件夹方式。如果使用共享文件夹，gMSAs无需执行此步骤。

（Administrative shares are hidden network shares created by Windows NT family of operating systems that allow system administrators to have remote access to every disk volume on a network-connected system. These shares may not be permanently deleted but may be disabled. Administrative shares cannot be accessed by users without administrative privileges.）

7. 配置各服务使用组托管服务帐户，密码留空并确认。

测试场景

1. 域控修改组托管服务帐户密码

域控修改组托管服务帐户密码后，验证各副本的连接，正常。

2. 使用脚本切换主备实例

使用mov.ps1切换主备实例后，新的主实例能正常运行。

备注：在Windows Server 2012 R2上使用gMSAs务必打相关补丁，否则会遇到配置了该账号的服务莫名崩溃的情况。见KB 2998082

后记：

使用域账号远程登录SQL Server实例，查看验证方式：

select * from sys.dm_exec_connections where session_id=@@spid;

无法使用Kerberos验证，使用的是NTLM。

参考：https://technet.microsoft.com/en-us/library/bb463166.aspx

打开Kerberos日志调试：

“

On an Active Directory server, Kerberos error messages are found in the Event Log. It is necessary to enable extended Kerberos logging before all message types will appear. To enable extended Kerberos logging, add a DWORD reGIStry entry of LogLevel in the following location, and set it to 1:

HKEY_LOCAL_MacHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters

The server must be started after this change before the logging will be implemented.

”

根据错误判断，应该是SPN未注册或未正确注册。

参考：https://technet.microsoft.com/en-us/library/bb463167.aspx

“

• DNS problems are often encountered only during a service ticket request after a successful TGT request. If a client can successfully authenticate initially but is then unable to acquire a service ticket or access services, then DNS problems are the likely cause.

• The error “Server not found in Kerberos database” is common and can be misleading because it often appears when the service principal is not missing. The error can be caused by domain/realm mapping problems or it can be the result of a DNS problem where the service principal name is not being built correctly. Server logs and network traces can be used to determine what service principal is actually being requested.

• Kerberos recognizes short host names as different from long host names. For example, problems may occur if a client computer knows an application server as appserver1.example.com, but the Kerberos server knows the same computer as appserver1. Check that each host in the environment knows the others by using a consistent naming pattern.

• Kerberos is case sensitive. Problems can occur in an environment using host names with mixed case. In the world of Kerberos, appserver1.EXAMPLE.COM and appserver1.example.com are not the same. Check that DNS resolves host names with consistent case.

• Kerberos relies on the presence of both forward and reverse lookup entries in DNS. Check that the host name of each computer can be resolved to its IP address and that its IP address can be resolved to its host name.

• DNS domain name ambiguities in a multidomain environment can result in subtle DNS issues. Check that each computer knows the others using the same domain name. Avoiding the use of short host names is particularly important in a multidomain environment.

• Look carefully at the configuration of any multihomed hosts. You might need to perfORM network traces to determine which interfaces and what names are being used in requests to or from computers with multiple network cards.

”

根据上文中“Server logs and network traces can be used to determine what service principal is actually being requested.”和“Kerberos recognizes short host names as different from long host names.”查找到日志中报错记录的是短域名的SPN。根据“Kerberos is case sensitive.”发现报错记录的短域名的SPN是小写的。于是，手工注册该小写短域名SPN。“Kerberos relies on the presence of both forward and reverse lookup entries in DNS.”对于绑定了新的网卡的IP，需要到DNS去做反向解析。

再去验证连接，就是Kerberos验证了。

因此，一定要去验证远程连接是否使用了Kerberos验证。

那么问题来了，为什么自动注册的SPN不行，而还要手动注册呢？自动注册的SPN是根据FQDN解析出的地址去自动注册的，机器名大写，在DNS里是大写的，那么注册的SPN也是大写的。那为什么会需要小写手工注册呢？我发现，之前为了测试Read-scale availability groups(a new feature introduced in SQL Server 2017)，在主机HOST中，添加了小写的域名解析：

那么导致了无法使用Kerberos验证。于是，从域控删除小写的SPN。重新尝试远程使用Windows验证连接，结果确实使用了Kerberos验证。

那么这两点我们再温习一遍：

“Kerberos relies on the presence of both forward and reverse lookup entries in DNS. Check that the host name of each computer can be resolved to its IP address and that its IP address can be resolved to its host name.”

“Kerberos is case sensitive. Problems can occur in an environment using host names with mixed case. In the world of Kerberos, appserver1.EXAMPLE.COM and appserver1.example.com are not the same. Check that DNS resolves host names with consistent case.”

结论是：SPN自动注册能使用Kerberos验证。

参考：https://blogs.msdn.microsoft.com/apGCdsd/2011/09/26/kerberosntlm-sql-server/

“

SQL Server 2008/2008 R2

1) 当SPN被映射到正确的域或者内建机器账号时 (Local System, Network Service)，本地连接会使用NTLM，而远程连接会使用Kerberos。

2) 当没有找到注册在正确的域或内建机器账号下的SPN时，连接会使用NTLM。

3) 当域中存在错误的SPN时，认证失败。

”

具体Kerberos验证的过程，可以参考：https://blogs.technet.microsoft.com/askds/2008/03/06/kerberos-for-the-busy-admin/