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

Group managed Service account (Group Managed Service Accounts, i.e. gMSAs)

The group managed service account is a MSA for multiple servers. Windows manages service accounts for services running on a set of servers. Active Directory automatically updates the group managed service account password without restarting the service. You can configure the SQL Server service to use the group managed service account principal. Starting with SQL Server 2014, SQL Server supports group managed service accounts on Windows Server 2012 R2 and later for stand-alone instances, failover cluster instances, and availability groups.

To use a SQL Server 2014 or later group managed service account, the operating system must be Windows Server 2012 R2 or later. Servers with Windows Server 2012 R2 need to apply KB 2998082 so that the service can log in immediately after the password change without interruption.

The group managed service account has permission to register for SPN.

Note: in the actual deployment, you can see that you have Write servicePrincipalName permission, but you don't have Read servicePrincipalName permission. After manually adding according to the http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/01/19/got-cannot-generate-sspi-context-error-message-after-changing-sql-service-account/ steps, restart the service, view the error log, and find that the SPN registration is successful)

Note: domain administrators must create a group managed service account in Active Directory before SQL Server installer can use it for SQL Server services.

Configuration step

1. If you are creating a group managed service account for the first time, you need to create a KDS root key.

Add-KdsRootKey-EffectiveTime ((Get-Date). Addhours (- 10)

two。 First create the computer group SQLServers on the domain control and add the hosts that need to use the group managed service account.

3. Create these group managed service accounts on the domain control.

Create the SSAS service account gMSAsqlssas, × × S service account gMSAsql***s, SQL Server service account gMSAsqldbe, SQL Server Agent service account gMSAsqlagt on the domain control:

New-ADServiceAccount-name gMSAsqlssas-DNSHostName dc.jztest.com-PrincipalsAllowedToRetrieveManagedPassword SQLServersNew-ADServiceAccount-name gMSAsql***s-DNSHostName dc.jztest.com-PrincipalsAllowedToRetrieveManagedPassword SQLServersNew-ADServiceAccount-name gMSAsqldbe-DNSHostName dc.jztest.com-PrincipalsAllowedToRetrieveManagedPassword SQLServersNew-ADServiceAccount-name gMSAsqlagt-DNSHostName dc.jztest.com-PrincipalsAllowedToRetrieveManagedPassword SQLServers

4. View account status.

Get-ADServiceAccount gMSAsqlssas-Properties msDS-GroupMsaMembership | Select-Expand msDS-GroupMsaMembership | Select-Expand Access | Select-Expand IdentityReference

5. Restart the member server and install and verify the group managed service account.

Execute the following script on each member server:

Import-Module ServerManagerAdd-WindowsFeature RSAT-AD-PowerShellImport-Module ActiveDirectoryInstall-ADServiceAccount gMSAsqlssasInstall-ADServiceAccount gMSAsql***sInstall-ADServiceAccount gMSAsqldbeInstall-ADServiceAccount gMSAsqlagtTest-ADServiceAccount gMSAsqlssasTest-ADServiceAccount gMSAsql***sTest-ADServiceAccount gMSAsqldbeTest-ADServiceAccount gMSAsqlagt

6. In order to use UNC remote backups, if you are used to using default shares such as c $for backups, you need to add these accounts to the Backup Operators group of each member server. However, this is a very bad habit, the management of shares are for users with administrator privileges, in the daily operation and maintenance process, be sure to use shared folders. If you use a shared folder, gMSAs does not need to perform this step.

(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. Configure each service to use the group managed service account, leave the password blank and confirm.

Test scenario

1. Domain control modifies the group managed service account password

After the domain control modifies the password of the group managed service account, verify the connection of each copy, normal.

two。 Use script to switch between active and standby instances

After switching between master and slave instances using mov.ps1, the new master instance can run normally.

Note: be sure to patch gMSAs on Windows Server 2012 R2, otherwise you will encounter an inexplicable crash of the service configured with this account. See KB 2998082

Postscript:

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

0x7KDC_ERR_S_PRINCIPAL_UNKNOWNServer 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 "Server logs and network traces can be used to determine what service principal is actually being requested." And "Kerberos recognizes short host names as different from long host names." What is found in the log is the SPN of the short domain name. According to "Kerberos is case sensitive." It is found that the SPN of the short domain name with the error record is lowercase. Therefore, the lowercase short domain name SPN is manually registered. "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.

Then verify the connection, that is, Kerberos verification.

Therefore, it is important to verify that Kerberos authentication is used for remote connections.

So the question is, why not automatically register SPN, but register manually? The auto-registered SPN is automatically registered according to the address parsed by the FQDN. The machine name is capitalized, which is uppercase in the DNS, so the registered SPN is also uppercase. Then why do you need lowercase manual registration? I found that in order to test Read-scale availability groups (a new feature introduced in SQL Server 2017), lowercase domain name resolution was added to the host HOST:

That makes it impossible to use Kerberos authentication. Therefore, the lowercase SPN is removed from the domain control. Retry using Windows to authenticate the connection remotely, and it does use Kerberos authentication.

So let's review these two points again:

"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. "

The conclusion is that SPN auto-registration can use Kerberos authentication.

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/.

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.