Kerberos is used as a network authentication protocol to provide secure process of authentication between client and server entities over an open network. In order to use Kerberos authentication with SQL server, users must have a Service Principal Name (SPN) filed with Active Directly. The Kerberos comprises of Key Distribution Center (KDC), client user and the server. KDC comes installed as a part of domain controller and helps to perform two functions:
- The authentication Service
- The Ticket-Granting service
When any user log into the network, the KDC requests a Ticket Grant Ticket from the Authentication Service from user’s domain. Afterwards, when client access to the network resources, it shows the TGT, authenticator and Server Principal Name of the respective server. Then it contact the TGS from the service account domain recover a new session ticket for further communication via network services. And since the target server formalizes the authenticator, it creates an access token for the user.
Benefits Of Authentication Kerberos
- Common Authentication: Using Kerberos authentication helps to validate the identity of server and client.
- AssuredAuthentication: It use only encrypted tickets instead of passwords in the ticket.
- IncorporatedAuthentication:Provides single log in access, user don’t have to log in again to access any service supported by Kerberos authentication.
Before a network connection is created, Kerberos provides a process for mutual authentication among-st entities. Also Kerberos uses a third party Key Distribution Center like Reliance so that generation and secure distribution of tickets and symmetric session keys can be generated.
Kerberos Authentication In SQL Server
SQL Server 2005 supports Kerberos indirectly by Windows Security Support Provider Interface (SSPI), when SQL use integrated authentication. Kerberos are used only when SQL Server allows SSPI to manage the authentication for protocol to use. If Kerberos cannot be used, in such case Windows fall it back as NTLM authentication. Having Kerberos authentication is far better than NTLM if taken security point of view wise. And for this it is important to know following are done:
- Both client and server machines should be a part of trusted Windows domains.
- The SPN (Service Principal Name) must be registered with Active Directly in SQL Server.
- The client is in running mode with TCP/IP, it must be enabled in client.
Registering an Service Principal Name (SPN) with SQL Server
The Service Principal Name is basically a process to map the principal name and Windows account which is used to start the server instance service. It is required because the client uses the server’s host name and TCP/IP port to compose and SPN. If users don’t perform the mapping process the Windows security layer fails to find the account related with the SPN and Kerberos authentication will not be used. During the process, the SQL Server 2005 will automatically try to register the SPN with Active Directory primarily if TCP/IP is enabled by user. Here the issue is only domain admin or local system account has the power to register an SPN, therefore in such case if service gets started under normal account, SQL Server fails to register the SPN for time being. The following entry will be received in error log:
The alternate method to register an SPN with SQL is having a domain administrator manually. The format for an SPN would be like this MSSQLSvc/FQDN:tcpport, where FQDN is domain name for server and tcpport is the TCP/IP port number. To register the SPN with Server, administrator needs to use SetSPN.exe tool which is available from the Windows server resource kit.
In such case if SPN exists already over server, it must be deleted before it re-registered. The whole process is carried out by domain admin using the command as setspn –D. Now to verify whether Kerberos Authentication is used, you can ask the sys.dm_exec_connections DMV and have a look under the Auth Scheme category. If the Kerberos is used by Server it will display “KERBEROS” else it will not.
Caution:
- If instance is set up to respond to another port, then in such case SPN needs to be deleted and recreated using the new port digits.
- If Server is configured with Dynamic IP address then, a new SPN must be configured each time whenever the Server gets started. Instead it is recommended to use the static IP address so that SPN need to be registered again and again.
Conclusion:
All I have explained about Kerberos and their existence in SQL server. They are used in a network environment to provide network security between client and server entities.
EmoticonEmoticon