Db2 Client
In order to connect to a Db2 database, I needed a Db2 client. Since working more and more with cloud environments and containers, I typically do not have a Db2 server or client installed. Thus, for testing, I installed the IBM Data Server Runtime Client and separately also used the official IBM Db2 Warehouse client container. As it turned out, it was a good choice...Once available, I tested the CLP for CONNECT syntax and both environments returned the following:
$ db2 ? connect
CONNECT [USER username [{USING password
[NEW new-password CONFIRM confirm-password] |
CHANGE PASSWORD}]]
CONNECT RESET
CONNECT TO database-alias
[IN {SHARE MODE | EXCLUSIVE MODE [ON SINGLE DBPARTITIONNUM]}]
[[USER username [{USING password
[NEW new-password CONFIRM confirm-password] |
CHANGE PASSWORD}]] |
[ACCESSTOKEN accesstoken] |
[APIKEY apikey]]
NOTE: From the operating system prompt, prefix commands with 'db2'.
Special characters MAY require an escape sequence (\), for example:
db2 \? change database
db2 ? change database xxx comment with \"text\"
CLP supports APIKEY and ACCESSTOKEN, but it is not reflected in the docs for all Db2 product editions.
Cataloging the database
To make the remote database known to CLP, I first needed to catalog the remote host (TCPIP NODE), then the actual database.db2 catalog tcpip node Db2oCfra remote db2host-fra02-xxx.services.eu-de.bluemix.net server 50001 security ssl
The above adds information about a node name Db2oCfra to the local node directory. The important part is to use port (SERVER) 50001 and to explicitly specify that it uses SSL. For security reasons, IAM support is restricted to SSL connections only. If port 50000 is used or if you forget to specify "security ssl", you are going to run into all kinds of error messages. Trust me on that...
The following command adds the remote database "bludb" with the alias "fradb" to the local database directory. Note that the authentication type is set to GSSPLUGIN to allow for login via IBMid.
db2 catalog db bludb as fradb at node db2ocfra authentication gssplugin
During my attempts to make IAM-based authentication work, I ran into a couple problems. The command ipclean helped me to reset / refresh the communication infrastructure and to make sure I didn't run into some caching issues.
Connect to Db2 using an API key
With the above setup in place, I added my IBM Cloud userid to my Db2 instance. It can be done in the browser console (tested) or by REST API. Next, I generated a new API key using the ibmcloud CLI. Then, the tests to connect to the database using the API key:db2 connect to fradb APIKEY myIBMCloudplatformApiKey
It succeeded in the client container, but failed in my local Db2 client environment. What was different? I found that the SSL support was not set up. I needed to install GSKit (through my package manager, alternatively by downloading), then configure SSL support for use with CLP:
1) Create a database for the encryption key:
gsk8capicmd_64 -keydb -create -db "db2clientssl.kdb" -pw "myClientPassw0rdpw0" -stash
2) Add the root certificate to the key database. The certificate is part of the Db2 client install:
gsk8capicmd_64 -cert -add -db "db2clientssl.kdb" -pw "myClientPassw0rdpw0" -file sqllib/cfg/DigiCertGlobalRootCA.arm
3) Configure the instance to use the key database and stash file for SSL-based client connections:
db2 update dbm cfg using SSL_CLNT_KEYDB /home/hloeser/db2clientssl.kdb SSL_CLNT_STASH /home/hloeser/db2clientssl.sth
With the above configured, I was able to successfully connect from my Db2 client environment.
Connect to Db2 using a bearer access token
An access token is a bearer token (actually a JSON Web Token, JWT) which can be generated based on an API key:curl -k -X POST --header "Content-Type: application/x-www-form-urlencoded" --header "Accept: application/json" --data-urlencode "grant_type=urn:ibm:params:oauth:grant-type:apikey" --data-urlencode "apikey=myIBMCloudplatformApiKey" "https://iam.cloud.ibm.com/identity/token"
The returned token can then be used to authenticate against Db2:
db2 connect to fradb ACCESSTOKEN eyHereIsAVeryLongStringblablabla
The token is valid for 60 minutes after it has been issued. Keep that in mind when testing. The established connection is not impacted by an expiring token. But when you try to connect using an expired token, it gives the following error message:
SQL30082N Security processing failed with reason "28" ("EXPIRED SERVER
CREDENTIAL"). SQLSTATE=08001
Conclusions
Getting a proper (security) configuration takes the right order of baby steps. With the right instructions and experience, it is easily and quickly accomplished. Once done, it is possible to connect to Db2 using either a platform API key of IBM Cloud or a generated access token, replacing traditional username and password. It could be used to share an automatically expiring token (for a test instance) or to pass an API key which later on gets revoked. The IAM support not only adds more authentication options, but also new items to the security toolbox.If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.
But wait, there is more...:
A collection of error messages
For the sake of documentation and finding help in this blog post, here are some error messages I ran into in setting up my environments:SQL30081N A communication error has been detected. Communication protocol being used: "SSL". Communication API being used: "SOCKETS". Location where the error was detected: "". Communication function detecting the error: "sqlccSSLSocketSetup". Protocol specific error code(s): "414", "*", "*". SQLSTATE=08001
SQL10013N The specified library "GSKit Error: 100" could not be loaded.
SQLSTATE=42724
SQL30082N Security processing failed with reason "25" ("CONNECTION
DISALLOWED"). SQLSTATE=08001
SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID"). SQLSTATE=08001
SQL30082N Security processing failed with reason "27" ("INVALID SERVER
CREDENTIAL"). SQLSTATE=08001
SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "xxx.yyy.yyy.xxx". Communication function detecting the error: "recv". Protocol specific error code(s): "104", "*",
"0". SQLSTATE=08001