Kerberos authentication for SQL Server Analysis Services (SharePoint Server 2010)

  • Article

 

Applies to: SharePoint Server 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

Note

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 (https://support.microsoft.com/kb/969083)

Configuration checklist

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:

MSOLAPSvc.3/<FQDN>

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.

MSOLAPSvc.3/<FQDN>:instanceName

For more information about registering SPNs for SQL Server 2008, see https://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.

  1. 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.

  2. 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.

    Note

    To use the AdventureWorks 2008 R2 sample databases, download from Microsoft SQL Server Community Projects & Samples and follow the installation instructions.

  3. 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).