Power BI Desktop Single-Sign-On to SAP HANA

This post is all about Single-Sign-On (SSO) from Microsoft Power BI Desktop to SAP HANA database 2.0.
When you initially get the task to design and/or set this up, there are a few questions that need to be asked first, before you start setting up SSO.
In the post I will compare the two methods for single-sign-on: Kerberos and SAML, plus the use of the Power BI Gateway (also known as the “On-Premises Data Gateway”).

Index:

Questions to Ask
On-Premises Data Gateway or no On-Premises Data Gateway
HANA Integration Point
Kerberos or SAML
Power BI Direct to HANA using Kerberos (SSO2DB)
Power BI via On-Premise Data Gateway to HANA using Kerberos
Power BI via On-Premise Data Gateway to HANA using SAML
Troubleshooting
References

Questions to Ask

Before setting up SSO from Power BI Desktop to SAP HANA, you should ask these questions:

Define what the Power BI Desktop end-user will be doing:
Are the end users creating reports or are they consuming already published reports?

End users that are creating new reports will need a direct Power BI Desktop to HANA connection with SSO (a.k.a SSO2DB). This will need to use Kerberos because SAML is not supported.
End users that are consuming already published reports can use the On-Premises Data Gateway with SSO to access and execute the reports from Power BI Desktop. The On-Premises Data Gateway can use Kerberos or SAML.
Define where Power BI Desktop will be running:
Do end-users all have Windows accounts in the same domain?

For direct to HANA connections with SSO, Kerberos is used and requires the end-user to be signed into Windows with a Windows account on the machine where Power BI Desktop is running.
If the end-user does not have a Windows account (or the they sign into Windows with a different, un-trusted domain) they can enter Windows credentials into the login box inside Power BI Desktop (this is not quite so seamless), but they will still need an AD account and one that is federated with the domain in which SAP HANA has been added to (it gets it’s own service account).
If using On-Premises Data Gateway, define how many HANA systems will be connected to it:
Is the On-Prem Data Gateway needing to connect to multiple HANA systems?

When connecting On-Premises Data Gateway to HANA using SAML for SSO, there is a one-to-many relationship with the SAML key and certificate generated for On-Premises Data Gateway. The On-Premises Data Gateway can only use one certificate and this one has to be deployed and trusted on all the HANA systems that it will be connecting to. Therefore, you really need to have a On-Premises Data Gateway for each HANA environment (or at least, one for Production and one for Non-Production) to allow proper testing and better security for production.
If planning to use Kerberos for SSO, identify corporate security policies & settings for Active Directory (AD) service accounts:
Do AD service accounts required AES256 bit encryption?
What are the server names and domains of the required domain Key Distribution Centre (KDC) server(s)?
What will be the full UPN of the user account when using Kerberos?

When AD service accounts have AES256 bit encryption, it changes the process for setting up the keytab file that is placed onto the SAP HANA server.
The KDC and domain information will be needed for the configuration of the HANA server’s krb5_hdb.conf file.
The AD administrators should be asked for the above information.

On-Premises Data Gateway or no On-Premises Data Gateway

You can use the On-Premises Data Gateway (Power BI Gateway) for accessing the data in “on-premise” systems. This includes HANA databases. The gateway acts as a kind of reverse proxy because it connects out to Microsoft from inside the customer’s network (where it is hosted).

The Gateway provides a distribution (publishing) framework where reports can be curated and published for access by many users.
End-users can connect from their Power BI Desktop (installed on their local computer) to the On-Premises Data Gateway *over the internet*.

Without the On-Premises Data Gateway, each Power BI Desktop end-user will need a direct connection to the SAP HANA database. It is recommended that this is performed over a VPN connection, or for the end-user to be physically in a corporate office on the LAN/WAN. In the future the Azure “v-Net” connection option may support SAP HANA connections if you happen to host your SAP HANA in Microsoft Azure.
NOTE: In the below, we could be using Azure AD or classic Active Directory Domain Services.

HANA Integration Point

Before we continue we need to highlight that the Power BI Desktop and On-Premises Data Gateway connect to the SAP HANA database indexserver for SSO via both Kerberos and SAML.
Changes are not required to the HANA XSA (Application Server Extended) for these integrations. It is not the same integration that you may read in other guides (especially guides related to HANA’s analytical capabilities).

Kerberos or SAML

Whether to use Kerberos or SAML is really up to your organisation’s preferences and capabilities.
Microsoft and SAP recommend SAML as a modern approach to Single-Sign-On.

SAML de-couples the SAP HANA system from the identity provider and is simpler to use with potentially less firewall changes.
Be aware, the On-Premises Data Gateway can only use one certificate for SAML for all the HANA databases it talks to.
When using SAML, the On-Premises Data Gateway connection to HANA needs securing with TLS, otherwise the SAML assertion (kind of like a certificate) would be sent unencrypted.

On the other hand, Kerberos provides a centralised identity management approach and is much more rigid in design with a few more steps involved in the setup. It is also a much older protocol with its own set of vulnerabilities, but it comes without the requirement to setup TLS (although it is still recommended).

If you need to have Power BI Desktop connecting directly to SAP HANA (a.k.a SSO2DB), then as of writing this can only use Kerberos for single-sign-on. Kerberos delegation is not needed in this scenario.
For connection from Power BI Desktop via the On-Premises Data Gateway to SAP HANA, then both Kerberos or SAML can be used.
When using the On-Premises Data Gateway with SAML, the On-Premises Data Gateway becomes the SAML identity provider (IdP).
When using the On-Premises Data Gateway with Kerberos, the On-Premises Data Gateway will use Kerberos delegation on behalf of the end-user.

Power BI Direct to HANA via Kerberos (SSO2DB)

NOTE: This is also known as SSO2DB.

The first thing to note about connecting Power BI directly to SAP HANA using Kerberos for single-sign-on, is that your BASIS team will need to work with the Microsoft Active Directory (AD) team.
It is possible that the AD team can delegate a proportion of the work to the BASIS team by creating a separate (dedicated) organisation unit (OU) and apply permissions to allow the BASIS team to use their Windows accounts to manage the AD entities created in this new OU.

Here is how the architecture will look for a direct connection from Power BI to SAP HANA via Kerberos:

Process Flow:

User opens Power BI (or Excel).
User connects to SAP HANA database using a Windows authentication account (authenticates via Azure AD in this example).
Kerberos authentication token (ticket) is forwarded to SAP HANA during the HANA logon process.
HANA decrypts token using keytab file which contains the key for the stored service principle (SPN) and maps the decrypted Windows account name (UPN) to the HANA DB account.

There is no requirement for Kerberos delegation in this setup.

For the above setup to work, there are some required steps and some optional steps:

Required: Install SAP HANA client
The main requirement is that the SAP HANA client is to be installed onto the end-user’s computer (where Power BI desktop is running). For SAP administrators, you will note that this HANA client will also need to be included in your frequent patching & maintenance routines to ensure it is aligned with the version of SAP HANA in use.
Recommended: Install SAPCRYPTOLIB
As well as the requirement for the SAP HANA client, it is recommended that you secure the connection to SAP HANA using TLS.
For this, you will need the SAPCRYPTOLIB also installing into the HANA client location on the end-user’s machine.
This set of libraries allow TLS to be used to encrypt the connection which is part of your “data-in-transit” security posture.
You will also therefore need a SAP Personal Security Environment (PSE) file placing onto the end-user’s machine along with the SAPCRYPTOLIB.
These libraries will also need to be included in your frequent patching & maintenance routines to ensure it is aligned with the version of SAPCRYPTOLIB in use on the SAP HANA server.
Required: Define Env Variable SECUDIR
So that the HANA client knows where the SAPCRYPTOLIB libraries (DLLs) have been deployed (if they are being deployed), you should set a SYSTEM environment variable called “SECUDIR” to point to the location of the SAPCRYPTOLIB files.
Optional: Enable “Server Validation”
An optional step is to enable “Server Validation” on the connection properties. It is recommended to enable this, because without server validation, it is not possible to know that the target SAP HANA server that has been connected to, is to be trusted with the Kerberos ticket that will be sent during logon.
This also serves as a method of helping to restrict who can connect to which servers, by un-trusting specific servers (maybe old sandbox ones).
For “Server Validation” to work, the PSE file which is located in the HANA client directory on the end-user’s computer, will need to be populated with the public TLS certificate(s) of the SAP HANA system(s) the end-user will be connecting to and these certificates will need to contain the FQDN that has been used to initiate the connection (e.g. my-virtual-db-hostname.corp.net).
Required: Configure Kerberos on HANA server
The krb5_hdb.conf is configured on the HANA server, according to your AD domain setup and whether AES256 is needed for the AD service account.
Once krb5_hdb.conf is configured, the AD service account can be tested at the Linux level using the required kinit and ktutil tools.
The Kerberos keytab can only be created once the AD service account has been created and the required SPN(s) mapped. The method of creating this changes depending on whether AES256 encryption is needed on the service account.
When using AES256 bit encryption, you cannot simply rotate the key in the keytab, you will need to take an interruption to SSO connectivity while you update the password in AD, then generate a new keytab key and update the keytab on the HANA system.
The SAP document speaks of not needing to restart HANA, this was not the case on all systems for whatever reason. Be prepared for HANA restarts or place the files into the /etc folder (changing names and permissions accordingly) until a restart can be done.
An important point is the host name resolution. When you setup the Kerberos keytab, the SPNs you are told to create are prefixed with “hdb/server-host”. When authentication tracing is enabled on HANA with “debug” level, you can see the hostname detection in the trace files. HANA finds its hostname then finds every canonical name it finds from DNS, then looks for matching entries in the keytab file. Obviously it has an order but from what I’ve seen you can get it to match on any canonical name even if the entry in DNS is uppercase and the keytab is lowercase.
Required: Map HANA User to UPN
In the HANA system, the database user account(s) need their “External ID” setting to the UPN that is passed in the Kerberos ticket. The UPN may not be apparent as you may imagine this to be “user.name@corp.net”, but in actual fact it may be the actual domain name “user.name@REALM”. Testing and tracing in the HANA system with the auth trace turned on, will reveal the UPN to you.

All of the above software and files can be packaged up and distributed to the end-user’s computer using orchestration tools such as SCCM.

Power BI via On-Premise Data Gateway to HANA using Kerberos

Connecting Power BI via the On-Premise Data Gateway to SAP HANA using Kerberos for single-sign-on will need to use something called Kerberos delegation. This delegation technique allows the On-Premise Data Gateway to impersonate the source user account when accessing the target SAP HANA system. It is similar to you lending your credit card to your partner (not your pin, but just your card, allowing them to make contact-less payments up-to a predefined value).
Again, the AD team will need to be involved in a similar way to the “direct to HANA via Kerberos” method.
In this setup, the On-Premise Data Gateway must be running as a domain service user (for delegation to be allowed).

As well as the AD team, you will also need to involve the Power BI administrators (or someone to configure the On-Premise Data Gateway) as some specific changes will need to be made on the gateway machine.

Here is how the architecture will look for a connection from Power BI via the On-Premise Data Gateway to SAP HANA using Kerberos for SSO:

Process Flow:

User login to Power BI Desktop.
Authentication via Azure AD (in this example).
User accesses query/connection for SAP HANA configured and published from the On-prem Data Gateway.
On-prem Data Gateway receives UPN and switches context to impersonate the end-user (account delegation), getting the token from AD and sending on to the HANA system.
HANA decrypts token using keytab file which contains the key for the stored SPN and maps the decrypted Windows account name (UPN) to the HANA DB account.

For the above setup to work, there are some required steps and some optional steps:

Required: Install SAP HANA client
The main requirement is that the SAP HANA client is to be installed onto the On-Premise Data Gateway machine. For SAP administrators, you will note that this client will also need to be included in your frequent patching & maintenance routines to ensure it is aligned with the version of SAP HANA in use.
Recommended: Install SAPCRYPTOLIB
As well as the requirement for the SAP HANA client, it is recommended that you secure the connection to SAP HANA using TLS.
For this, you will need the SAPCRYPTOLIB also installing into the HANA client location on the On-Premise Data Gateway machine.
This set of libraries allow TLS to be used to encrypt the connection which is part of your “data-in-transit” security posture.
You will also therefore need a SAP Personal Security Environment (PSE) file placing onto the On-Premise Data Gateway machine along with the SAPCRYPTOLIB.
These libraries will also need to be included in your frequent patching & maintenance routines to ensure it is aligned with the version of SAPCRYPTOLIB in use on the SAP HANA server.
Required: Define Env Variable SECUDIR
So that the HANA client knows where the SAPCRYPTOLIB libraries (DLLs) have been deployed (if they are being deployed), you should set a SYSTEM environment variable called “SECUDIR” to point to the location of the SAPCRYPTOLIB files.
Optional: Enable “Server Validation”
An optional step is to enable “Server Validation” on the connection properties. It is recommended to enable this, because without server validation, it is not possible to know that the target SAP HANA server that has been connected to, is to be trusted with the Krberos ticket that will be sent during logon.
For “Server Validation” to work, the PSE file which is located in the HANA client directory on the On-Premise Data Gateway machine, will need to be populated with the public TLS certificate(s) of the SAP HANA system(s) being connected to and these certificates will need to contain the FQDN that has been used to initiate the connection (e.g. my-virtual-db-hostname.corp.net).
Although this is optional, I suspect there is a bug in the On-Premise Data Gateway software, since it does not seem possible to use the “test connection” facility without enabling “Server Validation”.
For “Server Validation” to work, the PSE file will need to be populated with the public TLS certificate(s) of the SAP HANA system(s) the end-user will be connecting to.
Required: Configure Kerberos on HANA server
The krb5_hdb.conf is configured according to your AD domain setup and whether AES256 is needed for the AD service account.
Once krb5_hdb.conf is configured, the AD service account can be tested at the Linux level using the required kinit and ktutil tools.
The Kerberos keytab can only be created once the AD service account has been created and the required SPN(s) mapped. The method of creating this changes depending on whether AES256 encryption is needed on the service account.
Once configured, the AD service account can be tested.
The keytab can only be created once the AD service account has been created and the required SPN(s) mapped.
Required: Map HANA User to UPN
In the HANA system, the database user account(s) need their “External ID” setting to the UPN that is passed in the Kerberos ticket. The UPN may not be apparent as you may imagine this to be “user.name@corp.net”, but in actual fact it may be the actual domain name “user.name@REALM”. Testing and tracing in the HANA system with the auth trace turned on, will reveal the UPN to you.

In a High Availability cluster with 2 nodes for the On-Premise Data Gateway, both nodes will need the same files and config.

Power BI via On-Premise Data Gateway to HANA using SAML

Connecting Power BI via the On-Premise Data Gateway to SAP HANA using SAML is the most simplistic setup because it de-couples the HANA system from Azure AD, with the On-Premise Data Gateway becoming the Identity Provider in this scenario.
There is no need to make changes to AD and in this setup, the On-Premise Data Gateway service can be running as a local computer account.
You will need to involve the Power BI administrators (or someone to configure the On-Premise Data Gateway) as some specific changes will need to be made on the gateway machine.

One important point to note about this setup: The On-Premise Data Gateway can only use one certificate to connect to HANA. This means if you have more than one HANA system, they will need to all trust the same On-Premise Data Gateway certificate.
This is a limitation in the configuration file of the On-Premise Data Gateway.

Here is how the architecture will look for a connection from Power BI via the On-Premise Data Gateway to SAP HANA using SAML for SSO:

Process Flow:

User login to Power BI Desktop.
Authentication via Azure AD.
User accesses query/connection for SAP HANA configured and published from the On-prem Data Gateway.
On-prem Data Gateway receives UPN and generates a SAML assertion.
Gateway signs the SAML assertion including target user account details, using IdP key and sends to HANA DB server over TLS. HANA DB validates signature using IdP pub key then maps the target user to a DB user ID and performs the query work.

For the above setup to work, there are some required steps and some optional steps:

Required: Install SAP HANA client
The main requirement is that the SAP HANA client is to be installed onto the On-Premise Data Gateway machine. For SAP administrators, you will note that this client will also need to be included in your frequent patching & maintenance routines to ensure it is aligned with the version of SAP HANA in use.
Recommended: Install SAPCRYPTOLIB
As well as the requirement for the SAP HANA client, it is recommended that you secure the connection to SAP HANA using TLS.
For this, you will need the SAPCRYPTOLIB also installing into the HANA client location on the On-Premise Data Gateway machine.
This set of libraries allow TLS to be used to encrypt the connection which is part of your “data-in-transit” security posture.
You will also therefore need a SAP Personal Security Environment (PSE) file placing onto the On-Premise Data Gateway machine along with the SAPCRYPTOLIB.
These libraries will also need to be included in your frequent patching & maintenance routines to ensure it is aligned with the version of SAPCRYPTOLIB in use on the SAP HANA server.
Required: Define Env Variable SECUDIR
So that the HANA client knows where the SAPCRYPTOLIB libraries (DLLs) have been deployed (if they are being deployed), you should set a SYSTEM environment variable called “SECUDIR” to point to the location of the SAPCRYPTOLIB files.
Optional: Enable “Server Validation”
An optional step is to enable “Server Validation” on the connection properties. It is recommended to enable this, because without server validation, it is not possible to know that the target SAP HANA server that has been connected to, is to be trusted with the SAML assertion that will be sent during logon.
For “Server Validation” to work, the PSE file which is located in the HANA client directory on the On-Premise Data Gateway machine, will need to be populated with the public TLS certificate(s) of the SAP HANA system(s) being connected to and these certificates will need to contain the FQDN that has been used to initiate the connection (e.g. my-virtual-db-hostname.corp.net).
Although this is optional, I suspect there is a bug in the On-Premise Data Gateway software, since it does not seem possible to use the “test connection” facility without enabling “Server Validation”.
For “Server Validation” to work, the PSE file will need to be populated with the public TLS certificate(s) of the SAP HANA system(s) the end-user will be connecting to.
Required: Create HANA SAML Provider
In the HANA system a new SAML provider needs creating and assinging the IdP certificate that is to be trusted.
Required: Map HANA User to UPN
In the HANA system, the database user account(s) need their account enabling for SAML authentication and mapping to an allowed provider IdP.
The UPN may not be the same as the UPN use for any Kerberos setup. You may imagine this to be “user.name@REALM”, but in actual fact it may be the actual domain name “user.name@corp.net”. Testing and tracing in the HANA system with the auth trace turned on, will reveal the UPN to you.
Once a provider is mapped, the user’s HANA account needs updating with their external ID (the UPN).

In a High Availability cluster with 2 nodes for the On-Premise Data Gateway, both nodes will need the same files.

A new private key and certificate will need to be generated for the On-Premise Data Gateway. Whilst the Microsoft documentation for SAML setup shows using OpenSSL to create the certificate, it is entirely possible to do this in PowerShell (see my other post here which will save you much hassle 😉 ).

Another step that the Microsoft documentation has, is to create a Certificate Authority key, then create a signing request for a new non-CA key. This is just not required with SAML. A certificate chain is not needed and HANA does not verify the chain.
Instead just create a CA key and certificate (again see my other post here). If you use my linked PowerShell method you don’t even need to manually transfer keys around, just create and import into the Microsoft Certificate Store (for local computer).

In the Microsoft documentation there are a couple of additional errors/ommissions that may catch you out:

The On-Premise Data Gateway configuration file is prefixed with “Microsoft.”. This was missing in the documentation.
It should be: Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config
The thumbprint of the certificate should be in lowercase. It is not known if this is actually required, but ad-hoc Google searching revealed some customers were not able to get it to work with an uppercase certificate thumbprint.
When adding the certificate thumbprint to the Gateway config file, the file is XML format.
This means you need to change the closing tag of the “setting” element and add a child “value” element.
Overall it should look like this for the thumbprint:

<setting name=”SapHanaSAMLCertThumbprint” serializeAs=”String”>
<value>the-thumbprint-here</value>
</setting>

Troubleshooting

During the setup process, do not expect it to be straightforward.
From experience, the following areas will cause issues:

Knowledge of the Active Directory KDC servers.
Setting up the Kerberos configuration on the HANA server will need asnwers from the AD adminstrators.
Knowledge of the AD domain federation.
Setting up the Kerberos configuration on the HANA server will need asnwers from the AD adminstrators.
Knowledge of public key cryptography.
Creation of the IdP SAML keys is tricky and the documentation shows a convoluted method with added confusion.
Lack of accurate documentation.
Some of the Microsoft documntation is not correct or accurate enough.
On-Premise Data Gateway trace log files
These are difficult to get at as they have to be downloaded and un-zipped each time.
HANA system fails to find the Kerberos config and keytab, with only resolution being to place them in /etc or full HANA system restart.

My best advice is:

Test the Kerberos setup on the HANA server using the kinit and other tools. If using Kerberos this must work and will report “valid” during the testing of the kvno (key version number).
Use the On-Premise Data Gateway trace logs (if using On-Premise Data Gateway).
Once you are sure that it has selected the IdP certificate and is trying to talk to HANA, then switch to the HANA traces.
Use the HANA authorisation trace with “debug” setting, then check the traces.
This is useful once you know that the On-Premise Data Gateway is actually trying to talk to HANA (if using On-Premise Data Gateway), or if you are using SS2DB use these traces straight away.
These traces will tell you the decoded UPN and whether HANA has found an appropriate user account mapping (or SAML provider if using SAML).

Thanks for reading and good luck!