Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to connect to SAP HANA DB using X.509 certificate in hdbuserstore

eason_chen
Product and Topic Expert
Product and Topic Expert
‎2024 Aug 07 9:41 AM
0 Kudos
694

Overview

This guide shows you how to connect to HANA by using X.509 certificate. It can be used for logon with hdbsql, hdbuserstore for ABAP system, etc. In this demo, we would create USER.PSE for authentication and DB user DEMOUSER is mapped to it. 

"Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental."

Host Details

  • HANA
    SID: HA6
    Instance number:06
    apj-b.openstack.ap-cn-1.cloud.sap 
    SYSTEMDB@HA6
    HA6@HA6 

Referrence documents

https://help.sap.com/docs/SAP_HANA_PLATFORM/b3ee5778bc2e4a089d3299b82ec762a7/2b335f7eec6a450095f110e...
3024995 - Connecting to SAP HANA DB using X.509 certificate
2154997 - Migration of hdbuserstore entries to ABAP SSFS

Prerequisite

HANA client is above 2.13.21

HANA server is above 2.0 SPS05

Steps to Follow

  1. Create a PSE used for X509 certificate SSO. And it would be issued by a CA. This can be done with SAPGENPSE, Webdispatcher Admin Tool, etc.
    We are going to use Webdispatcher Admin Tool in this demo.
    1. Logon to http://<hostname>:80<instance number>/sap/hana/xs/wdisp/admin/public/default.html
      Set PSE File Name: USER.PSE
      Set Distinguished Name: CN=DEMOUSER.openstack.ap-cn-1.cloud.sap, O=SAP, C=DE
    2. Create CA request and ask CA to sign it.
    3. Import CA response for the PSE created.
    4. As result, a PSE used for X.509 Certificate-Based User Authentication has been created in folder /usr/sap/HA6/HDB06/apj-b/sec/. This certificate is signed by 'SAPNetCA_G2, O=SAP, L=Walldorf, C=DE'' in this demo.
      eason_chen_0-1723019360102.png

       

  2. Configure user mapping.
    1. Log on to tenant DB HA6@HA6 from HANA studio.
    2. Create DB user <USER>  in HA6@HA6 if it does not exist.
      1. CREATE USER DEMOUSER PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE;
    3. Create X509 Identity Providers. Issuer should be the same in the new created PSE file. When identity provider-based user mappings is used, matching rule also need to be created. e.g.
      1. CREATE X509 PROVIDER DEMO_X509_PROVIDER WITH ISSUER 'CN=SAPNetCA_G2, O=SAP, L=Walldorf, C=DE';
      2. ALTER X509 PROVIDER DEMO_X509_PROVIDER SET MATCHING RULES 'CN=*.openstack.ap-cn-1.cloud.sap, O=SAP, C=DE';
        PS: The identity provider specifies the matching rule 'CN=*.openstack.ap-cn-1.cloud.sap, O=SAP, C=DE'. When the client presents a user certificate with the certificate subject 'CN=DEMOUSER.openstack.ap-cn-1.cloud.sap, O=SAP, C=DE', SAP HANA checks whether the DB user DEMOUSER exists. If the user exists and all other attributes in the certificate match and are in same order, logon is successful.
    4. Mapping DB user <USER> with x509 provider. “ANY” for rule-based mapping.
      1. ALTER USER DEMOUSER ADD IDENTITY ANY FOR X509 PROVIDER DEMO_X509_PROVIDER;
      2. ALTER USER DEMOUSER ENABLE X509;
    5. Import the certificate of CA “CN=SAPNetCA_G2, O=SAP, L=Walldorf, C=DE” into certificate store of HA6@HA6 as CA_CERT.
      • 2.0 SPS06 and higher:
        CREATE         CERTIFICATE CA_CERT FROM ' -----BEGIN CERTIFICATE-----MIIGPTCCBCWgAwIBAgIKYQ4GNwAAAAAADDANBgkqhkiG9w0BAQsFADBOMQswCQYD
        ...
        7HJNBB1ZTjnrxJAgCQgNBIQ=
        -----END CERTIFICATE----- ';
      • 2.0 SPS05:
        CREATE CERTIFICATE FROM ' -----BEGIN CERTIFICATE-----MIIGPTCCBCWgAwIBAgIKYQ4GNwAAAAAADDANBgkqhkiG9w0BAQsFADBOMQswCQYD
        ...
        7HJNBB1ZTjnrxJAgCQgNBIQ=
        -----END CERTIFICATE----- ' COMMENT 'CA_CERT';
        Then you need find out the certificate id with following SQL:SELECT * FROM CERTIFICATES WHERE COMMENT = 'CA_CERT'
    6. Create certificate collection as X509_PSE
      1. CREATE PSE X509_PSE;
    7. Add previous certificate into X509_PSE
      • 2.0 SPS06 and higher:
        ALTER         PSE X509_PSE ADD CERTIFICATE CA_CERT;
      • 2.0 SPS05:
        1. find out the certificate id with following SQL:
          SELECT * FROM CERTIFICATES WHERE COMMENT = 'CA_CERT'
        2. ALTER PSE X509_PSE ADD CERTIFICATE <certificate id found with above query>;
    8. Edit the purpose
      1. SET PSE X509_PSE PURPOSE X509 FOR PROVIDER DEMO_X509_PROVIDER;
  3. Test the connection with hdbsql.
    1. Log on as ha6adm to OS
    2. cd /usr/sap/HA6/home/sap/hdbclient
    3. ./hdbsql -j -A -Z authenticationMethods=x509 -Z authenticationX509=/usr/sap/HA6/HDB06/apj-b/sec/USER.PSE -Z traceFile=stdout -Z traceOptions=debug=info,flush=on -n apj-b.openstack.ap-cn-1.cloud.sap:30615 "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"
  4. Create hdbuserstore and test it.
    1. Log on as ha6adm to OS
    2. cd /usr/sap/HA6/home/sap/hdbclient
    3. ./hdbuserstore setX509 X509DEMOUSER apj-b.openstack.ap-cn-1.cloud.sap:30615 /usr/sap/HA6/HDB06/apj-b/sec/USER.PSE
    4. ./hdbsql -j -A -U X509DEMOUSER "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"
      PS. If you want to use this PSE for X.509 Certificate-Based User Authentication in ABAP system. You need copy file USER.PSE into your ABAP server ${SECUDIR} directory (e.g. /usr/sap/<SID>/D01/sec) and set hdbuserstore accordingly.
      e.g. hdbuserstore setX509 DEFAULT apj-b.openstack.ap-cn-1.cloud.sap:30615 /usr/sap/<SID>/D01/sec/USER.PSE
    5.  
  5. To reuse this configuration for multiple users. Steps 1, 2.b, 2.d need to be rerun for each new user. Then you may use the PSE created for others and run test as in step 3, 4.

Troubleshooting

Issue 1

  1. Symptom:
    hdbsql connection test was failed.
    eason_chen_1-1723019360133.jpeg

     

  2. Analysis:
    1. Enable HANA debug trace.
      alter       system alter configuration ('global.ini','system') set ('trace','authentication') = 'debug', ('trace','crypto') = 'debug' with reconfigure;
    2. Reproduce the issue and check indexserver trace.
    3. Disable debug trace.
      alter       system alter configuration ('global.ini','system') unset ('trace','authentication'), ('trace','crypto') with reconfigure;
      eason_chen_2-1723019360144.jpeg
  3. Root Cause:
    Mapping between x509 certificate and HANA DB user is not maintained.
  4. Resolution:
    Refer to Step 2.4. to map the DB user with x509 certificate.

    PS: In case you don’t want to use rule based mapping, following step can be skipped.
    ALTER X509 PROVIDER DEMO_X509_PROVIDER SET MATCHING RULES 'CN=*.openstack.ap-cn-1.cloud.sap, O=SAP, C=DE';
    However, you need specific the identity for the user in step 2.4. as following.
    ALTER USER DEMOUSER ADD IDENTITY 'CN=DEMOUSER.openstack.ap-cn-1.cloud.sap, O=SAP, C=DE' FOR X509 PROVIDER DEMO_X509_PROVIDER;


Issue 2

  1. Symptom:
    eason_chen_3-1723019360147.jpeg
  2. Reproduce steps:
    1. Log on as ha6adm to OS
    2. hdbuserstore setX509 X509DEMOUSER apj-b.openstack.ap-cn-1.cloud.sap:30615 /usr/sap/HA6/HDB06/apj-b/sec/USER.PSE
  3. Analysis:
    ha6adm@apj-b:/usr/sap/HA6/HDB06> hdbuserstore -v
    hdbuserstore 2.11.17.1638822267
    SYSTEM: Linux / X64
    BUILD AT: 2021-12-06 20:28:00
    BRANCH: unknown
    BUILD MODE: rel
    PROCESS ID: 12978
  4. Root Cause:
    HANA client used is lower than 2.13.21.
  5. Resolution:
    Upgrade HANA client
Labels in this area
Popular Blog Posts