AlwaysOn業(yè)務(wù)IP和高可用IP分開使用（三）

方案二：主備源IP用于AG通信、主備新增網(wǎng)卡綁定IP用于業(yè)務(wù)訪問

測試環(huán)境描述：

總體思路：

將業(yè)務(wù)使用的網(wǎng)卡和高可用使用的網(wǎng)卡分開，高可用優(yōu)先使用到Ethernet 0，配置網(wǎng)卡優(yōu)先級。在故障切換時WSFC和AG優(yōu)先使用Ethernet 0通信。業(yè)務(wù)訪問使用的是SQL賬號，無需Kerberos驗(yàn)證，是SQL驗(yàn)證。

調(diào)整網(wǎng)卡優(yōu)先級：

開始->運(yùn)行->輸入“ncpa.cpl”->快捷鍵“Alt+N”->高級設(shè)置

將源IP綁定的網(wǎng)絡(luò)適配器如Ethernet0優(yōu)先級調(diào)整到最一個。

對于訪問的業(yè)務(wù)應(yīng)用IP添加靜態(tài)路由：

由于主機(jī)只能有一個默認(rèn)網(wǎng)關(guān)，已設(shè)置到源IP綁定的網(wǎng)絡(luò)適配器如Ehernet0上。對于需要訪問新增IP的業(yè)務(wù)應(yīng)用，需要使用route add -p添加靜態(tài)路由，并使用if參數(shù)指定具體的網(wǎng)絡(luò)適配器接口ID。

遠(yuǎn)程Windows驗(yàn)證登錄SQL Server實(shí)例使用Kerberos驗(yàn)證：

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

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

無法使用Kerberos驗(yàn)證，使用的是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

“

Common DNS Issues

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ù)上文中 “Kerberos relies on the presence of both forward and reverse lookup entries in DNS.”對于綁定了新的網(wǎng)卡的IP，需要到DNS去做反向解析。如下圖：

再去驗(yàn)證連接，就是Kerberos驗(yàn)證了。

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

“

SQL Server 2008/2008 R2

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

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

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

”

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

測試：

1. 從AG移除備節(jié)點(diǎn)，切換10.198.197.173和10.198.197.174，通過167和168登錄服務(wù)器，修改Ethernet 1的IP地址后，禁用啟用網(wǎng)卡。DNS反向查找區(qū)域中刪除原來的解析，添加新對應(yīng)關(guān)系的地址解析。

查看WSFC狀態(tài)：

通過Windows驗(yàn)證遠(yuǎn)程訪問10.198.197.173和174，查看是否使用Kerberos驗(yàn)證：

都能遠(yuǎn)程訪問數(shù)據(jù)庫服務(wù)。

2. 業(yè)務(wù)切換到備庫測試完畢后，IP切換回來。

都能遠(yuǎn)程訪問數(shù)據(jù)庫服務(wù)。

總結(jié)：

方案二能滿足業(yè)務(wù)IP和高可用IP分開使用的需求。能保證WSFC和AG對Ethernet 0的優(yōu)先穩(wěn)定使用，保證集群的安全可靠。