Analysis Services and “Double Hop” Authentication

Working with Microsoft® SQL Server 2005 Analysis Services can be a challenge in a secure environment. For whatever reason, Microsoft® has chosen to not fully integrate Analysis Services and Kerberos out of the box, which means that administrators of Analysis Services must do a bit of work to get it to play nice when there are intermediate hosts, such as Windows SharePoint Portal Server or Internet Information Server involved.

Double Hop Authentication

In Analysis Services, a "Double Hop" occurs when the client is not directly connected to the Analysis Services Server, such as when remotely browsing a SharePoint site that is configured to retrieve data from Analysis Services (Like a Dashboard page with KPI indicators that get their data from a cube hosted on a remote Analysis Services instance). In order to correctly deal with double hop authentication, Kerberos (The underlying authentication mechanism used by Active Directory) must be able to properly pass the user's credentials to Analysis Services. In order to accomplish this, Analysis Services must be configured to utilize Kerberos properly, which is not done automatically when installing Analysis Services.

Configuring Analysis Services for use with Kerberos

The steps to configure Analysis Services to utilize Kerberos are detailed in Microsoft KB Article 917409, which can be read here: http://support.microsoft.com/kb/917409/en-us , however a quick overview of the process is:

1. Download and install the SetSPN utility (Either from the Windows Resource kit, or from http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en)
   
2. SETSPN is used to configure a "Service Principal Name", which is required by Kerberos
   
3. Create a Service Principal Name (SPN) for the Analysis Services Instance (on the AS server)
   
4. (If AS is running as a domain account) setspn.exe –A MSOLAPSvc.3/<fully qualified hostname> <Account Used to run Analysis Services>
   
5. (If AS is running as localsystem) setspn.exe –A MSOLAPSvc.3/<hostname> <hostname>
   
6. Configure Active Directory Settings
   
7. All users that are connecting to Analysis Services through the intermediate host need to be allowed to use Kerberos Delegation ("Account is sensitive and cannot be delegated" must be unchecked)
   
8. The "Account is trusted for delegation" setting must be checked for each user and each service account used in the process.
   
9. The "Trust computer for delegation" setting must be checked for each computer used in the process
   
10. Configure IIS Settings for the Virtual Directory hosting the application (or SharePoint site)
    
11. The Authentication must be set to "Integrated Windows Authentication" or "Basic Authentication"
    
12. The application protection level must be set to "High (Isolated)"
    
13. The impersonation level for COM+ must be set to "Delegate"
    
14. The COM+ application identity must be set to an account where "Account is trusted for delegation" is checked.
    
15. Connection strings must include SSPI=Kerberos
    
16. Connection strings CANNOT use IP Addresses
    

If you go through the above steps, Analysis Services should be configured to use Kerberos and should be able to participate in double-hop authentication. As more Analysis Services applications come online, this will be something that everyone has to worry about, so hopefully this article has helped alleviate the problem.

Visual Studio Team System - Database Professionals

During the PASS conference, I had the opportunity to sit down with Gert Drapers (DataDude himself!) and ended up getting a video recorded. If you would like to see this cheesy video, check it out here: http://supichoo.com/Gert_and_Ted/