Toad World Blog

How to create an SSL connection to the Db2 Warehouse on Cloud (former dashDB) using Toad for DB2 v 6.3 and Data Server Driver.

Aug 4, 2017 7:40:00 AM by Ondrej Zizka

In this post, I will show you how to configure an SSL connection to the IBM Db2 Warehouse on Cloud. The SSL connection is not always necessary as the majority of communication between databases and applications is done in restricted networks, but if there is a need to communicate outside of the restricted network, the SSL connection is one of the “must have” features.As a first step, it is necessary to create a database service on Bluemix.

Open the following URL:

https://console.bluemix.net/catalog/?search=dashdb

Select “Db2 Warehouse on Cloud” and log in to the web interface with the IBM ID.

Select Service Name and click on Create button in the bottom-left corner. 

When the service is created, it can be found in the Services Dashboard.

Click on the service record to access the service page.

Click on the Service credentials to access the credentials page.

Click on the New credentials button.

The Add new credential window will appear. Only the Name value is mandatory. When you type the name, click on Add.

When the creation process is finished, there will be a new record in the Service credentials tab.

Click on the View credentials link to view the credentials. Several types of credentials will appear.

For our purposes the following values are important:

  • hostname
  • username
  • password

The port is also important but currently only port 50001 used for SSL connections.

Now we have all we need and we can start with the configuration of the Data Server Driver on our local machine.

Open the command line prompt and execute the following commands:

db2cli writecfg add -database BLUDB -host hostname -port 50001
db2cli writecfg add -dsn alias -database BLUDB -host hostname -port 50001
db2cli writecfg add -database BLUDB -host hostname -port 50001 -parameter "SecurityTransportMode=SSL"

Substitute hostname for the hostname you recorded on the credentials page and alias for the alias for the database you want to use on your computer.

These commands will create a DSN data source what will communicate via the SSL connection.

For this type of connection, it is not necessary to have a local certificate repository which will maintain database certificates. This topic will be discussed in another post.

Now it is time to test whether the connection works. It can be performed by the following command:

testconn40 "DATABASE=alias;UID=userid;PWD=password;"

As an alias use the alias created in the previous step; as a UID use a username, and as a PWD use password for your cloud database.

The successful test should look like this.

Now it is possible to connect to the Db2 Warehouse on Cloud database from the Toad for DB2.

Start Toad for DB2 and click on File | New | Connection.

Click on the Launch DS Drive Config File Wizard button.

Select DSN Connection and click Next.

Select the DSN alias created for the cloud database and click on Finish.

Fill in the username and password for the database and click on Connect.

When you see schemata like GOSALES or GOSALESDW you’ve successfully connected to the database.

 We have learnt how to establish an SSL connection between cloud database and Toad for DB2 using Data Server Drive which is a new version of client what can be used Toad for DB2 since version 6.3.

This is the way how to connect to the database without the need to have a local key store or use Global Security Kit. This is not possible when you want to use e.g. native Java application. How to configure Db2 to use key store will be covered in another post.


Sources:

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.doc/connecting/connect_connecting__net_applications.html

 

Tags: Toad for IBM DB2

Ondrej Zizka

Written by Ondrej Zizka