How to enable secure SSL connection between IBM BigSQL server and the client? Several useful links:
More practical: https://developer.ibm.com/hadoop/2016/01/08/configure-big-sql-support-ssl/
Below I'm presenting a procedure which worked for me in several environments.
The BigSQL secure connection can be configured using bigsql credentials. No need for root authority.
BigSQL SSL connection is implemented by means of IBM Global Security Kit (GSKit). Make sure that GSKit dependency is included in the LD_LIBRARY_PATH.
(as bigsql user)
echo $LD_LIBRARY_PATH
/home/bigsql/sqllib/lib64:/home/bigsql/sqllib/lib64/gskit:/home/bigsql/sqllib/lib32
Find the gsk8capicmd_64 utility and add to PATH variable.
locate gsk8capicmd_64
/usr/ibmpacks/bigsql/6.0.0.0/db2/gskit/bin/gsk8capicmd_64
export PATH=$PATH:/usr/ibmpacks/bigsql/6.0.0.0/db2/gskit/bin
Assuming /etc/bigsql/security directory.
mkdir /etc/bigsql/security
There is no need for any other then bigsql to deal with this directory.
chmod 700 /etc/bigsql/security
cd /etc/bigsql/security/
gsk8capicmd_64 -keydb -create -db bigsql.kdb -pw "secret" -stash
Verify that all necessary files are in place.
ls -l
rw------- 1 bigsql hadoop 88 Mar 9 12:00 bigsql.crl
-rw------- 1 bigsql hadoop 88 Mar 9 12:00 bigsql.kdb
-rw------- 1 bigsql hadoop 88 Mar 9 12:00 bigsql.rdb
-rw------- 1 bigsql hadoop 193 Mar 9 12:00 bigsql.sth
gsk8capicmd_64 -cert -create -label bigsql -db bigsql.kdb -dn "CN=aa1.fyre.ibm.com"
-dn parameter is the certificate subject and can include more features. It is a good practice the have CN as the hostname where BigSQL Head node is installed.
Verify the current content of key database.
gsk8capicmd_64 -cert -list -db bigsql.kdb -stashed
Certificates found
* default, - personal, ! trusted, # secret key
- bigsql
For more trusted environment, user certificates signed by CA authority.
Create Certificate Signing Reqeust (csr). The same key database is used for certficates and CSR requests.
gsk8capicmd_64 -certreq -create -dn "CN=aa1.fyre.ibm.com,O=myBIGSQL,OU=FYRE,L=H,ST=MZ,C=WAW" -db bigsql.kdb -label bigsql -file bigsql.csr -stashed
The bigsql.csr file should be created.
Verify the content key database regarding CSR entries.
gsk8capicmd_64 -certreq -list -db bigsql.kdb -stashed
Certificates requests found
bigsql
Send the bigsql.csr to the CA centre to be signed.
Two files should be received from CA centre.
- CA root chain certificate (here ca-chain.cert.pem)
- BigSQL server certificate signed by CA (here aa1.fyre.ibm.com.cert.pem)
Add root CA and BigSQL signed certificates to the key dataabase. Pay attention to receive command to add BigSQL certficate, it matches the certificate with the proper SSL key.
gsk8capicmd_64 -cert -add -db bigsql.kdb -file /tmp/ca-chain.cert.pem -stashed
gsk8capicmd_64 -cert -receive -db bigsql.kdb -file /tmp/aa1.fyre.ibm.com.cert.pem -stashed
Verify the current content of key database.
gsk8capicmd_64 -cert -list -db bigsql.kdb -stashed
Certificates found
* default, - personal, ! trusted, # secret key
! CN=thinkde.sb.com,OU=IntermediateRoom,O=MyHome,ST=Mazovia,C=PL
! CN=thinkde.sb.com,OU=MyRoom,O=MyHome,L=Warsaw,ST=Mazovia,C=PL
- bigsql
Distribute bigsql.kdb and bigsql.sth across BigSQL Worker nodes
scp /etc/bigsql/security/bigsql.kdb :/etc/bigsql/security/
scp /etc/bigsql/security/bigsql.sth :/etc/bigsql/security/
db2 update dbm cfg using SSL_SVR_KEYDB /etc/bigsql/security/bigsql.kdb
db2 update dbm cfg using SSL_SVR_STASH /etc/bigsql/security/bigsql.sth
db2 update dbm cfg using SSL_SVR_LABEL bigsql
db2 update dbm cfg using SSL_SVCENAME 32052
Several remarks
- Use full path names for key database and stash files.
- Decide on secure port connection, here 32502. Avoid using standard SSL ports like 8443, 443 etc.
Enable SSL
Keep both, secure and non-secure connections active.
db2set DB2COMM=SSL,TCPIP
Only SSL connection available, disable non-secure.
db2set DB2COMM=SSL
bigsql stop
bigsql start
Verify that BigSQL secure port is enabled
openssl s_client -connect aa1.fyre.ibm.com:32052
CONNECTED(00000003)
depth=2 C = PL, ST = Mazovia, L = Warsaw, O = MyHome, OU = MyRoom, CN = thinkde.sb.com
verify error:num=19:self signed certificate in certificate chain
---
Certificate chain
0 s:/C=WAW/ST=MZ/L=H/O=myBIGSQL/OU=FYRE/CN=aa1.fyre.ibm.com
i:/C=PL/ST=Mazovia/O=MyHome/OU=IntermediateRoom/CN=thinkde.sb.com
1 s:/C=PL/ST=Mazovia/O=MyHome/OU=IntermediateRoom/CN=thinkde.sb.com
i:/C=PL/ST=Mazovia/L=Warsaw/O=MyHome/OU=MyRoom/CN=thinkde.sb.com
2 s:/C=PL/ST=Mazovia/L=Warsaw/O=MyHome/OU=MyRoom/CN=thinkde.sb.com
i:/C=PL/ST=Mazovia/L=Warsaw/O=MyHome/OU=MyRoom/CN=thinkde.sb.com
---
Server certificate
-----BEGIN CERTIFICATE-----
............
gsk8capicmd_64 -cert -extract -db bigsql.kdb -label bigsql -target /tmp/bigsql.arm -format ascii -fips -stashed
Use Java keytool to create keystore to be used by jsqsh utility or add to already existing keystore. The password here is used to protect Java keystore, it is not the password to get access to BigSQL key database.
keytool -import -file /etc/bigsql/security/bigsql.arm -keystore server.jks
Launch jsqsh
jsqsh
1> \connect -Ubigsql -Pbigsql -S aa1.fyre.ibm.com -p 32052 -ddb2 -Dbigsql -O sslConnection=true -O sslTrustStoreLocation=/home/bigsql/server.jks -O sslTrustStorePassword=secret
Directly using jsqsh command line parameters
jsqsh -ddb2 -O sslConnection=true -O sslTrustStoreLocation=/home/bigsql/server.jks -O sslTrustStorePassword=secret -S aa1 -p 32052 -Ubigsql -Dbigsql
- -Ubigsql The BigSQL user to connect
- -Pbigsql The BigSQL user password
- -S aa1.fyre.ibm.com BigSQL Head node hostname
- -p 32052 The SSL secure port
- -O sslTrustStoreLocation= The qualified pathname of the Java keystore file
- -O sslTrustStorePassword= The password protecting Java keystore
Quick test that the SSL connection is working.
select * from syscat.tables;
create hadoop table x (x int);
insert into x values(1);
select * from x;
drop table x;