Chapter 1 Securing Your Server and Network (9): use Kerberos for authentication, securingkerberos

Chapter 1 Securing Your Server and Network (9): use Kerberos for authentication, securingkerberos
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38263043

 

Preface:

 

In the Active Directory, two authentication mechanisms are available: NTLM and Kerberos. NTLM (nt lan Manager) is an authorization protocol based on the old encryption method. Microsoft does not recommend that you use it any more. Details visible: http://msdn.microsoft.com/en-us/library/cc236715.aspx

Kerberos is a free software protocol that was first developed by MIT (MIT) and introduced from Windows 2000. Use the switch of secured tickets (NO Terminology found at the moment) to identify the security of the client and server. Kerberos manages identity authentication by sharing the security encryption key. The client shares a symmetric key with the verification server ,. The verification server becomes the KDC (Key Distributor Center, Key Distribution Center) and runs as the service of the domain controller.

Upon logon, the client requests a Ticket Grant Ticket (TGT) from KDC. Then, KDC creates a TGT containing the Client ID and returns the session with the key to the client. The TGT is not long, usually only 8 ~ 10 hours. After expiration, the client cannot access resources.

Service ticket must contain the Service Principal Name (SPNs, Service subject Name) of the target resource (for example, SQL Server instance ). When KDC receives the request, it returns the service tag. This label will be used later for client requests to access the server. For more information, visit: http://technet.microsoft.com/en-us/library/cc772815.aspx

 

Implementation:

 

To use Kerberos, you must be in the domain environment and the server's SPNs must have been registered to Active Directory. If these conditions are met, Kerberos should be used by default. You can check in SQL Server:

SELECT auth_scheme, net_transport, client_net_address FROM sys.dm_exec_connections;

If the connection is from the same domain or a trusted domain authorized by NTLM, you need to study why Kerberos is not used. When SQL Server is started, it automatically registers its own SPNs to Active Directory. If the SQL Server service account does not have permissions, it cannot be created and Kerberos authorization cannot be used.

Run the following command in cmd or PowerShell to check whether the SPNs have been registered:

setspn.exe -L DOMAIN\<SQL service Account>

In my environment

You should see one of the information:

MSSQLSvc/SQL-A.Contoso.com: 1433 -- format: MSSQLSvc/<SQL Server machine Name>. <Domain Name>. com: <port number>

The check shows that this information does not prove successful. Therefore, the weekly renewal result of the previous figure is only authorized by NTLM. This account does not have the "write public information" permission. By default, if an SQL Server service account is installed by a domain user, the account does not have this permission.

Now, change the SQL Server service account to the domain administrator. Check again to see the expected results:

 

If no SPNs are registered, follow these steps:

1. In the domain controller's Administrative Tools, select ADSI Editor ]:

 

2. Connect to [default naming context], find the service account, right-click [properties], and select the [Security] Page:

 

3. Authorize SELF to Write Public Information and restart the SQL Server service:

Note that the local machine is installed and run with contoso \ mirroradmin, so select this account for authorization. After authorization, query again. You can see that Kerberos has been connected.

Principle:

 

The SPNs are the unique identifier of an instance and do not have an appropriate SPNs. Kerberos cannot verify a service and provide service tags to allow access by the client. If no SPNs exist, the only authentication method that can be used by the client is NTLM, And the SPNs must be installed in the Active Directory and have the KDC role.

Fixed format: <service>/

 

For more information:

 

If you do not want to grant [Write Public Information] to the AD, or for some reason, the SPNs cannot be registered, You can manually execute the following statement to create:

Setspn.exe-A MSSQLSvc/SQL-A.Contoso.com: <port> Contoso \ SQL-A -- where the SQL-A is the machine name and the contoso is the domain name

 

For Kerberos issue detection, read the following article:

Http://blogs.technet.com/ B /askds/archive/2008/05/14/troubleshooting-kerberos-authentication-problems-name-resolution-issues.aspx