溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
組托管服務帳戶（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.
”
| Error | ErrorName | Description |
| 0x7 | KDC_ERR_S_PRINCIPAL_UNKNOWN | Server not found in Kerberos database |
根據錯誤判斷，應該是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/
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
