This blog provides details on the steps required to configure LDAP based authentication for SAP HANA 2.0. LDAP based authentication is of great help for below two reason to the users –
- With the growing number of HANA databases in landscape, it is very tedious to remember password for each database.
- Avoid frequently generated alert for “Expiration of database user passwords”
LDAP defines a standard protocol for accessing directory services, which is supported by various directory products. Using directory services enables important information in a corporate network to be stored centrally on a server. The advantage of storing information centrally for the entire network is that you only must maintain data once, which avoids redundancy and inconsistency.
If an LDAP directory is available in your corporate network, you can configure the SAP system to use this feature. For example, a correctly configured SAP system can read information from the directory and store information there.
LDAP user authentication was first introduced in HANA 2.0 SPS 00, in later release HANA 2.0 SPS 03 it was enhanced with automatic user provisioning. So if your database is on HANA 2.0, you can configure LDAP as per the step listed in this blog.
To enable LDAP user authentication, you set up a connection to an LDAP server by creating an LDAP provider in the SAP HANA database. Depending on your requirements, you configure the LDAP server to authenticate users only, or to authenticate and authorize users.
In this blog, we will just configure LDAP to authenticate user. Other functionality like LDAP group authorization and automatic user creation is not covered in this blog
- An LDAP v3 compliant server
- You have the system privilege LDAP ADMIN.
- A certificate collection with purpose LDAP exists in the database and the certificate of the Certificate Authority (CA) that signed the certificate used by the LDAP server has been added. This is required to enable secure communication between SAP HANA and the LDAP server using the TLS/SSL protocol.
- LDAP authentication is an active authentication mechanism in the SAP HANA database. You can verify this by checking the value of the parameter [authentication] authentication_methods in the global.ini configuration file.
Always check latest available document SAP Help Portal – Configure an LDAP Server Connection for LDAPUser Authentication
Create LDAP Provider
To configure a connection to an LDAP server in SAP HANA, you need to create an LDAP provider in the SYSTEMDB and each tenant database database with the CREATE LDAP PROVIDER or ALTER LDAP PROVIDER statements.
NOTE: In this blog, steps shown is performed in tenant database. So if you want LDAP authentication in SYSTEMDB and other tenant in the system then same steps needs to be followed in each database.
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).
--- Create LDAP Provider CREATE LDAP PROVIDER LDAP_NONPROD CREDENTIAL TYPE 'PASSWORD' USING 'user=CN=<user_dn_string_literal>;password=<passphrase>' USER LOOKUP URL 'ldap://<hostname>:<Port>/<Base DN>??sub?(&(objectClass=user)(sAMAccountName=*))' ATTRIBUTE DN 'distinguishedName' SSL ON DEFAULT ON ENABLE PROVIDER;
I have not used used this LDAP attribute as I don’t want to verify whether the users are member of any group or not.
I want SSL to be enabled for the client connection with LDAP. When using SSL protocol, the trust store used to authenticate communication must be a certificate collection in the SAP HANA database with the purpose LDAP. The certificate of the Certificate Authority (CA) that signed the certificate used by the LDAP server must be available in this certificate collection.
When set to ON, the SSL/TLS protocol is used, and the URL begins with “ldap://”.
Detail option about syntax is mentioned in CREATE LDAP PROVIDER Statement (Access Control)
If above Syntax is correct, you will get below message and LDAP provider gets created
Statement 'CREATE LDAP PROVIDER LDAP_NONPROD CREDENTIAL TYPE 'PASSWORD' USING ...' successfully executed in 67 ms 373 µs (server processing time: 2 ms 676 µs) - Rows Affected: 0
NOTE: There is high chances on getting this “CREATE LDAP PROVIDER” SQL query wrong and you won’t be able to validate it. I cannot post the query here, but if you guys have performed LDAP in ABAP then try to use Similar User String here and also the base entry same as that in ABAP.
Now we will validate an LDAP provider configuration and LDAP authentication for users of that LDAP provider.
--- Validate LDAP provider VALIDATE LDAP PROVIDER LDAP_NONPROD;
You will get below message, as we have not maintained client root certificate in our tenant database certificate collection.
Could not execute 'VALIDATE LDAP PROVIDER LDAP_NONPROD' in 66 ms 781 µs . SAP DBTech JDBC: : Validate LDAP provider failed because of internal error: Unable to bind with LDAP provider LDAP_NONPROD.
Secure Communication Between SAP HANA and an LDAP Directory Server
How to get AD root certificate?
Either you can ask your AD team or you can get AD root certificate from your desktop. Open “Manage computer certificates”
In our case our root certificate resides under “Intermediates Certificate Authorities” > “Certificates”. So check where your root certificate is installed in your desktop and download the same in .cer file.
Login to Tenant Database in HANA Cockpit > “Manage Certificates”
Click on “Import”
If you have multiple root certificate, browse the path of root certificate and import all one by one.
Once all are added, click on “Go to Certificate Collections”
Click on “Add” in bottom left
Give name “LDAP_PSE”
Click on “Edit Purpose” and change the purpose to “LDAP”
Click on “Add Certificate” and select all the certificate that you have imported in earlier steps
Now try to validate LDAP Provider again, it should now work without any issue
--- Validate LDAP provider VALIDATE LDAP PROVIDER LDAP_NONPROD;
Now you should get below result
Statement 'VALIDATE LDAP PROVIDER LDAP_NONPROD' successfully executed in 103 ms 461 µs (server processing time: 34 ms 595 µs) - Rows Affected: 0
Verify AD User using LDAP Provider in HANA
Now as LDAP provider is configured and validated, we will now check whether AD user can validate using the LDAP provider even though that user is not present in HANA database.
--- Validate connectivity of users using LDAP VALIDATE LDAP PROVIDER LDAP_NONPROD CHECK USER PADD02 PASSWORD "*********";
You will get below result (I din't had this user in HANA but it is AD user) Statement 'VALIDATE LDAP PROVIDER LDAP_NONPROD CHECK USER PADD02 PASSWORD "*********"' successfully executed in 96 ms 728 µs (server processing time: 31 ms 41 µs) - Rows Affected: 0
With below query you can check whether user is available in HANA database on not. As we can see, user is not is HANA database, but it was still able to validate user in LDAP.
SELECT USER_NAME, CREATOR, CREATE_TIME, LAST_SUCCESSFUL_CONNECT, AUTHORIZATION_MODE FROM USERS WHERE USER_NAME = 'PADD02';
NOTE: We have not enabled automatic user creation in HANA database, so we have to create user in HANA database manually with needed roles and provide authentication as LDAP.
User Creation in HANA with LDAP Authentication Enabled
Now we will execute below query to create user in HANA database with authentication via LDAP provider.
--- Create User CREATE USER USPADD02 WITH IDENTITY FOR LDAP PROVIDER;
Result will be
Statement 'CREATE USER PADD02 WITH IDENTITY FOR LDAP PROVIDER' successfully executed in 85 ms 631 µs (server processing time: 19 ms 802 µs) - Rows Affected: 0
We don’t have to provide password while creating user. Login to tenant database in HANA studio > Manage users
As you can see the authentication method is LDAP and I’m able to login with network password
How do we change authentication to LDAP for existing users?
Just for testing purpose, I have removed above user with LDAP authentication and created it normally with password authentication. So, as you can see existing user PADD02 authentication is password and now I have to change it LDAP.
So, first thing is to disable user password
ALTER USER PADD02 DISABLE PASSWORD;
Now you see there is no authentication method define for the user. So, we will enable LDAP authentication for this user using below query
ALTER USER PADD02 ENABLE LDAP;
LDAP authentication is enabled for user.
Add roles manually to user as we won’t be providing roles using LDAP groups. Also make sure that authorization mode is LOCAL instead of LDAP.
Authorization of LDAP-Authenticated Users
The internal database user to be used for subsequent authorization checks in SAP HANA is determined during the logon process. With LDAP authentication, the internal database user name is same as the external identity used to log on. The following situations are possible:
- Database user exists and is configured for LDAP group authorization
If the database user exists and is configured for LDAP group authorization (authorization mode LDAP), it is verified that the authenticated user is a member of at least one LDAP group mapped to at least one SAP HANA role. If this is the case, the user is logged on and the identified roles granted. For more information, see the section on LDAP group authorization for existing users.
- Database user exists and is configured for local authorization
If the database user exists and is configured for local authorization (authorization mode LOCAL), the user is logged on. Privileges and roles must be granted directly to the database user by a user administrator.
- Database user does not exist and the LDAP provider is configured for automatic user creation
If the database user does not exist and the LDAP provider is enabled to create database users in SAP HANA, the required database user is created. This is described in more detail in the next section