Chapter 1 Securing Your Server and Network (9): Using Kerberos for authentication

Original source: http://blog.csdn.net/dba_huangzj/article/details/38332605, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

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

Objective:

In Active Directory, there are two types of authentication mechanisms: NTLM and Kerberos. where NTLM (NT LAN Manager) is an authorization agreement based on the legacy encryption method, Microsoft does not recommend reuse. Details Visible: http://msdn.microsoft.com/en-us/library/cc236715.aspx

Kerberos is a free software protocol that has been first developed by MIT and introduced from Windows 2000. Secure identification of clients and servers through the conversion of secured tickets (temporary discovery of professional terminology). Kerberos manages authentication with a shared security encryption key, relative to the use of hashed passwords. The client and the authentication server share a symmetric key. Where the authentication server becomes the KDC (key Distributor Center, Key Distribution center), and the service runs as a domain controller.

At logon, the client requests a Ticket Grant Ticket (TGT) from the KDC, and the KDC creates a TGT that contains the client identity and returns the session with the key to the client. The TGT's lifespan is not long, usually only 8-10 hours. After it is obsolete, the client cannot access the resource.

Service ticket must contain the services Principal name (SPN, Service principal name) of the target resource (such as an instance of SQL Server). When the KDC receives the request, the service tag is echoed. This tag will be used later for client requests to access the server. More information can be accessed: http://technet.microsoft.com/en-us/library/cc772815.aspx

Realize:

With Kerberos, you need to be in a domain environment, and the server's SPN must already be registered to active Directory. If these conditions are met, Kerberos should already be used by default and can be checked 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 that uses NTLM authorization, you need to investigate why Kerberos is not being used. When SQL Server starts, it attempts to automatically register its own SPN in Active Directory, and if the SQL Server service account does not have permissions, the SPN cannot be created and Kerberos authorization will not be available.

To check if the SPN is registered, you can enter the following command in CMD or PowerShell:

Setspn.exe-l Domain\<sql Service account>

Under My environment

You should see one of these messages:

mssqlsvc/sql-a.contoso.com:1433--format: Mssqlsvc/<sql server machine name >.< domain name >.com:< port number >

The check is visible without this information and proves unsuccessful, so the result of the previous graph Zhou Yi only NTLM authorization. This is usually because the account does not have the "write Public information" permission. By default, the SQL Server service account will not have this permission if it is installed by a domain user.

Now replace the SQL Server service account with the domain administrator and check again that the results are expected:

If you find that you have not registered an SPN, you can do so using the following steps:

1. In the administrative tools for the domain controller, select ADSI Edit:

2. Connect to the default naming context, locate the service account and right-click Properties, and then select the Security page:

3. Grant "Write Public Information" to self and restart the SQL Server service:

Note that this machine is installed and running with Contoso\mirroradmin, so choose this account authorization here. After the authorization is complete, again inquires, can see already has the Kerberos to be connected.

Principle:

An SPN is a unique identifier for an instance, and there is no appropriate spn,kerberos to validate a service and provide a service tag to allow client access. So if there is no SPN, the only authentication method that the client can use is NTLM, and the SPN must be installed in the Active Directory and has the KDC role.

The fixed format of the SPN is:<service>/

More information:

If you do not want to grant "write Public Information" to the ad, or for some reason the SPN cannot be registered, you can manually execute the following statement creation:

Setspn.exe-a mssqlsvc/sql-a.contoso.com:<port> contoso\sql-a  --where sql-a is the machine name, Contoso is the domain name

For Kerberos problem detection, you can read the following article:

http://blogs.technet.com/b/askds/archive/2008/05/14/ Troubleshooting-kerberos-authentication-problems-name-resolution-issues.aspx