Cloud Pak for Data

 View Only

IBM Cloud Pak For Data : Connecting to Db2 Warehouse using API Key On SSO Enabled Cluster

By BHARATH DEVARAJU posted Wed March 01, 2023 01:48 AM

  
Summary

 IBM Db2 Warehouse is an analytics data warehouse that features in-memory data processing and in-database analytics. It is client-managed and optimized for fast and flexible deployment, with automated scaling that supports analytics workloads.

Db2 warehouse hosted on IBM Cloud Pak for Data supports following authentication methods,

1. Username and password 

2. JWT token. 

3. API keys. 

Username and password is still one of the widely used authentication mechanism, however Username and password and JWT token authentication requires LDAP integration.

     In scenarios where customer is using an external authentication provider (SSO) and direct LDAP integration is unavailable, API key is the preferred authentication method for external clients to connect to Cloud Pak for Data hosted services.

In the following article we will discuss steps to configure your external JDBC clients such as DBeaver to connect to DB2 Warehouse service using API key. 

Pre-requisities 

1. Cloud Pak for Data 4.0.x , 4.5.x, 4.6.x with Db2 Warehouse service installed 

2. The users connecting to Db2 warehouse instance should be granted appropriate access, login to CPD web client as an administrator, goto the Instances page and open the Manage access page.

Add the user to the instance and assign the required role, in the following example we have added aduser and assigned an admin role, the definition of each role and the assigned privileges are explained in the following link - https://www.ibm.com/docs/en/cloud-paks/cp-data/4.6.x?topic=p-managing-database-privileges-cloud-pak-data-users

Steps For Configuring The Database Client To Use API Keys For Authentication

1. Users can generate an API key using Cloud Pak for Data web client, login to CPD UI as the required user and goto "Profile and settings" page to generate the platform API key as shown following

API Keys cannot be recovered,  hence there are only two options available Generate a new key or Revoke the current key, select Generate a new key and make sure to store the key some place safe.

More details regarding the platform API keys are available in the following link - https://www.ibm.com/docs/en/cloud-paks/cp-data/4.6.x?topic=steps-generating-api-keys

2. Once you have the API key, configure your Db2 connection properties using the SQL Clients such as DBeaver and use the IBMIAMauth plugin for authentication.

Following driver properties needs to be set within your Db2 connection to enable API key authentication,
apiKey - The APIkey needs to be provided in the following format - username:api_key, for example - aduser:sDAZA4yUpaUmxJOnBHI8eqAryd0Rr7iG889nOnfk

pluginName - IBMIAMauth

securityMechanism - 15

sslConnection - true

sslTrustStoreLocation - location of your SSL trust store

sslTrustStorePassword - SSL trust store password

The SSL connection is mandatory while using JWT token or API key for authentication

Please note - While running DBeaver on Windows platform an additional driver property needs to be added as shown following

sslTrustStoreType - jks

Once the properties are configured they should appear as shown in the following screenshot

Now test the connection , when prompted for username and password don't provide any values, click OK instead.
Since username and password were not provided the driver will fallback to API key authentication and if configured properly the connection will succeed. 
Conclusion

We have successfully connected to DB2 Warehouse using API keys. 

Additional Information

For additional information regarding exposing the DB2 JDBC ports or generating the trust store for Db2 warehouse on Cloud Pak For Data, refer to the following article - https://community.ibm.com/community/user/cloudpakfordata/blogs/bharath-kumar-devaraju/2022/11/19/ibm-cloud-pak-for-data-exposing-the-db2-warehouse?CommunityKey=c0c16ff2-10ef-4b50-ae4c-57d769937235

ReferenceS
  1. Db2 Authentication methods  -https://www.ibm.com/docs/en/cloud-paks/cp-data/4.6.x?topic=credentials-user-supported-authentication-methods
  2. Cloud pak for data security considerations - https://www.ibm.com/docs/en/cloud-paks/cp-data/4.5.x?topic=planning-security-considerations

#Featured-area-2-home
#Featured-area-2
0 comments
536 views

Permalink