Kerberos authentication for SQL OLTP (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010

In this scenario we walk through the process of configuring Kerberos authentication for the SQL Server cluster in our sample environment. Once that process is complete, we validate that SharePoint Server services are authenticated with the cluster by using the Kerberos protocol.

In this scenario, you do the following things:

• Configure an existing SQL Server 2008 R2 cluster to use Kerberos authentication

• Verify that the client can authenticate with the cluster by using Kerberos authentication

• Create a test database and sample data to be used in later scenarios

Note

It is not required to use Kerberos authentication for SQL Server for core SharePoint Server data services (for example, connections to platform databases). The sample environment has a sole SQL Server cluster that hosts additional sample databases used in later scenarios. For delegation to work correctly in these scenarios, the SQL Server cluster must accept Kerberos authenticated connection.

Note

If you are installing on Windows Server 2008, you may need to install the following hotfix for Kerberos authentication:
A Kerberos authentication fails together with the error code 0X80090302 or 0x8009030f on a computer that is running Windows Server 2008 or Windows Vista when the AES algorithm is used (https://support.microsoft.com/kb/969083)

Configuration checklist

Area of configuration	Description
Configure DNS	Create DNS (A) host records for the SQL Server cluster IP
Configure Active Directory	Create Service Principal Names (SPNs) for the SQL Server service
Verify SQL Server Kerberos configuration	Use SQL Server Management Studio to query SQL connection metadata to ensure the Kerberos authentication protocol is used

Scenario environment details

This scenario demonstrates a SharePoint Server farm configured to use a SQL alias for a connection to a SQL Server cluster that is configured to use Kerberos authentication.

Step-by-step configuration instructions

Configure DNS

Configure DNS for the SQL Server cluster in your environment. In this example we have one SQL Server cluster, MySqlCluster.vmlab.local, running on port 1433 at cluster IP 192.168.8.135/4. The cluster is Active/Passive with the SQL Server database engine running on the default instance of the first node.

For general information about how to configure DNS, see Managing DNS Records.

In this example, we configured a DNS (A) record for the SQL Server cluster.

Note

Technically, because SQL Server SPNs include an instance name (if you are using the second-named instance on the same computer), you can register the DNS host for the cluster as a CNAME alias and avoid the CNAME issue described in Appendix A, Kerberos configuration known issues (SharePoint Server 2010). However, if you choose to use CNAMEs, you have to register an SPN using the DNS (A) record host name for the CNAME aliases.

Configure Active Directory

For SQL Server to authenticate clients using Kerberos authentication, you have to register a service principal name (SPN) on the service account that is running SQL Server. Service principal names for the SQL Server database engine use the following format for configurations that are using the default instance and not a SQL Server named instance:

MSSQLSvc/<FQDN>:port

For more information about registering SPNs for SQL Server 2008, see Registering a Service Principal Name.

In our example, we configured the SQL Server SPN on the SQL Server database engine service account (vmlab\svcSQL) with the following SetSPN command:

SetSPN -S MSSQLSVC/MySQLCluster.vmlab.local:1433 vmlab\svcSQL

SQL Server named instances

If you use SQL Server named instances instead of the default instance, you have to register SPNs specific to the SQL Server instance and for the SQL Server browser service. See the following articles for more information about configuring Kerberos authentication for names instances:

• Registering a Service Principal Name

• An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server 2005 Analysis Services or of SQL Server 2005

SQL aliases

As a best practice, when building your farm you should consider using SQL aliases for connections to your SQL Server computer. If you choose to use SQL aliases, the Kerberos SPN format for those connections does not change. You continue to use the registered DNS host name (A record) in the SPN for SQL Server. For example, if you register an alias "SPFARMSQL" for "MySQLCluster.vmlab.local" the SPN when you are connecting to SPFarmSQL remains "MSSQLSVC/MySQLCluster.vmlab.local:1433".

Verify SQL Server Kerberos configuration

When DNS and Service Principal Names are configured, you can reboot the computers that are running SharePoint Server and verify that SharePoint Server services now authenticate with SQL Server by using Kerberos authentication.

To verify the cluster configuration

1. Reboot the computers that are running SharePoint Server — This action restarts all services and forces them to re-connect and re-authenticate by using Kerberos authentication.

2. Open SQL Server Management Studio and run the following query:

   Select 
      s.session_id,
      s.login_name,
      s.host_name,
      c.auth_scheme
   from
   sys.dm_exec_connections c
   inner join
   sys.dm_exec_sessions s
   on c.session_id = s.session_id
   

   The query returns metadata about each session and connection. The session data helps identify the connection source, and the session information reveals the authentication scheme for the connection.

3. Verify that the SharePoint Server services are authenticating by using Kerberos authentication. If Kerberos authentication is configured correctly, you see Kerberos in the auth_scheme column of the query results.

Create a test SQL Server database and test table

To test delegation across the various SharePoint Server service applications covered in the scenarios in this document, you have to configure a test data source for those services to access. In the final step of this scenario, you configure a test database called "Test" and a test table called "Sales" to be used later.

1. In SQL Server Management Studio, create a new database called "Test". Keep the default settings when creating this database.

2. In the Test database, create a new table with the following schema:

   Column Name	Data Type	Allow Nulls
   Region	nvarchar(10)	No
   Year	nvarchar(4)	No
   Amount	money	No
   RowId	int	No

3. Save the table with the name "Sales".

4. In Management Studio, populate the table with test data. The data itself does not matter and does not affect the function of later scenarios. A few rows of data will suffice.