SAP HANA 2.0 supports LDAP!

One of the great new features available in SAP HANA 2.0 SPS0 is its support of LDAP authorization. SAP also takes that a step further in SAP HANA 2.0 SPS3 by also adding support for LDAP authentication with automated user provisioning. With this in mind, one could now state that SAP HANA supports LDAP in the enterprise environment. However, closer inspection of the evolution of its LDAP capabilities in required. Because SAP HANA’s LDAP support evolved from authorization to authentication and provisioning in later versions, the setup can get a little confusing. In many ways the components of authorization and authentication can each operate independently. However, using both together is the most practical approach. With that in mind, lets look at authorization, authentication and user provisioning each in more detail. I will also conclude with an example setup using SQL commands.

LDAP Authorization

Authorization is the function of specifying access rights/privileges to resources related to information security and computer security

With HANA 2.0, organizations can effectively map LDAP groups to SAP HANA roles. When a user authenticates, SAP HANA will perform an LDAP lookup based on the authenticating username and retrieve their LDAP group memberships. It then looks for any SAP HANA roles that are mapped to each LDAP group. It then grants each mapped role to the authenticating user. In short, the security model can be setup so that SAP HANA automatically grants roles based on a user’s corresponding LDAP (AD) group memberships.

This can be handy for organizations that have a well defined enterprise wide user provisioning process based on AD. For example, members of an existing DBA AD group could be automatically mapped to a base SAP HANA DBA role. There would be no need for the SAP HANA security team to manually grant these roles.

LDAP Authentication

Authentication (from Greek: αὐθεντικός authentikos, “real, genuine”, from αὐθέντης authentes, “author”) is the act of confirming the truth of an attribute of a single piece of data claimed true by an entity.

With SAP HANA 2.0 SPS3, organizations can also leverage LDAP for authentication. Users can be provisioned in SAP HANA to support LDAP authentication as well as LDAP authorization. When a SAP HANA user is setup for LDAP authentication, standard password authentication is disabled. This means that the user can only authenticate via one of the supported SSO providers or using their LDAP credentials. For example, if my Active Directory account were configured as an LDAP account in SAP HANA, I could access SAP HANA Studio using my Active Directory managed password. I would not be able to set a SAP HANA managed password for that same account.

Note: LDAP authentication failed the first few time I tried. As I discovered, you have to use a very recent version of SAP HANA Studio to successfully use LDAP authentication. The same goes with the SAP HANA client. You must install the version corresponding to SAP HANA 2.0 SPS3. I also found that the most recent version of SAP Crypto is required in conjunction with the SAP HANA Client. You will need to install and configure both the SAP HANA client and SAP Crypto to leverage hdbsql or ODBC access via LDAP authentication. I also found that the XSC did not support automated user provisioning. However, once a user was setup for LDAP, XSC supported LDAP authentication. Finally, no version of the SAP HANA Cockpit 2.0 allowed successful LDAP authentication. SAP has not responded to any requests for support related to the cockpit and LDAP authentication.

Automated User Provisioning

Starting with SAP HANA 2.0 SPS3, organization can chose to allow SAP HANA to automatically create LDAP users within the SAP HANA database. The user creation process is triggered assuming that the user does not already exists in SAP HANA. During a user’s first successful authentication attempt, HANA will query LDAP to see if the authenticating user is found based on the provider’s LDAP user lookup URL. If the user is found, it will create the account automatically and grant any mapped roles accordingly based on the authorization setup above. Security administrators can also manually add LDAP users using the SAP HANA Cockpit GUI or the CREATE USER xxxx WITH IDENTITY LDAP SQL.

Note: To enable automated user provisioning, make sure to include the SQL statement ENABLE USER CREATION FOR LDAP when defining the LDAP provider .

Example Setup

Below is an example, end-to-end setup for LDAP authorization and LDAP authentication using SQL statements. Lets assume the following fictitious environmental configuration parameters.

  1. LDAP is based on Active Directory (AD)
  2. AD domain:
  3. LDAP SSL is required

Create the LDAP provider via SQL:

Note: In the example, I am including the option for user creation to enable automated user provisioning. I am also using the nested group lookup url to recursively check for nested group memberships.

CREDENTIAL TYPE 'PASSWORD' USING 'user=CN=HDBLDAP,OU=Service Accounts,DC=eddco,DC=com;password=N0tIt2018'
USER LOOKUP URL 'ldap://,DC=eddco,DC=com??sub?(&(objectClass=user)(sAMAccountName=*))'
NESTED GROUP LOOKUP URL 'ldap://,DC=com??sub?(member:1.2.840.113556.1.4.1941:=*)'
ATTRIBUTE DN 'distinguishedName'

Create HANA roles mapped to LDAP (AD) groups

CREATE ROLE eddco_security_admins LDAP GROUP 'CN=HANA_SECURITY_TEAM,CN=Users,DC=eddco,DC=com';

CREATE ROLE ddco_system_admins LDAP GROUP 'CN=Domain Admins,CN=Users,DC=eddco,DC=com';

Grant Other Roles to LDAP mapped Roles

CALL "_SYS_REPO"."GRANT_ACTIVATED_ROLE"('sap.hana.ide.roles::Developer','eddco_developers');
CALL "_SYS_REPO"."GRANT_ACTIVATED_ROLE"('sap.hana.ide.roles::DBA','eddco_security_admins');

Generate Certificate from AD and import into HANA

STEP 1 (Export the AD certificate)

From an AD Domain Controller, run the following command to generate the LDAP certificate. We need to import this certificate into SAP HANA.

certutil -ca.cert c:\eddco_ca_name.cer

Copy the certificate text you see in the command line output. You need to use this text in the SQL command below. (That means… Don’t open the .cer file)

STEP 2 (Import the AD certificate into HANA’s database resident certificate store)

COMMENT 'AD Certificate';

STEP 3 (Create PSE and Set its Purpose)


STEP 4 (Add certificate to PSE)

ALTER PSE LDAP_PSE ADD CERTIFICATE 378500; /*Use Certificate ID from above*/

Validate LDAP user via SQL


Test with the most recent version of HANA Studio

Try to logon with the most recent version of SAP HANA Studio using your LDAP credentials. Make sure an account with the same account name does not already exist in SAP HANA. Your account must also be a member of one of the AD groups mapped to SAP HANA roles with studio access. If all goes well, you should be able to access SAP HANA using your AD user name and password.


  1. Hey Jonathan,

    We have configured LDAP on our HANA DB, however it appears now Power BI cannot connect using a user that is configured to use LDAP. It will authenticate against AD, but then gives an error about cannot read registry value. I assume it may be a Power BI thing, but figured I’d ask the expert 🙂

    We are on HANA DB 2.0 SP03 Rev 35.

  2. I think powerBI uses ODBC. Can you access the Windows ODBC Data Source manager and test a HANA ODBC connection… test using LDAP credentials? If that is not working, then make sure you have the most recent HANA 2.0 SPS3 Client and SAPCrypto install with its environment variables setup. Once you get the ODBD test working, power BI should also work. Note: I had this working with Alterex and anything that used ODBC when I tested in the past.

  3. Hey Jonathan,

    Thanks for the suggestion. I agree it must be some sort of flavor of ODBC. I have no problems using Eclipse to connect over JDBC, however when either using Power BI, or even the Windows HDBODBC driver to create a DSN for a test connection, it will not accept my credentials. I updated to the latest HANA DB ODBC driver but it didn’t help. And I’ve no idea about the SAPCrypto part, but that gives me something to Google on this afternoon.

    It’s encouraging you got it to work with Alteryx, since that would be to your point “just ODBC”. That gives me enough motivation to keep trying. Thanks again!

  4. Ah! Then that may in fact be my missing link. I downloaded the SAPCrypto .SAR and extracted it, but I’ll have to find an actual guide on what the Windows ENV variable name(s) to set are. I couldn’t find a suitable tutorial but this is enough to encourage me to figure it out. Thanks again so much and I’ll post a follow up with the (hopefully successful) results.

  5. Hi!

    I configured LDAP provider on SAP HANA 2.0 SPS03. I would like to validate the provider with the following command:
    hdbsql DEV=> VALIDATE LDAP PROVIDER ldap1 CHECK USER dev.test01;
    * 257: sql syntax error: incorrect syntax near “.”: line 1 col 47 (at pos 47) SQLSTATE: HY000
    How can I escape username, If it contains a dot? I tried from Hana cockpit and from hdbsql, too.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.