betterITsolutions

What follows are the instructions for configuring Kerberos for various aspects of SQL (this part) and SharePoint (Part 3, coming soon). This is not necessarily a hard and fast set of instructions but a collection of steps known to prove working results for Microsoft Office SharePoint Server (MOSS) 2007 with SP1 and the Infrastructure Update running on Windows Server 2003 R2 with SP2 and connected to a Microsoft SQL Server 2005 Database Engine. Configuration may be slightly different for Windows Server 2008 (and therefore later versions of IIS than 6.0). Note that the infrastructure update for MOSS is necessary if you require all aspects of your Shared Services Provider (SSP) to use Kerberos. These instructions should also be fine if you are running MOSS SP2.

Enabling Kerberos for the SQL Server Database Engine

To enable Kerberos for SQL Server, it is simply a case of mapping the correct SPNs to Active Directory user accounts. Up to four commands should be run:


setspn –A MSSQLSvc/sqlserver yourdomain\SQLServerServiceAccount
setspn –A MSSQLSvc/sqlserver.yourdomain.com yourdomain\SQLServerServiceAccount
setspn –A MSSQLSvc/sqlserver:1433 yourdomain\SQLServerServiceAccount
setspn –A MSSQLSvc/sqlserver.yourdomain.com:1433 yourdomain\SQLServerServiceAccount


This is for a default instance on port 1433. If the port number has been manually changed or you are using a non-default instance then only two SPNs are required but you must use the two commands above containing the port number and substitute your non–default port number. Also ensure that the non–default instance is properly configured to use a fixed port number.

Two less SPNs are required in this case because clients can no longer connect by simply using the computer name, they must specify the port number so any SPNs without port numbers would simply never be passed.

We can check that Kerberos is now working by connecting to SQL using the Management Studio from another machine on the same domain. Open the Management Studio, connect to the Database Engine and look in the Security log on the SQL Server. You should see a 540 success event containing the following kind of information:


Event Type: Success Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 540
Date: 09/07/2009
Time: 12:17:37
User: DOMAIN\UserName
Computer:
Description:
Successful Network Logon:
User Name:
Domain:
Logon ID: (0x0,0x3FF14)
Logon Type: 3
Logon Process: Kerberos
Authentication Package: Kerberos
Workstation Name:
Logon GUID: {cc8d6921-4c0d-e68c-8b1b-ba2fcdf4836f}
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: 10.11.12.3
Source Port: 1150


Note that user, domain and computer names have been deliberately removed.

This is all that is required for the SQL Database Engine to use Kerberos. It always prefers to use Kerberos by default but if you manually choose the accounts used to run your services (which is usual) then you have to add the SPNs to Active Directory yourself for it to work. According to MSDN: For Win32 services, a service installer specifies the logon account when an instance of the service is installed. The installer then composes the SPNs and writes them as a property of the account object in Active Directory Domain Services. If the logon account of a service instance changes, the SPNs must be re-registered under the new account.

Using Kerberos with other SQL Components

In addition, if you wish to connect to other SQL components using Kerberos (such as Analysis Services) then you will require further SPNs. The commands for adding SPNs for use with Analysis Services are as follows:


setspn –A MSOLAPSvc.3/sqlserver:instance yourdomain\SQLServerAnalysisServicesServiceAccount
setspn –A MSOLAPSvc.3/sqlserver.yourdomain.com:instance yourdomain\SQLServerAnalysisServicesServiceAccount


Note that this is a little different to the SPNs for the Database Engine because, rather than a port number, you specify the instance name (if non–default, otherwise leave blank). SQL Server Analysis Services can only operate as a named instance in SQL Server 2005 (it is not supported in SQL Server 2000).

In the next part, I'll move on to talking about configuring Kerberos for SharePoint.