This pattern uses IIS, Microsoft SQL Server, and Windows AD. No other configurations have been tested for this pattern.
Prerequisites and additional information
The following checklist shows the prerequisite configurations, system rights, and tools you'll need to successfully set up the OLAP connection.
Microsoft Internet Information Services 7.5 ( IIS) installed and configured.
Windows Server 2008 R2.
Microsoft SQL Server 2008 R2 with MSAS installed.
Service Account created for setting up the SPN for MSAS.
Service Account granted permissions and assigned a role on MSAS.
IIS And MS SQL Server are on the same domain.
- To make a connection to MSAS through TCP/IP, the OLE database must be installed on the same machine that hosts the IIS server. You
will need the drivers for Microsoft Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2, which are
available at: http://www.microsoft.com/en-us/download/details.aspx?id=16978.
For information about setting up IIS with Windows 2003, refer to the following Microsoft TechNet article at http://technet.microsoft.com/en-ca/library/gg492140.aspx
- For general information about configuring IIS, see these Microsoft articles:
- For a Kerberos configuration checklist, refer to the following blog article: http://blogs.msdn.com/b/psssql/archive/2010/06/23/my-kerberos-checklist.aspx
- For general information about configuring IIS, see these Microsoft articles:
The workflow involves the following tasks:
- Copying the required files from the MSAS server to the IIS server.
- Creating an Application Pool.
- Creating a Virtual Directory.
- Setting up IIS Authentication and adding the requisite extension.
- Setting up a service account for MSAS and IIS, and creating the Service Principal Name (SPN).
- (SPN) Defining the OLAP connection in the Central Management Console (CMC).
- Configuring MDAS (Multi Dimensional Analysis Services) for the Adaptive Processing Server(s).
The steps for each of the tasks are shown here. For more background information on the tasks, see http://msdn.microsoft.com/en-us/library/gg492140.aspx.
To create a connection from Analysis for OLAP through XMLA
To copy the required files
1) On the Web Server, under the patch c:\inetpub\wwwroot, create a new folder named "OLAP".
2) Go to the folder named "ISAPI", copy the contents of the ISAPI folder, and paste to the "OLAP" folder you created on the previous step.
For example, on a standard installation of SQL Server 2008 Release 2, copy all files inside
C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi, and paste them to c:\inetpub\wwwroot\olap.
The OLAP folder will have a Resources folder containing two files: msmdpump.dll and msmdpump.ini.
To create an Application Pool
1) Click Start > All Programs > Administrative Tools > Internet Information Services (IIS) Manager.
The "Internet Information Services (IIS) Manager" dialog box opens.
2) In the "Connections" area, expand your server name (VANTGVMWINPB07.BI4PATTERN.COM).
The "Application Pools" node appears.
3) Right-click Application Pools, and select Add Application Pools.
The "Add Application Pool" dialog box opens.
3) In the Name box, type OLAP, and from the Managed Pipeline mode list, select Classic.
4) In the .NET Framework version box, select .NET Framework v2.0.50727.
5) Right-click the OLAP application pool and select Advanced Settings.
The "Advanced Settings" dialog box opens.
6) In the “General” area, set Enable 32-Bit Applications to False.
7) In the “Process Model” area, set the Identity to NetworkService, and click OK.
To create a Virtual Directory
1) In IIS Manager, expand Sites.
2) Right-click Default Web Site (or the name of the site you are using), and select Add Virtual Directory.
The "Add Virtual Directory" dialog box opens.
3) In the Alias box, type OLAP.
4) In the Physical Path box, browse to c:\inetpub\wwwroot\olap, and then click OK.
5) When the virtual directory has been added, right-click the olap virtual folder, and select Convert to Application.
To set up IIS authentication
In this section, you configure further the MSAS virtual directory you just created. You will specify an authentication method, and then add a
1) In IIS Manager, open Site, open Default Web Site, and then select the olap Virtual directory.
2) In the "IIS" area, double-click Authentication.
3) Enable Windows Authentication.
Windows authentication is the most secure and recommended authentication. It must be enabled to configure SSO.
Note: Anonymous Authentication must be set to disabled.
4) Click on the OLAP vitrual directory to open the main page. Double-click Handler Mappings.
The "Handler Mappings" dialog box opens.
5) Right-click anywhere on the page, and select Add Script Map.
The "Add Script Map" dialog box opens.
6) In the Add Script Map dialog box, do the following:
a) In the Request path box, type *.dll.
b) In the Executable box, type c:\inetpub\wwwroot\OLAP\msmdpump.dll
c) In the Name box, type OLAP.
7) Click Request Restrictions.
The "Request Restrictions" dialog box opens.
8) On the Verbs tab, ensure All verbs is selected.
9) Click OK, and click OK again to finish adding the script mapping.
10) When prompted to allow the ISAPI extension, click Yes.
To set up the Service Principal Name (SPN) to enable SSO with MSAS and IIS to Analysis for OLAP
When a connection is made to a computer that is running Microsoft SQL Server 2008 Analysis Services or Microsoft SQL
Server 2005 Analysis Services, and that connection involves a double-hop authentication scenario, you must use Kerberos
as the authentication protocol. For example, in a double-hop authentication scenario, a client computer may pass the logon
credentials to a computer that is running Microsoft Internet Information Services (IIS). The computer that is running IIS must then
pass the logon credentials to the Analysis Services server.
This SPN you create here will establish the Kerberos communication between MSAS and the requests from IIS.
- Ensure the servers hosting MSAS and ISS belong to the same Active Directory domain. For example, this pattern has been using
BI4PATTERN.COM as the domain.
- Ensure a MSAS_PATTERN Service Account is created for running the SSAS Service and for SSO.
- Create a
msasuseruser account to test for SSAS.
- Ensure the Service Account and the server hosting MSAS are both enabled for Delegation.
- Under the settings for local policy, add the Service Account.
- Ensure Kerberos authentication is set up as shown in this Microsoft KB article: http://support.microsoft.com/kb/917409.
This pattern uses a Service Account named
MSAS_PATTERN to run the SSAS service.
1) Click Start > Administrative Tools > Active Directory Users and Computers.
The "Active Directory Users and Computers" dialog box opens.
2) Click Service Accounts.
3) On the Delegation tab, select Trust this user for delegation to any service (Kerberos only).
4) To create the SPN account, in a command prompt window, type the following:
Setspn.exe -S MSOLAPSvc.3/<Fully_Qualified_domainName>.<OLAP_Service_Startup_Account>
Replace <Fully_Qualified_domainName> with the fully qualified domain name and <OLAP_Service_Startup_Account> with your
OLAP Service Account.. This pattern uses the following settings:
* Service Account:
* MSAS SERVER:
* IIS SERVER:
5) To create the account for the IIS server, in a command prompt window, type a command for the Fully Qualified Domain Name and a
command for the Net Bios Name as follows:
setspn -s http/vantgvmwinpb07.BI4PATTERN.COM vantgvmwinpb07.BI4PATTERN.COM
setspn -s http/vantgvmwinpb07.BI4PATTERN.COM vantgvmwinpb07
6) To verify that the SPN has been created, do the following:
a) In a command prompt window, type the following command:
setspn -L vantgvmwinpb07
b) In a command prompt window, type the following command:
setspn -L MSAS_PATTERN
You are now able to create an OLAP Connection from the CMC to MSAS 2005-08 for use with SSO, provided you have set up
SSO with BI Launchpad. You can otherwise test your configuration by using Windows AD Authentication manually.
To configure MDAS and the hosting Adaptive Processing Server (APS)
To set up end-to-end SSO with Microsoft Analysis Server and BI 4.0 Analyis Edition for OLAP you need to create keytab file and APS
hosting the MDAS server on BI platform. Modifications need to be made to ensure the APS hosting to the MDAS recognizes
the following files:
Krb5.ini. You will add extra parameters to the
The keytab file needs to be copied to C:\Windows Folders where the MDAS server is being deployed.
This section shows how to do the following steps:
- Create the keytab file with the ktpass command.
- Add the
- Ensure the APS that is hosting the MDAS recognizes the
To create the keytab file using the ktpass command
In a command prompt window, type the following:
ktpass -out bosso.keytab -princ service-account-spn@REALM -mapuser service-account-<name@REALM --pass service-account-password
The keytab for the Windows pattern will look as follows
To add the
server parameters to the bsclogin.conf file
bscLogin.conf file must contain both
server (com.businessobjects.security.jgss.accept) configurations.
1) In a text editor, open the
bsclogin.conf file, and ensure it contains the following client parameters:
2) Below the
client parameters, add the following for the server parameters:
To ensure the APS that is hosting the MDAS recognizes
The Adaptive Processing Server (APS) running the Multi Dimensional Analysis Service (MDAS) must be set to recognize the configuration files
krb5.ini. The path to
bscLogin.conf must always be specified, and by default the APS searches for
C:\Windows. However, it is recommended to explicitly specify the search locations, in case the default search location is changed by third-party software.
1) Add the following argument to the command line of APSs running the MDAS service:
To verify the SSO connection from BI Launchpad
Test the connection from the BI Platform CMC OLAP connection.
1) To open the CMC, click Start > All Programs > SAP BusinessObjects BI platform 4.0 > SAP BusinessObjects BI platform > SAP BusinessObjects BI platform Central Management Console.
The login page appears.
2) After you have logged in to the CMC, in the Organize list, click OLAP Connections.
3) Click the New connection icon.
4) In the Name box, type a name for the connection, and in the Description box provide additional details.
5) In the Provider list, choose a data provider.
For SSAS, currently Microsoft Analysis Services 2005 and Microsoft Analysis Services 2008 are supported.
6) In the Server Information box, enter the URL path to
If you have been using the examples suggested in this pattern, the URL will be
http:<IIS servername>/olap/msmdpump.dll, where <IIS servername> is the name of the IIS server that has been configured.