設定 Kerberos 驗證與 SQL Server 連線與問題排除

        最近遇到許多關於 Kerberos 的問題，所以我也自學習並整理相關 Kerberos 的問題，希望對大家會有幫助，也提供給自已記錄。

在 SQL Server 的驗證模式，可以分成 SQL Server 驗證與 Windows 的整合驗證，而在網域的環境下，Windows 整合驗證主要透過 Kerberos 進行，但在 Kerberos 無法啟用成功時，就會透過 NTLM 的方式進行，簡單的說，NTLM 是一種舊的認證方式，而且本身在證驗過程會帶著使用者輸入的密碼，而且加密方式也被證實是可以被反計算出的，所以微軟建議透過 Kerberos 的方式進行連線驗證，而二者之間的差異，也可以透過下列的文章進行了解。

NTLM 驗證模式：

Kerberos 驗證模式：

NTLM vs KERBEROS
https://answers.microsoft.com/en-us/msoffice/forum/all/ntlm-vs-kerberos/d8b139bf-6b5a-4a53-9a00-bb75d4e219eb

使用 Kerberos 的好處：

• More secure: No password stored locally or sent over the net.
• Best performance: improved performance over NTLM authentication.
• Delegation support: Servers can impersonate clients and use the client's security context to access a resource.
• Simpler trust management: Avoids the need to have p2p trust relationships on multiple domains environment.
• Supports MFA (Multi Factor Authentication)

而 SQL Server 如果要使用 Kerberos 的驗證模式，必須符合下列二個條件。

1. 用戶端與主機都在相同的網域或在信任的網域中。
2. Service principal Name(SPN) 又可稱為服務主機名稱，必預註冊於網域中。

Register a Service Principal Name for Kerberos connections
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver16

針對上述的說明，如果在網域中，預設的情況下會使用 Kerberos 的驗證模式進行，但如何確認目前 Kerberos 是否有啟用，可以透過下列的方式進行確認。

1. 從 SQL Server 的 Error Log 中，確認主機是否有正確的註冊 SPN。

成功的註冊 SPN:

2023-11-16 09:37:30.94 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG1.mscaryhsu.com ] for the SQL Server service.

2023-11-16 09:37:30.94 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG1.mscaryhsu.com:1433 ] for the SQL Server service.

SPN 註冊失敗：

2023-11-16 10:34:40.80 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG2.mscaryhsu.com ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2023-11-16 10:34:40.80 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL2019-AG2.mscaryhsu.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2. 透過語法確認目前的連線是 NTLM or Kerberos.

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

如何進行自動註冊 SPN 的動作。

在 SQL Server 端，預設在啟動時，會進行自動註冊 SPN 的動作，如同上述的日誌檔顯示，即可確認成功與否，而這部份也要注意你的啟動帳號必須要有下列的權限，關於啟動帳號的部份，也可以參考我另一篇的文章說明。

Read servicePrincipalName
Write servicePrincipalName

SQL Server 啟動帳號最佳實踐與最小化權限設定
http://caryhsu.blogspot.com/2023/11/sql-server.html

當無法進行自動註冊時，就需要進行手動註冊與問題排除，在此篇，我就透過上述 SPN 註冊失敗的情況為例，說明該如何進行問題排除。

1。從錯誤訊息上看 Windows return code: 0x21c7，如同下列的說明，主要是疑似 SPN 重覆，造成註冊 SPN 失敗。

SPN: 0x21c7
ERROR_DS_SPN_VALUE_NOT_UNIQUE_IN_FOREST The operation failed because SPN value provided for addition/modification isn't unique forest-wide.

2. 透過 setspn 的指令進行確認，確認該啟動帳號已註冊的 SPN 有那些。

setspn -L mscaryhsu\gmsasql

Registered ServicePrincipalNames for CN=gMSAsql,CN=Managed Service Accounts,DC=mscaryhsu,DC=com:

MSSQLSvc/SQL2019-AG1.mscaryhsu.com:1433
MSSQLSvc/SQL2019-AG1.mscaryhsu.com

3. 從上述來看，只有第一個節點 (AG1) 有註冊成功，但第二個節點 (AG2) 最沒有註冊，所以我嘗試手動註冊第二個節點。

在註冊前，簡單的說一下格式，在預設的情況下，SQL Server 預設的 port 為 1433，所以需同時註冊二個，一個是沒有 port 的連線方式，而另一個則是預設的 port

格式：
setspn -S MSSQLSvc/Server FQDN domain\username

手動進行註冊：

setspn -S MSSQLSvc/SQL2019-AG2.mscaryhsu.com  mscaryhsu\gmsasql

Checking domain DC=mscaryhsu,DC=com
CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Duplicate SPN found, aborting operation!

setspn -S MSSQLSvc/SQL2019-AG2.mscaryhsu.com:1433  mscaryhsu\gmsasql
Checking domain DC=mscaryhsu,DC=com

Registering ServicePrincipalNames for CN=gMSAsql,CN=Managed Service Accounts,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com:1433

Updated object

從上述來看沒有 port 的那一個是註冊失敗的，所以也就是為何 SQL Server 自動註冊失敗的情況。

4。 透過 setspn -x 的方式來進行找出重覆的 SPN，但可惜是找不出來的，原因在後面會描述。

setspn -x
Checking domain DC=mscaryhsu,DC=com
Processing entry 0

found 0 group of duplicate SPNs.

5. 透過網域主機上的 Directory Service 日誌，發現一個明顯的錯誤，其實認真的看，你可以看出，主要是有另一個使用者 "caryhsu" 已註冊此台主機所造成，所以當你透過新帳號進行註冊時，就會出現帳號重覆註冊的情況。

Log Name:      Directory Service
Source:        Microsoft-Windows-ActiveDirectory_DomainService
Date:          2023/11/16 上午 10:34:40
Event ID:      2974
Task Category: Global Catalog
Level:         Error
User:          MSCARYHSU\gMSAsql$
Computer:      Win-CaryDC.mscaryhsu.com
Description:
The attribute value provided is not unique in the forest or partition. Attribute: servicePrincipalName Value=MSSQLSvc/SQL2019-AG2.mscaryhsu.com
CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com Winerror: 8647 

6. 透過下列的指令，你可以發現原來兇手就是自已的另一個帳號，因為我換了啟動帳號後，原先的帳號沒有刪除，造成無法註冊更換後的新帳號。

setspn -Q MSSQLSvc/SQL2019-AG2.mscaryhsu.com6.
Checking domain DC=mscaryhsu,DC=com
CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Existing SPN found!

7。 最後，將此舊的 SPN 刪除，並再進行一次註冊後，問題就解決了。

setspn -D MSSQLSvc/SQL2019-AG2.mscaryhsu.com  mscaryhsu\caryhsu
Unregistering ServicePrincipalNames for CN=cary hsu,CN=Users,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Updated object

setspn -S MSSQLSvc/SQL2019-AG2.mscaryhsu.com  mscaryhsu\gmsasql
Checking domain DC=mscaryhsu,DC=com
Registering ServicePrincipalNames for CN=gMSAsql,CN=Managed Service Accounts,DC=mscaryhsu,DC=com
MSSQLSvc/SQL2019-AG2.mscaryhsu.com

Updated object