Overview
Using SSL with IBM Big Insights – Big SQL means over the network your data can be sent securely. In this article, you will learn how to configure this protocol for Big SQL v 4.0 and new releases.
The goal of this article is to make the extensive steps in Knowledge Centre Enabling SSL (Secure Socket Layer encryption) easier to follow and execute but it is still recommended to go through the Knowledge Centre especially for unfamiliar terms, commands, options etc. mentioned in this article.
Note: The following instructions were tested in RHEL 6*, 7* and SLES 11 on x86_64 platforms. These steps simulates a client-server approach where in Big SQL Head Node is the “server” and “client” mentioned here could be a DB2 instance installed in another machine, an IBM Data Studio client that uses JDBC driver, or any client application having to access Big SQL service.
Pre-configuration checks from Big SQL head node
Before configuring SSL, ensure that IBM Global Security Kit (GSKit) is listed in the PATH and LD_LIBRARY_PATH environment variable. GSKit is automatically included when you install the Big SQL service. On supported Linux platforms, GSKit libraries are in /home/bigsql/sqllib where the database instance can be found in:
export PATH=$PATH:/home/bigsql/sqllib/gskit/bin
export PATH=$LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/bigsql/sqllib/gskit/bin
Setting up Big SQL head node (server)
Login as “bigsql” user normally or a user who is the instance owner
1. Configure your environment variable. e.g.
export PATH=$PATH:/home/bigsql/sqllib/gskit/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/bigsql/sqllib/gskit/bin
2. Create a new directory where all your SSL files will reside. e.g.
/home/bigsql> mkdir ~/bigsql_server_SSL
3. Create a server key database. e.g.
/home/bigsql/bigsql_server_SSL> gsk8capicmd_64 -keydb -create -db "key.kdb" -pw "bigsqldb" -stash
4. Add a certificate for your server to your key database. e.g.
/home/bigsql/bigsql_server_SSL> gsk8capicmd_64 -cert -create -db "key.kdb" -pw "bigsqldb" -label "bigsqlscert" -dn "CN=myhost.mycompany.com"
5. Extract the certificate you just created to a file. e.g.
/home/bigsql/bigsql_server_SSL> gsk8capicmd_64 -cert -extract -db "key.kdb" -pw "bigsqldb" -label "bigsqlscert" -target "key.arm" -format ascii -fips
6. Verify the following files are created. e.g.
/home/bigsql/bigsql_server_SSL> ls
key.arm key.crl key.kdb key.rdb key.stn
7. Copy the directory and its contents ie. /home/bigsql/bigsql_server_SSL to all worker nodes
8. Set database manager configuration parameters. e.g.
/home/bigsql> db2 update dbm cfg using SSL_SVR_KEYDB /home/bigsql/bigsql_server_SSL/key.kdb
/home/bigsql> db2 update dbm cfg using SSL_SVR_STASH /home/bigsql/bigsql_server_SSL/key.sth
/home/bigsql> db2 update dbm cfg using SSL_SVR_LABEL bigsqlscert
/home/bigsql> db2 update dbm cfg using SSL_SVCENAME 52000
9. Set DB2COMM registry variable. e.g.
db2set DB2COMM=SSL,TCPIP or
db2set DB2COMM=SSL
10. Update DIAGLEVEL database manager configuration parameter to 4. e.g.
/home/bigsql> db2 update dbm cfg using DIAGLEVEL 4
11. Refresh your instance to updates to take effect. e.g.
/home/bigsql> db2stop
/home/bigsql> db2start
Setting up a client
In this example, to test if we can connect to SSL configured head node, a client which is another DB2 instance from a separate machine is used for testing.
1. Create a new directory where all your SSL files will reside. e.g.
/home/bigsql> mkdir ~/bigsql_client_SSL
2. FTP the key.arm from the server and place it in the client inside. e.g.
/home/bigsql/bigsql_client_SSL directory
3. Create the client key database. e.g.
/home/bigsql/bigsql_client_SSL> gsk8capicmd_64 -keydb -create -db "keyclient.kdb" -pw "bigsqldb" -stash
4. Add the signer certificate sent from the server to the client key database. e.g.
/home/bigsql/bigsql_client_SSL> gsk8capicmd_64 -cert -add -db "keyclient.kdb" -pw "bigsqldb" -label "bigsqlclt" -file key.arm -format ascii -fips
5. Set database manager configuration parameters. e.g.
/home/bigsql/bigsql_client_SSL> db2 update dbm cfg using SSL_CLNT_KEYDB /home/bigsql/bigsql_client_SSL/keyclient.kdb
/home/bigsql/bigsql_client_SSL> db2 update dbm cfg using SSL_CLNT_STASH /home/bigsql/bigsql_client_SSL/keyclient.sth
6. Catalog the server with SSL port and SECURITY SSL option. e.g.
/home/bigsql/bigsql_client_SSL> db2 catalog tcpip node CLTNODE remote <server name> server 52000 security ssl
/home/bigsql/bigsql_client_SSL> db2 catalog db BIGSQL as BIGSQLCG at node CLTNODE
/home/bigsql/bigsql_client_SSL> terminate
7. Connect to the server database. e.g.
/home/bigsql/bigsql_client_SSL> db2 connect to BIGSQLCG user bigsql using bigsql
Database Connection Information
Database server = DB2/LINUXX8664 10.6.3
SQL authorization ID = BIGSQL
Local database alias = BIGSQLCG
Using JSQSH client to connect to already setup SSL server
In this example, to test if we can connect to SSL configured head node, a JSQSH client which is using JDBC connection is used for testing.
1. From SSL server (/home/bigsql/bigsql_server_SSL), run the following command to import key.arm to a keystore server.jks that a client will be using as one of the parameter in the JDBC conenctions. Note the password used here it will be used later for keystore password. e.g.
keytool -import -file key.arm -keystore server.jks
2. From JSQSH client, granting the connection has been setup already with the SSL port ie. <52000>from the connection wizard, run the following command after running jsqsh command and prompt appears. e.g.
\connect -Ubigsql -Pbigsql -S <server name> -p <SSL port> -ddb2 -Dbigsql -O sslConnection=true -O sslTrustStoreLocation=/home/bigsql/server.jks -O sslTrustStorePassword= <keystore password>
3. Run some query to test if the connection is successful.
Note:
To connect to any JDBC client (e.g. IBM Data Studio), the three parameters to be aware of when you setup the JDBC connection are:
1. sslConnection
2. sslTrustStorePassword
3. sslTrustStoreLocation
Troubleshooting
1. SSL is not configured properly if configuration parameter is null or unset: SSL_SVR_KEYDB, SSL_SVR_STASH they both must be a qualified path.
2. SSL is not configured properly if SSL_SVCENAME configuration parameter is null or unset: If TCP/IP and SSL are both enabled (the DB2COMM registry variable is set to ‘TCPIP, SSL’), you must set SSL_SVCENAME to a different port than the port to which SVCENAME is set. The SVCENAME configuration parameter sets the port that the DB2 database system listens on for TCP/IP connections. If you set SSL_SVCENAME to the same port as SVCENAME, neither TCP/IP or SSL will be enabled.
3. SSL is not configured properly if SSL_CLNT_KEYDB and SSL_CLNT_STASH NULL configuration parameters are not specified, the connection will fail with SQL30081N
4. When the DB2COMM registry variable is set to ‘TCPIP, SSL’, and if TCPIP support is not properly enabled, such as the svcename configuration parameter being set to null, the error SQL5043N is returned and SSL support is not enabled
/home/bigsql> db2set -all
[i] DB2COMM=SSL,TCPIP
/home/bigsql> db2 get dbm cfg | grep -i svcename
TCP/IP Service name (SVCENAME) =
5. If starting your instance during db2start will return SQL5043N
/home/bigsql> db2start
SQL5043N Support for one or more communications protocols failed to start
successfully.
However, core database manager functionality started successfully.
Also, these errors will appear if step #7 from the head node section above is missed which means only node 0 (head node) has successfully started.
6. If DB2 connection concentrator is ON. To determine whether connection concentrator is ON, issue the GET DATABASE MANAGER CONFIGURATION command. If the configuration parameter MAX_CONNECTIONS is set to a value greater than the value of MAX_COORDAGENTS, connection concentrator is ON
/home/bigsql> db2 get dbm cfg | grep -i max
You can see messages in db2diag.log e.g. from /var/ibm/bigsql/diag/DIAG0000/db2diag.log
2015-11-14-07.01.01.252735-240 I13216A330 LEVEL: Error
PID : 1126574 TID : 258 PROC : db2sysc
INSTANCE: bigsql NODE : 000
EDUID : 258 EDUNAME: db2sysc
FUNCTION: DB2 UDB, common communication, sqlcctcpconnmgr, probe:110
MESSAGE : Disable SSL as Concentrator is ON
Note: If DB2 Connect Concentrator is ON, the inbound request to the DB2 Connect server can not be SSL. However, the outbound request to the target database server can still be SSL. If DB2 Connect Concentrator is OFF, both the inbound and the outbound requests can be SSL.
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(100)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(1000)
Resources
- For more information of ways to make Big SQL secured Configuring security for Big SQL
- For more information about GSKit, read GSK_CapiCmd_UserGuide
- To learn more about IBM Global Security Kit installation, IBM Global Security Kit