Kerberos authentication for SQL OLTP (SharePoint Server 2010)
Applies to: SharePoint Server 2010
Topic Last Modified: 2011-08-05
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
|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.|
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 (http://support.microsoft.com/kb/969083)
|Area of configuration||Description|
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
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.
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.
|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.|
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:
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
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:
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".
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
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.
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.
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.
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.
In SQL Server Management Studio, create a new database called "Test". Keep the default settings when creating this database.
In the Test database, create a new table with the following schema:
Column Name Data Type Allow Nulls
Save the table with the name "Sales".
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.