組托管服務(wù)帳戶（Group Managed Service Accounts，即gMSAs）

組托管服務(wù)帳戶（Group Managed Service Accounts，即gMSAs）

組托管服務(wù)帳戶是針對多個服務(wù)器的 MSA。 Windows 為在一組服務(wù)器上運行的服務(wù)管理服務(wù)帳戶。 Active Directory 自動更新組托管服務(wù)帳戶密碼，而不重啟服務(wù)。 你可以配置 SQL Server 服務(wù)以使用組托管服務(wù)帳戶主體。 從 SQL Server 2014 開始，SQL Server 針對獨立實例、故障轉(zhuǎn)移群集實例和可用性組，在 Windows Server 2012 R2 和更高版本上支持組托管服務(wù)帳戶。

若要使用 SQL Server 2014 或更高版本的組托管服務(wù)帳戶，操作系統(tǒng)必須是 Windows Server 2012 R2 或更高版本。 裝有 Windows Server 2012 R2 的服務(wù)器需要應(yīng)用 KB 2998082 ，以便服務(wù)可以在密碼更改后立即登錄而不中斷。

組托管服務(wù)帳戶有注冊SPN的權(quán)限。

（備注：在實際部署中，看到有Write servicePrincipalName權(quán)限，但是沒有Read servicePrincipalName權(quán)限。按照http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/01/19/got-cannot-generate-sspi-context-error-message-after-changing-sql-service-account/步驟手工添加后，重啟服務(wù)，查看錯誤日志，發(fā)現(xiàn)SPN注冊成功）

備注：域管理員必須先在 Active Directory 中創(chuàng)建組托管服務(wù)帳戶，然后 SQL Server 安裝程序才能將其用于 SQL Server 服務(wù)。

配置步驟

1. 如果是首次創(chuàng)建組托管服務(wù)帳戶，需要創(chuàng)建KDS根秘鑰。

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

2. 先在域控上創(chuàng)建計算機組SQLServers，將需要使用組托管服務(wù)帳戶的主機添加進(jìn)來。

3. 在域控上創(chuàng)建這些組托管服務(wù)帳戶。

在域控上創(chuàng)建SSAS服務(wù)帳戶gMSAsqlssas、×××S服務(wù)帳戶gMSAsql***s、SQL Server服務(wù)帳戶gMSAsqldbe、SQL Server Agent服務(wù)帳戶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. 查看帳戶狀態(tài)。

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

5. 重啟成員服務(wù)器，并安裝和驗證組托管服務(wù)帳戶。

在各成員服務(wù)器執(zhí)行以下腳本：

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遠(yuǎn)程備份，如果你習(xí)慣使用默認(rèn)共享如c$去做備份，需要將這些賬號添加到每臺成員服務(wù)器的Backup Operators組中。但是，這是一個很不好的習(xí)慣，管理共享都是給與具有管理員權(quán)限的用戶使用的，在日常運維過程中，務(wù)必使用共享文件夾方式。如果使用共享文件夾，gMSAs無需執(zhí)行此步驟。

（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. 配置各服務(wù)使用組托管服務(wù)帳戶，密碼留空并確認(rèn)。

測試場景

1. 域控修改組托管服務(wù)帳戶密碼

域控修改組托管服務(wù)帳戶密碼后，驗證各副本的連接，正常。

2. 使用腳本切換主備實例

使用mov.ps1切換主備實例后，新的主實例能正常運行。

備注：在Windows Server 2012 R2上使用gMSAs務(wù)必打相關(guān)補丁，否則會遇到配置了該賬號的服務(wù)莫名崩潰的情況。見KB 2998082

后記：

使用域賬號遠(yuǎn)程登錄SQL Server實例，查看驗證方式：

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

無法使用Kerberos驗證，使用的是NTLM。

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

打開Kerberos日志調(diào)試：

“

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.

”

根據(jù)錯誤判斷，應(yīng)該是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.

”

根據(jù)上文中“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。根據(jù)“Kerberos is case sensitive.”發(fā)現(xiàn)報錯記錄的短域名的SPN是小寫的。于是，手工注冊該小寫短域名SPN?！癒erberos relies on the presence of both forward and reverse lookup entries in DNS.”對于綁定了新的網(wǎng)卡的IP，需要到DNS去做反向解析。

再去驗證連接，就是Kerberos驗證了。

因此，一定要去驗證遠(yuǎn)程連接是否使用了Kerberos驗證。

那么問題來了，為什么自動注冊的SPN不行，而還要手動注冊呢？自動注冊的SPN是根據(jù)FQDN解析出的地址去自動注冊的，機器名大寫，在DNS里是大寫的，那么注冊的SPN也是大寫的。那為什么會需要小寫手工注冊呢？我發(fā)現(xiàn)，之前為了測試Read-scale availability groups(a new feature introduced in SQL Server 2017)，在主機HOST中，添加了小寫的域名解析：

那么導(dǎo)致了無法使用Kerberos驗證。于是，從域控刪除小寫的SPN。重新嘗試遠(yuǎn)程使用Windows驗證連接，結(jié)果確實使用了Kerberos驗證。

那么這兩點我們再溫習(xí)一遍：

“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.”

結(jié)論是：SPN自動注冊能使用Kerberos驗證。

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

“

SQL Server 2008/2008 R2

1) 當(dāng)SPN被映射到正確的域或者內(nèi)建機器賬號時 (Local System, Network Service)，本地連接會使用NTLM，而遠(yuǎn)程連接會使用Kerberos。

2) 當(dāng)沒有找到注冊在正確的域或內(nèi)建機器賬號下的SPN時，連接會使用NTLM。

3) 當(dāng)域中存在錯誤的SPN時，認(rèn)證失敗。

”

具體Kerberos驗證的過程，可以參考：https://blogs.technet.microsoft.com/askds/2008/03/06/kerberos-for-the-busy-admin/