During the installation of Microsoft SQL Server, you have two choices regarding the account used to start the “SQL Server” service:

• Local System of the computer.
• Service account, user account, created in Active Directory.

Microsoft recommends to use the service account instead of local system. However, when using service account, you can have this error in SQL Logs:
The SQL Server Network Interface library could not register the Service Principal Name (SPN)  for the SQL Server service. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos

Service Principal Name

For register the SPN, two solutions exist:

• Manual registration with setspn tool.
• Automatically registration used by SQL service.

We will only see the automatically registration, into 4 steps:

1. NTLM is currently in use.
2. Modify Computer object rights in Active Directory.
3. Modify Service account rights in Active Directory.
4. Verification and Kerberos authentication.

NTLM is currently in use

To check if the connection is using the NTLM protocol:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid;

Tips: if you execute this query from the SQL Server itself, it always displays NTLM. You need to execute it from another computer to get the correct status.

You also have these errors in the SQL Log:

Computer object Security rights

The service account must have the right to read and write Service Principal Name on the server object.

To do this, go to the computer object in Active Directory, select the Security tab and clic on Advanced:

Next, select Add:

In the Principal, enter the service account:

Check these rights to the service account:

• Validated write to service principal name.
• Read servicePrincipalName.
• Write servicePrincipalName.

Service account Security rights

In addition to rights on the server object, service account needs to write public information on itself.

To do this: Go to the service account in Active Directory, select the Security tab and select SELF:

Check these rights to the service account:

• Read public information.
• Write public information.

Kerberos authentication

To check the modification, you can re execute the query below.
But before this, you have to reboot the SQL Server and the SQL client where you ran the query in the first step.

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid;

After a reboot, you will find in the SQL Log:
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN)

And when you run the query:

You can find more information: http://msdn.microsoft.com/fr-fr/library/ms191153.aspx