Kerberos authentication for SQL Server Analysis Services (SharePoint Server 2010)
Published: December 2, 2010
In this scenario you do the following things:
Configure Analysis Service instances in the SQL Server 2008 R2 cluster to use Kerberos authentication
Verify that the client can authenticate with the cluster by using Kerberos authentication
Enabling Kerberos authentication for SQL Server Analysis Services is similar to SQL Server
If you are installing on Windows Server 2008, you may have 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|
Configure Active Directory
Create Service Principal Names (SPNs) for the Analysis Services instance
Verify SQL Kerberos Configuration
Connect to the Analysis Services instance in Excel 2010
Step-by-step configuration instructions
Configure Active Directory
For SQL Server Analysis Services to authenticate clients by using Kerberos authentication, you have to register a service principal name (SPN) on the service account that is running SQL Server. The SPN for a default Analysis Services instance uses the following format:
If you are using a named instance of Analysis Services, note that you cannot specify a port after the colon. If you do, it is interpreted as part of the hostname or domain name. Instead, you must use the actual instance name for all functionality to work correctly.
For more information about registering SPNs for SQL Server 2008, see http://support.microsoft.com/kb/917409.
This scenario assumes a default Analysis Services instance. We will configure the Analysis Services SPN on the Analysis Services service account (vmlab\svcSQLAS) with the following SetSPN command:
SetSPN -S MSOLAPSvc.3/MySQLCluster.vmlab.local vmlab\svcSQLAS
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 named instances:
Verify SQL Server Kerberos configuration
Once the SPN is configured, verify the Kerberos connection to the cluster by using Excel 2010.
Open Excel 2010 on the client computer by using a domain account that has access to at least one database in the Analysis Services instance and open a data connection to your Analysis Services instance by selecting the Data tab, clicking From Other Sources, and then clicking From Analysis Services.
In the Data Connection Wizard, type MySQLCluster in the Server name box, then click Next. If Kerberos authentication is working, then you can see all the databases that you already have the permission to see.
To use the AdventureWorks 2008 R2 sample databases, download from Microsoft SQL Server Community Projects & Samples and follow the installation instructions.
Open the event viewer on the database server (vmsql2k8r2-01). You should now be able to see an audit success in the security log similar to the one you see in the verification steps for Scenario 2, Kerberos authentication for SQL OLTP (SharePoint Server 2010).