Microsoft Analysis Services Data Sources

Authentication using Signons

When you want to store and maintain credentials to authenticate to Microsoft Analysis Services data sources in IBM Cognos software, use a signon when you create the data source. You can define a signon which is used by everyone (default) or you can grant access to specific users. You can also create multiple signons and use permissions to grant access for specified users, groups or roles.

The signon stores valid Windows domain credentials, which are used to authenticate to Analysis Services. They must be specified in the following syntax:

<DOMAIN>\<USERNAME>

For Microsoft Analysis Services 2005 and 2008, users with credentials should be a part of the local OLAP users group that exists on the computer where Analysis Services is running. This group, which is created when Analysis Services is installed, is called SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER.

On every installation of an IBM Cognos Application Tier component, ensure that IBM Cognos software is run as a LocalSystem built-in account or that IBM Cognos software is run as a valid domain account which has been granted the Act as part of the operating System privilege in the local security policy.

IBM Cognos users must be granted read and execute permission for that signon.

Authentication using Service Credentials

When you want to use the credentials of the account that is executing the IBM Cognos service to authenticate to Microsoft Analysis Services, use service credentials. Every connection to Microsoft Analysis Services data sources uses the service credentials regardless of which user executes the request.

To use service credentials, IBM Cognos software must be started as a Windows service. The service must be run as a valid Windows domain user. The built-in accounts of LocalSystem or NetworkService are not applicable. For information on how to start the IBM Cognos service under an account, see information about configuring a user account or network service account in the IBM Cognos Business Intelligence Installation and Configuration Guide.

The account running the IBM Cognos service must fulfill the following requirements:

• The account must either be a member of the same Active Directory Forest as Analysis Services or Forest trust must be established for cross-forest setups.
• The account must be granted the Log on as a service privilege in the local security policy of all Windows computers running IBM Cognos Application Tier components
• For multi-node setups, the same account must be used on all computers running IBM Cognos Application Tier components.
• For Microsoft Analysis Services 2005 and 2008, the service account must be granted sufficient privileges in SSAS security to attach to the desired cubes and retrieve data.
• For Microsoft Analysis Services 2005 and 2008, the account should be a part of the local OLAP Users group, existing on the computer where Analysis Services is running. This group, which is created when Analysis Services is installed, is called SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER.

Authentication using an External Namespace

If you want IBM Cognos users to access Microsoft Analysis Services data sources with their own credentials (user pass-through authentication, signon), use an external namespace. The credentials that are used to authenticate to Analysis Services are taken from the specified namespace to which the user authenticated previously.

The credentials provided by a user who is logged on to the namespace are passed to Analysis Services. Due to the authentication methods supported by Analysis Services, you can only choose a namespace of type Microsoft Active Directory.

Depending on how the user is authenticated to the Active Directory namespace specified for external namespace authentication, you can have the following sign-on setups that provide a seamless user experience:

• If a user authenticated explicitly by providing a domain user name and a password, pass-through authentication is possible. The domain credentials that are provided are passed to Analysis Services.
• If a user authenticated to the Active Directory namespace by a signon which is not based on Kerberos, user pass-through authentication is not possible. This applies to setups where IBM Cognos software is integrated with any third-party portal or where the Active Directory Namespace is configured for identity mapping mode.

To configure user pass-through authentication to Analysis Services, ensure that the following conditions are met:

• All computers running IBM Cognos Application Tier components must run IBM Cognos BI as a Windows service under a valid domain account or LocalSystem.
• All computers running IBM Cognos software must have a Microsoft Windows server operating system. (Pass-through authentication is not supported for Windows XP.)
• The computers running Analysis Services and IBM Cognos software must be part of the same Active Directory Forest.
• The domain account (user account) or the computer account (LocalSystem) must be trusted for delegation.
• All user Windows accounts that require access to Analysis Services through IBM Cognos software must not have the Account is sensitive and cannot be delegated property set.

Analysis Services are configured for Kerberos authentication. For details, contact your Analysis Services Administrator.

For SSAS 2005 and SSAS 2008, Windows accounts for all users must be a part of the local OLAP users group on the computer where Analysis Services is running. This group, which is created when Analysis Services is installed, is called SQLServerMSASUser$<SERVERNAME>$MSSQLSERVER.

Note that there is a Microsoft issue that hinders user pass-through authentication when Analysis Services and the clients accessing it are both run on AES-aware operating systems (Windows 2008, Microsoft Vista, Windows 7). Refer to Microsoft documentation for details.

Note that you cannot test a data source which is configured for external namespace authentication. To verify that it is working, access the data source in a query.

Framework Manager Considerations

IBM Cognos Framework Manager accesses Analysis Services data sources directly without using the Report or Metadata services. This has important implications, especially for configurations with user pass-through authentication for Analysis Services.

If Kerberos-based signon is enabled for the Active Directory namespace that is configured, as an external namespace authentication source for the Analysis Services data source, ensure that the users running Framework Manager meet the following criterion:

• has the Act as part of the operating System privilege set in the local security policy on the computer running Framework Manager or is a member of the Local Administrators group on the Framework Manager computer with the log on locally privilege
• is trusted for delegation

Multidimensional Expression (MDX) Queries

You must install the following Microsoft Office components for Microsoft Excel Visual Basic for Applications (VBA) functions, such as ROUNDDOWN for MDX queries:

• Office Excel
• Microsoft Visual Basic for Applications (a shared feature in Office)

Install these components on the IBM Cognos Server for MSAS and on the Analysis Services server computer for SSAS 2005 or SSAS 2008, then restart the server machine.