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.
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.
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 .
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.
- LDAP is based on Active Directory (AD)
- AD domain: eddco.com
- 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.
CREATE LDAP PROVIDER ldap_eddco CREDENTIAL TYPE 'PASSWORD' USING 'user=CN=HDBLDAP,OU=Service Accounts,DC=eddco,DC=com;password=N0tIt2018' USER LOOKUP URL 'ldap://eddco.com:389/CN=Users,DC=eddco,DC=com??sub?(&(objectClass=user)(sAMAccountName=*))' NESTED GROUP LOOKUP URL 'ldap://eddco.com:389/DC=eddco,DC=com??sub?(member:1.2.840.1135188.8.131.521:=*)' ATTRIBUTE DN 'distinguishedName' ATTRIBUTE MEMBER_OF 'memberOf' SSL ON DEFAULT ON ENABLE PROVIDER ENABLE USER CREATION FOR LDAP;
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)
CREATE CERTIFICATE FROM '-----BEGIN CERTIFICATE----- MfdsgFDGdfHgfhdD9gAVErJNkl7RYP0HKzANBgkqhkiG8hsdfE MRUwEwYKCZImiZPyLGQBGRYFbG9jYWwxHTAbBgoJkiaJk/SDFSDFre4567ujfgdfd ... PA78M4CX8fSrBI2lT433h4hjtG9fM8QKIgzLjVi3tFNaYkkZ4VzgF1MxvSRT9YRa pG0RDSFsdgfadgfasrty== -----END CERTIFICATE-----' COMMENT 'AD Certificate';
STEP 3 (Create PSE and Set its Purpose)
CREATE PSE LDAP_PSE; SET PSE LDAP_PSE PURPOSE LDAP;
STEP 4 (Add certificate to PSE)
SELECT * FROM CERTIFICATES; /*Get Certificate ID*/ ALTER PSE LDAP_PSE ADD CERTIFICATE 378500; /*Use Certificate ID from above*/
Validate LDAP user via SQL
VALIDATE LDAP PROVIDER ldap_eddco CHECK USER JONATHHAU99 PASSWORD "NeverG0ing2Get!t"; VALIDATE LDAP PROVIDER ldap_eddco CHECK USER CREATION FOR LDAP USER JONATHHAU99 ;
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.
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.
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.
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!
FYI. Installing SAPCrypto and setting the ENV VARs was required for ODBC. Nothing worked until that was installed.
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.
in summary SECUDIR=\sec but check the link for more details.
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.