AlwaysOn業務IP和高可用IP分開使用（三）

方案二：主備源IP用于AG通信、主備新增網卡綁定IP用于業務訪問

測試環境描述：

總體思路：

將業務使用的網卡和高可用使用的網卡分開，高可用優先使用到Ethernet 0，配置網卡優先級。在故障切換時WSFC和AG優先使用Ethernet 0通信。業務訪問使用的是SQL賬號，無需Kerberos驗證，是SQL驗證。

調整網卡優先級：

開始->運行->輸入“ncpa.cpl”->快捷鍵“Alt+N”->高級設置

將源IP綁定的網絡適配器如Ethernet0優先級調整到最一個。

對于訪問的業務應用IP添加靜態路由：

由于主機只能有一個默認網關，已設置到源IP綁定的網絡適配器如Ehernet0上。對于需要訪問新增IP的業務應用，需要使用route add -p添加靜態路由，并使用if參數指定具體的網絡適配器接口ID。

遠程Windows驗證登錄SQL Server實例使用Kerberos驗證：

使用域賬號遠程登錄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

“

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.

”

根據上文中 “Kerberos relies on the presence of both forward and reverse lookup entries in DNS.”對于綁定了新的網卡的IP，需要到DNS去做反向解析。如下圖：

再去驗證連接，就是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/

測試：

1. 從AG移除備節點，切換10.198.197.173和10.198.197.174，通過167和168登錄服務器，修改Ethernet 1的IP地址后，禁用啟用網卡。DNS反向查找區域中刪除原來的解析，添加新對應關系的地址解析。

查看WSFC狀態：

通過Windows驗證遠程訪問10.198.197.173和174，查看是否使用Kerberos驗證：

都能遠程訪問數據庫服務。

2. 業務切換到備庫測試完畢后，IP切換回來。

都能遠程訪問數據庫服務。

總結：

方案二能滿足業務IP和高可用IP分開使用的需求。能保證WSFC和AG對Ethernet 0的優先穩定使用，保證集群的安全可靠。