Analysis Office 2.x allows you to use SAML Single Sign On over the BI platform to connect to your HANA systems. The blog post Setting up SAML SSO between Analysis Office 2.x to HANA SP9 from Jyotish Gogoi already describes the necessary configuration steps in HANA and BI Platform very well.
But what if you carefully performed all the steps and it still does not work as expected? How to do troubleshooting and find the root cause?
I want to share with you the most prominent pitfalls during SAML setup and provide ways to discover wrong configurations.
❗ Correction in Analysis Office for HANA SP 10
If you have installed HANA SP 10 you probably get the following error during logon procedure in Analysis Office:
This is a bug in Analysis Office in combination with HANA SP 10 (and later) which is resolved with note 2232963. But anyway this error text may also appear due to some other root causes. So please first review the following instructions to be really sure that everything is configured correctly.
ℹ Check HTTPS/SSL connection to HANA
When you get this error message in Analysis Office please ensure that the HTTPS/SSL configuration is correct:
“The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
>> General .NET Exception:
The remote certificate is invalid according to the validation procedure.”
Open a browser (IE or Chrome) on the maschine where Analysis Office is installed. Navigate to the HANA XS starting page by using the HTTPS/SSL connection:
If SSL is configured correctly then you will get the following result:
If SSL is not configured correctly you will see something like that:
In the error case please ensure that you have configured SSL correctly on HANA server (see Configure HTTPS (SSL) for Client Application Access – SAP HANA Administration Guide – SAP Library) and that the servers certificate (or one of its root certificates) is part of the Windows trust store.
ℹ Check BI platform configuration (CMC)
The server name you define in the “OLAP Connections” area must be identical with the host name you enter in the “HANA Authentication” application. You should use the fully qualified host name of the HANA server.
Both in “OLAP Connections” and in “HANA Authentication” application you have to specify the HTTPS port of the HANA server. The default HTTPS port is 43<INSTANCE_NUMBER>, e.g. for instance number 01 the HTTPS port is 4301. Using SAML requires the SSL protocol when connecting to HANA so entering the HTTP port (e.g. 8001) will not work.
ℹ Check HANA XS configuration
Check HANA user authorizations
Your HANA user needs specific privileges in HANA. See note 2097965 for all details. If some of the privileges are missing you will get a HTTP 403 error during logon in Analysis Office:
Analysis Office executes the following URL when trying to authenticate to HANA:
You have to enter the same host name and port that you used in the “OLAP Connections” area in CMC. When executing the URL in the browser you are probably asked for a HANA user and password. Please execute the URL in a browser (IE or Chrome) on the machine where Analysis Office is installed. As a result there should be no error response code from server but a valid JSON response. If this is the case then the HANA user has all the needed rights to access the InA (Information Access) component in HANA. InA is the interface that is used by Analysis Office 2.x to query the multi dimensional data from HANA.
Restart HANA server
After the IDP certificate from BI Platform has been imported into HANA trust store you have to restart the HANA server. Otherwise you may get this error in Analysis Office when trying to logon via SAML to HANA:
Probably there is some inconsistency in the SAML configuration in HANA. You can execute the following SQL statements in HANA Studio to see what is really stored in the HANA configuration tables and views. You should use the SYSTEM user for that. If some configuration is wrong you can use HANA XS Admin Tools or HANA Studio to correct it.
select * from "PUBLIC"."SAML_PROVIDERS" ; select * from "SYS"."SAML_USER_MAPPINGS" ; select * from "_SYS_XS"."RUNTIME_CONFIGURATION" ; call "HANA_XS_BASE"."sap.hana.xs.admin.db::metadataParams" (?)
01. Identity Providers
The statement should return the identity provider that you have defined in HANA XS Admin tool. The name must be the same as declared in “HANA Authentication” application in CMC.
02. User Mappings
There should be at least one row in the result set containing a user mapping from your BIP user (that you use to logon in Analysis Office) to the respective HANA user.
Error message in case of a wrong configuration:
Error message in xsengine trace on HANA: “Assertion authentication failed with reason: User is not allowed to use SAML(StatusCode: , StatusMessage: )”
Important: the SAML configuration of the HANA user is case sensitive. For example if the BI Platform user is named “MyUser01” then you have to enter “MyUser01” in the SAML configuration of the HANA user.
03. Runtime Configuration
There should be a row in the result set where the SAML authentication method is enabled for the InA service and the correct identity provider is used. In my example I enabled SAML for the “sap.bc.ina.service.v2” package.
Error message in case of a wrong configuration:
04. Service Provider Name
This statement should return one row where the SERVICE_PROVIDER_NAME column contains “spId”. This is a limitation of the BI Platform where the HANA service provider has to be named exactly like that. See SAP note 2169386 for more details when this limitation is resolved.
If you come to this point and still the logon via SAML is not working the next option would be to have a look at the traces written by Analysis Office and HANA.
In case you get an error when logging on via SAML to HANA you can check the Analysis Office trace files for a detailed error message. Please set the log severity to “Support”, restart Excel and repeat the scenario. For more details about how to enable tracing in Analysis Office and how to find the trace files see note 2129389.
Mostly the HANA server traces contain more detailed error information. You first have to set the trace level of the authentication components to DEBUG in order to see all details. Here is an example trace level configuration:
After that repeat the scenario and have a look at the traces of webdispatcher, indexserver and xsengine by searching for “SAML” keyword.
Fiddler is a tool that captures HTTP(s) traffic from your local machine. You can use it to see what HTTP(s) requests are sent from your client to BI platform and HANA and which errors come back.
See note 1766704 for details how to install and user Fiddler.