Shulou(Shulou.com)06/01 Report--

Scheme 2: the active and standby source IP is used for AG communication, and the new Nic is bound to IP for business access.

Test environment description:

AG role

Hostnam

Network adapter

IP address

Main library

TEST-GS-ZHXT1

Ethernet0

10.198.197.167

Ethernet1

10.198.197.173

Prepare the library

TEST-GS-ZHXT2

Ethernet0

10.198.197.168

Ethernet1

10.198.197.174

Disaster preparedness

TEST-GS-ZHXT3

Ethernet0

10.198.194.183

General idea:

Separate the network card used by the business from the network card used by high availability, use Ethernet 0 as the priority for high availability, and configure the network card priority. WSFC and AG give priority to Ethernet 0 communication during a failover. Business access uses a SQL account, which does not require Kerberos verification, but is SQL verification.

Adjust the priority of the network card:

Start-> run-> enter "ncpa.cpl"-> shortcut key "Alt+N"-> Advanced Settings

Adjust the priority of the network adapter bound to the source IP, such as Ethernet0, to the one.

Add static routes to the accessed business application IP:

Since the host can only have one default gateway, it has been set to a network adapter bound by the source IP, such as Ehernet0. For business applications that need to access the new IP, you need to use route add-p to add static routes and use the if parameter to specify the specific network adapter interface ID.

Remote Windows authentication logs in to the SQL Server instance using Kerberos authentication:

Remotely log in to the SQL Server instance using the domain account to view the verification method:

Select * from sys.dm_exec_connections where session_id=@@spid

Cannot use Kerberos authentication, using NTLM.

Reference: https://technet.microsoft.com/en-us/library/bb463166.aspx

Open Kerberos log debugging:

"

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

According to the misjudgment, the SPN should not be registered or registered correctly.

Reference: 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.

"

According to the above "Kerberos relies on the presence of both forward and reverse lookup entries in DNS." For IP bound with a new network card, you need to go to DNS to do reverse parsing. As shown below:

Then verify the connection, that is, Kerberos verification.

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

"

SQL Server 2008/2008 R2

1) when SPN is mapped to the correct domain or built-in machine account (Local System, Network Service), the local connection uses NTLM and the remote connection uses Kerberos.

2) when the SPN registered in the correct domain or built-in machine account is not found, the connection will use NTLM.

3) when there is an incorrect SPN in the domain, the authentication fails.

"

For more information on the process of Kerberos verification, please see https://blogs.technet.microsoft.com/askds/2008/03/06/kerberos-for-the-busy-admin/.

Test:

1. Remove the standby node from AG, switch between 10.198.197.173 and 10.198.197.174, log in to the server through 167,168, and disable the network card after modifying the IP address of Ethernet 1. Delete the original resolution from the DNS reverse lookup zone and add the address resolution of the new corresponding relationship.

View WSFC status:

Verify remote access to 10.198.197.173 and 174via Windows to see if Kerberos authentication is used:

Can access the database service remotely.

two。 After the business switch to standby database test is completed, IP switches back.

Can access the database service remotely.

Summary:

The second scheme can meet the needs of separate use of business IP and high-availability IP. It can ensure the priority and stable use of Ethernet 0 by WSFC and AG, and ensure the security and reliability of the cluster.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.