Product:
Cognos BI 10.1.1
Windows 2008 R2 server
Active Directory
Microsoft SASS MSAS server
Symptom:
Get a log in dialog when you surf to Cognos connection after you setup SSO.
You need to ensure that you have done all the steps needed to get Kerberos to work with Cognos Bi and Windows 2008 servers. Here is a list of the steps needed, it can be that in your environment you need to do add additional steps.
Steps to activate Kerberos on a Windows 2008 R2 server:
Remove the use of “RemoteUser” from Cognos Configuration on the Cognos BI server.
Start Cognos Configuration
Go to security – authentication – your AD connection
Click on Advanced Properties
Mark the line singleSignOnOption = IdentityMapping
Click on remove
Click OK
Save and exit cognos configuration
Restart the Cognos Bi service.
Set the Cognos Gateway server to be trusted for delegation in Active Directory
Start Active Directory Users and Computers on a server (ADSIEdit)
Search for the Cognos Gateway server
On view menu mark “Advanced Features”
Right click on server and select properties.
Go to Delegation tab
Mark “ Trust this computer for delegation to any service ”
Click OK
Set the windows service account used by cognos service to be trusted for delegation in Active Directory
(to active delegation tab you must use SETSPN command:
SETSPN -S HTTP/Gatewayservername yourdomainname/servicename )
Start Active Directory Users and Computers on a server (ADSIEdit)
Search for the Cognos service account.
Right click on user account and select properties.
Go to Delegation tab
Mark “ Trust this user for delegation to any service ”
Click OK
Ensure all the Cognos servers and MSAS SSAS server are in the same domain, and that that the Microsoft Windows domain is set to native mode.
Check this in Active Directory Users and Computers on a server (ADSIEdit)
Select the domain and right click properties
In the General tab
Domain function level should be Microsoft Windows 2003 or 2008.
Ensure the end user is not set to be sensitive and not trusted for delegation in Active Directory
Start Active Directory Users and Computers on a server (ADSIEdit)
Search for the user account (that will run the reports)
On view menu mark “Advanced Features”
Right click on user account and select properties.
Go to Account tab
UnMark “ Account is sensitive and cannot be delegated ”
Click OK
On the client computer, start Internet Explorer and go to Internet Options under Tools menu.
Go to Advanced tab
Scroll down to security
Ensure that ” Enable integrated Windows Authentication ” is marked.
Click OK
Go to Security tab
Mark Local Intranet icon
Click on Sites
Click on Advanced
Enter the Cognos BI gateway server name at ” add this website to the zone”
Click Add
Click Close
Click OK
Click on Custom Level
Go to bottom under User Authentication
Under Logon select “automatic logon with current user name and password”
Click OK
Click OK
Ensure that you have installed the Microsoft SQL 2008 native client drivers on the Windows 2008 R2 server where Cognos BI are. You download it from http://www.microsoft.com/en-us/download/details.aspx?id=16978 and should have installed version 9.00.1399.6 of Microsoft SQL Server Native Client. (sqlncli.msi)
You need to install SQLSERVER2008_ASOLEDB10.MSI on the Cognos BI server.
Set the windows service account used by Cognos service to be local administrator on the SSAS server and administrator in the SSAS server and cubes. Ensure the Cognos Windows service account is member of the local administrator group on the SSAS server.
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.
To be able to use Cognos Framework manager to access a MSAS SSAS 2008 server and cube, the user starting framwork manager must have the Active Directory setting “trust for delegation” set. To active the delegation tab inside user and computers properties, you must set a SETSPN.
Run command like this for all users that should be using Frame work manager to MSAS cubes.
SETSPN -A HTTP/dummy domain\username
where you replace domain\username with the real domain name and the username of each user.
Then you can search for the user in Active Directory and on the Delegation tab set “trust this user for delegation to any service”.
See more at:
Usage: setspn [modifiers switch] [accountname]
Where “accountname” can be the name or domain\name
of the target computer or user account
Edit Mode Switches:
-R = reset HOST ServicePrincipalName
Usage: setspn -R accountname
-A = add arbitrary SPN
Usage: setspn -A SPN accountname
-S = add arbitrary SPN after verifying no duplicates exist
Usage: setspn -S SPN accountname
-D = delete arbitrary SPN
Usage: setspn -D SPN accountname
-L = list SPNs registered to target account
Usage: setspn [-L] accountname
In some cases the user who should be using Cognos Frame Work Manager need to have administrator rights or ”Act as part of the operating system” rights on the computer where he starts his Framework Manager client program.
Ensure that you use FQDN server names in Cognos Configuration on the Cognos BI servers
Start Cognos Configuration
Go to Environment tab
Change all servernames (e.g. Gateway URI) to be servername.domain.com from servername.
(change to your domain name)
Save and restart the Cognos BI service.
Run SETSPN -L servername for the Cognos BI server and the MSAS SSAS server.
It should list the FQDN name similar to below for the Cognos BI server.
Registered ServicePrincipalNames for CN=servername,CN=Computers,DC=corp,DC= company,DC=lan:
WSMAN/servername.corp.company.lan
WSMAN/servername
TERMSRV/servername.corp.company.lan
TERMSRV/servername
RestrictedKrbHost/servername
HOST/servername
RestrictedKrbHost/servername.corp.company.lan
HOST/servername.corp.company.lan
To check SETSPN on the SSAS server:
Enter the following at the command prompt:
SETSPN -L domain1\stomssqlserviceaccountname
You should have spn similar to this
MSOLAPSvc.3/sqlservername.corp.company.lan
MSOLAPSvc.3/sqlservername
MSSQLSvc/sqlservername.corp.company.lan:1433
MSSQLSvc/sqlservername:1433
To set value for the MSAS SQL server:
Log in as a ActiveDirectory DomainAdmin and enter the following command prompt;
setspn -A MSOLAPSvc.3/sqlservername.corp.company.lan domain1\stomssqlserviceaccountname
The windows service account that run the cognos service must be activated on the Cognos BI windows 2008 server to have this functions:
”replace a process level token”.
”Act as part of the operating system”
Log on to BISERVER (Cognos server) with the service account (that run cognos services)
Run the following command to reach local security settings: secpol.msc or gpedit.msc
Go to Local Policies (or Computer configuration – Windows settings – Security settings – local policies – User Rights Assignment).
Under User Rights Assignment, click on “Replace a process level token”
Add the service account
Under User Rights Assignment, click on “Act as part of the operating system”
Add the service account
Exit the tool
Reboot the server
Don’t get fooled by the “test data source”, it will always fail for external namespace as the Kerberos delegation is NOT run for testing the data source.
You must create a report to test the SSAS MSAS 2008 data, save the report in public folders samples and let different users test to run it.
To be able to create a framework manager package, you can create a data source SSAS that uses the cognos service account credentials to connect. This package often works better.
After the framework manager package is created and working, change the data source connection to use the AD external namespace.
Go to Cognos connection
Go to Cognos Administration
Click on configuration tab
Click on the data source you want to change, so you get one level down
Click on more for the data source you want to change
Click set properties
Click connection
Click “edit the connection string” icon
Here you can change authentication between
IBM Cognos software service credentials
and
An external namespace:
Select An external namespace:
and the AD you are using.
Click OK
Click OK
and go back and test your report again.
To troubleshoot Kerberos issues, you can download and install DelegConfig.v2.beta.zip, to get more help about the Windows setup off Kerberos.
http://blogs.iis.net/brian-murphy-booth/archive/2009/04/22/delegconfig-v2-beta.aspx