SAP HANA with SSL in Spotfire® web clients with Kerberos delegated authentication

By default, SAP HANA connections that use SSL do not work in Spotfire web clients, when you use Kerberos delegated authentication.

This issue occurs because the default crypto provider, mscrypto, which is used by the SAP HANA ODBC driver, will look for certificate information in the user profile. When you use Kerberos delegated authentication, the delegated user session in the web client does not have a user profile on the node running the Web Player service. Because of this, the SSL certificate handling fails and the connection cannot be established.   

Possible workaround

A possible workaround has been identified for this problem. You might be able to use the SAP commoncrypto to point to a certificate store located on disk on the node, which will be the same regardless of the user.

The following is a step-by-step example for trying this solution.

Procedure

Prerequisites

You must have the following files and software provided by SAP:

• Sapcrypto
• SAP HANA ODBC driver

The path chosen does not have to be C:\Temp\, and sapcrypto.dll might be found elsewhere if not in the PATH, but this is one resolution. Testing with a DSN is not necessary but doing this with odbc trace turned on made it easier to catch issues.

Setting up and testing commoncrypto

1. Unpack the sapcrypto package and place the contents in C:\Temp.
2. Create a subdirectory, C:\Temp\sec.
3. Create the following system environment variable: SECUDIR=C:\Temp\sec
4. Add the following directory to the system environment variable PATH: C:\Temp\

5. Use C:\Temp\sapgenpse.exe to create the store sapcli.pse (default name used by driver) and to import the certificates to it, by executing the following commands in a command-line window:

   1. C:\Temp>sapgenpse gen_pse -p sapcli.pse "C=CA, ST=British Columbia, L=Vancouver, O=SAP, OU=AGS, CN=HANA Server HDB"
      
      Note: If prompted to specify a pin, leave it blank and just press return.
   2. C:\Temp>sapgenpse maintain_pk -a key.pem -p sapcli.pse
   3. C:\Temp>sapgenpse maintain_pk -a trust.pem -p sapcli.pse
      
      Note: The key.pem and trust.pem are from the server.

6. Test by using a DSN with SSL with these special properties (besides "Connect using SSL"):
   
   sslCryptoProvider=commoncrypto
   
   sslKeyStore=C:\Temp\sec\sapcli.pse
   
   sslTruststore=C:\Temp\sec\sapcli.pse

You can test this in Spotfire Analyst, using the DNS and username/password, which is enough to verify the SSL functionality.

Moving the configurations to the node running the Web Player

1. Copy the file sapcrypto.dll and the subdirectory sec to C:\Temp\ on the node running the Web Player service.
2. Create the following system environment variable: SECUDIR=C:\Temp\sec
3. Add the following directory to the system environment variable PATH: C:\Temp\
4. Create an identical DSN, with the same special properties as the one you created on the Analyst client computer.
5. Restart the Node Manger Service, so that it can pick up the altered system environment variables.

Test the same file as on the Analyst.

Making the Spotfire SAP HANA connector use the same certificate store

There are settings in the registry for each ODBC driver and the parameter sslCryptoProvider can apparently be set there:

In HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\HDBODBC add the string sslCryptoProvider=commoncrypto.

After this, the driver seems to be able to find the certificate store as SECUDIR\sapcli.pse (default name), and apparently, it can use SSL when running as a delegated user in the Web Player.

Test that the fix is working properly by setting the HanaAdapter for the Web Player to "Kerberos" and opening a file using the SAP Hana connector with Kerberos+SSL in the web client.

Notes

Tests for this article were run using sapcrypto.dll 8.5.17 and ODBC driver version 2.2.26.49562 (which also works with constrained delegation).

hdbodbc_cons.exe was used with the "-u student1" option to trace ODBC for a specific user (student1 in this case) on the Web Player node to only trap the test runs. "trace api on" and "trace debug on" was used to get some clue about what the driver was complaining about.