LDAP-based Authentication and User Provisioning fo...

dvankempen · ‎11-23-2018

Introduction

SAP HANA 2.0 SPS 03 introduced LDAP-based user provisioning, that is, the capability to automatically create database accounts for LDAP users and map their LDAP roles. This significantly reduces both complexity and cost for maintaining users and authorizations in larger system landscapes.

To explain how you can set this up, we have created a playlist on our SAP HANA Academy YouTube channel with sample code on the associated GitHub repository with links to the documentation.

YouTube Playlist

All the video tutorials on the client-side data encryption topic are bundled in a single playlist on our channel:

https://www.youtube.com/playlist?list=PLkzo92owKnVy851u716gxj4jRiSi7gZkY

What's New?

In the first video, what's new on the security topic for SAP HANA 2.0 SPS 03 concerning LDAP is discussed.

Tutorial Video

https://youtu.be/9OGphP_1npY?list=PLkzo92owKnVy851u716gxj4jRiSi7gZkY

Create LDAP Provider

To configure a connection to an LDAP server in SAP HANA, you need to create an LDAP provider in the (tenant) database with the CREATE LDAP PROVIDER or ALTER LDAP PROVIDER statements.

Access to the LDAP server takes place using an LDAP server user with permission to perform searches as specified by the user look-up URL. The credential of this user is stored in the secure internal credential store.

Communication between SAP HANA and the LDAP server can be secured using the TLS/SSL protocol or Secure LDAP protocol (LDAPS).

For the code, see

https://github.com/saphanaacademy/ldap

For the documentation, see

Secure Communication Between SAP HANA and an LDAP Directory Server

Tutorial Video

https://www.youtube.com/watch?v=e4beKQRhPQg

LDAP Group Authorizations

You can use LDAP group membership to authorize existing SAP HANA database users. To implement LDAP group authorization, you need to

Map LDAP groups to SAP HANA catalog roles using the CREATE ROLE or ALTER ROLE statements

Configure SAP HANA users for LDAP group authorization

For the code, see

https://github.com/saphanaacademy/ldap

For the documentation, see

LDAP Group Authorization

Tutorial Video

https://www.youtube.com/watch?v=2PiYh63RYM8

LDAP User Authentication - Automatic User Provisioning

LDAP authentication can be implemented for users accessing SAP HANA directly via JDBC/ODBC database clients. Using LDAP user passwords for authentication eliminates the need to manage user passwords and password policies in the SAP HANA database.

For the code, see

https://github.com/saphanaacademy/ldap

For the documentation, see

LDAP User Authentication

Tutorial Video

https://www.youtube.com/watch?v=IpIvOV1HKzs

Thank you for watching

The SAP HANA Academy provides free online video tutorials for the developers, consultants, partners and customers of SAP HANA.

Topics range from practical how-to instructions on administration, data loading and modeling, and integration with other SAP solutions, to more conceptual projects to help build out new solutions using mobile applications or predictive analysis.

For the full library, see SAP HANA Academy Library - by the SAP HANA Academy.

For the full list of blogs, see Blog Posts - by the SAP HANA Academy.

Subscribe to our YouTube channel for updates

Join us on LinkedIn: linkedin.com/in/saphanaacademy

Follow us on Twitter: @saphanaacademy

Facebook: facebook.com/saphanaacademy

mike_howles4 · ‎02-18-2019

After configuring LDAP, I can no longer authenticate with ODBC clients. (Even something as simple as a DSN entry in the ODBC client configuration.) Any tips on what I need to configure from an end-user workstation perspective?

dvankempen · ‎02-19-2019

Hi Mike,

LDAP requires the SAP CommonCrypto Library (CCL) and a SAP HANA 2.0 client (Driver 2.3.*).

Configuring the Client for Client-Side Encryption and LDAP

Use the SAP Common Crypto Library

Steps:

Download CCL (+ HANA client)

Set Windows System Properties Environment Variables SECUDIR (path to HANA client) and PATH (add path to HANA client)

Configure Advanced ODBC Connection Properties: Connect using SSL (Encrypt=true)

Works?

dvankempen · ‎02-19-2019

By the way, to debug, you can use the commands:

hdbodbc_cons.exe config trace debug on

odbcreg -t hdbodbc

Trace files are in current directory

mike_howles4 · ‎02-26-2019

Thanks for the details. We got it working, however it seems that we can no longer replicate BW reporting authorizations over to HANA users that are authenticated via LDAP, so it turns out we probably will not be able to pursue LDAP after all 😞

SumitPatel11 · ‎09-09-2019

Hi,

Already LDAP provider and now when I try to validate using below command , it gives error.

Also imported AD root certificate on Hana DB

I am facing issue with command VALIDATE LDAP PROVIDER

Error: (dberror) [4200]: Validate LDAP provider failed because of internal error: Unable to bind with LDAP provider

Kindly suggest.

dvankempen · ‎09-11-2019

Hi Sumit,

For internal errors I suggest to contact SAP support / create a ticket/incident.

azharuddin · ‎06-01-2021

Hi Denys,

Mapping LDAP groups to SAP HANA catalog roles, we are thinking of using a Nested AD group. Does HANA integration with LDAP support the Nested AD group?

Kindly suggest.

Thanks!
~Azhar

dvankempen · ‎06-01-2021

Hi Azhar,

I have not tried it out but according to the documentation this is supported (as I parse the text)

LDAP Provider Details | SAP HANA Administration Guide

azharuddin · ‎06-03-2021

Hi Denys,

Thanks for your response!!

I did the PoC yesterday and I'm getting the below error.

Could not execute 'VALIDATE LDAP PROVIDER TEST_NESTAD CHECK USER CREATION FOR LDAP USER AS00829' in 297 ms 319 µs .

SAP DBTech JDBC: [4200]: Validate LDAP provider failed because of internal error: No roles mapped for the LDAP groups user AS00829 is part of

As per the documentation that SAP Provided, Yes HANA supports Nested AD for user lookup. We are creating a role and alter it to map it to an LDAP group for automatic user creation. The LDAP group which we are using is a Nested Group. Even though user AS00829 is part of that Nested AD group, HANA is not able to find that user.

~Azhar

dvankempen · ‎06-03-2021

Thanks Azhar,

I have no access to an LDAP environment 1-2-3, so cannot give you a working code sample.

dennis.padia posted a blog on the topic some time ago, which provide some insights in what goes wrong.

LDAP Based Authentication for SAP HANA 2.0

If you have access to SAP Support, probably best to create a request.

If not, suggest to post this issue as a question (https://answers.sap.com) to loop in the experts from the Community. Tag with SAP HANA, Security, and LDAP. If possible, add print screens and code snippets to make it ease to reproduce.

anandmuthu · ‎04-06-2023

Hello,

Is there a way to link the XSA user from the application to the database user which is authenticated by LDAP ?

I manage to get the database user validated with LDAP but I guess its not possible. SAML would be the only option ?

Thanks.

Anand Muthu

dvankempen · ‎04-11-2023

Hi Anand,

Best to post this question to the forum [answers.sap.com]; it has been a while since I worked on this project ...

Anwar03 · ‎12-20-2023

Hi,

Thanks for the detailed blog. However, I'm trying to configure LDAP (AD) which is at on Premise and and connecting Hana Cloud to AD via Cloud Connector.

The connections works to AD LDAP host via cloud connector but how do I make it work in the SQL query.

The issue I'm facing while validating the LDAP connection. IT gives the below binding error.

Could not execute 'VALIDATE LDAP PROVIDER CGAD'
Error: (dberror) [4200]: Validate LDAP provider failed because of internal error: Unable to bind with LDAP provider CGAD: Connection failed

The LDAP provider is created successfully but the next step to validate is not working. Even I have imported all the Root Certs of AD properly in the Hana Cloud as per your blog.

Is this configuration only available using a public LDAP server or a configuration using SAP Cloud Connector is also possible to integrate with an on-prem LDAP?

In such scenario, how the LDAP URL has to be set in the CREATE/ALTER LDAP provider statement? I don't see any details on how to specify that the URL has to point to the SAP Cloud Connector.

CREATE LDAP PROVIDER test_ldap

CREDENTIAL TYPE 'PASSWORD'

USING 'user=CN=admin,CN-Users,OU=Employees,DC=corp,DC=com;password=******'

USER LOOKUP URL 'ldaps://virtualhost:443/CN=Users,OU=Employees,DC=corp,DC=com??sub?(&(objectClass=user)(sAMAccountName=*))'

ATTRIBUTE DN 'distinguishedName'

ATTRIBUTE MEMBER_OF 'memberOf'

DEFAULT ON;

ALTER LDAP PROVIDER test_ldap

ENABLE PROVIDER;

Appreciate your response