Encrypted connection to SQL Server

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
ASU_ETL_DEV
Premium Member
Premium Member
Posts: 43
Joined: Tue Sep 09, 2008 1:56 pm

Encrypted connection to SQL Server

Post by ASU_ETL_DEV »

Hello,
We need to code a set of parallel jobs that connect to a SQL Server source database that has encryption turned on. They gave us CA-signed certificates to install on the DataStage server and client.

We have never configured an encrypted SQL Server connection so we need a bit of step-by-step help.

We know that there are several ODBC wire driver security options that can be configured in odbc.ini, to point to the trust store.
We know that WebSphere has a trust and key store but we do not know whether that is the correct place where to import the certificates or whether an ad hoc trust store needs to be created (and how).

Any insight is greatly appreciated.
Marco
ASU Developer
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I have not done this either but I gather from the documentation that it is supported. I kind of doubt that WebSphere would need to come into play. For step by step instructions, assuming you searched and came up empty, then I would suggest opening a case to work with DataStage Support on this. They probably know how but if they don't then they will contact Progress/DataDirect to get the instructions.
Choose a job you love, and you will never have to work a day in your life. - Confucius
ASU_ETL_DEV
Premium Member
Premium Member
Posts: 43
Joined: Tue Sep 09, 2008 1:56 pm

Post by ASU_ETL_DEV »

Thanks qt_ky,
We opened a case with IBM Support. They pointed us to the odbc manuals in /branded_odbc but the driver security options' configuration is just one part of the story.
We are waiting for them to explain how to handle the certificates on the Linux server.
Marco
ASU Developer
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
ASU_ETL_DEV
Premium Member
Premium Member
Posts: 43
Joined: Tue Sep 09, 2008 1:56 pm

Post by ASU_ETL_DEV »

Thanks JRodriguez.

Yes, that was one of the posts I found too. Let's call that post#1. The other one I found is this (post#2):

https://www.ibm.com/support/knowledgece ... nnect.html

IBM approved the instructions in both. We discovered that it is not necessary to add extra options to the odbc.ini driver entry, as post#1 would suggest, so I went ahead with the following post#2 configurations.

a) On the DataStage server, creation of a custom trust store with the keytool command. An example is the following which creates the trust store and imports the x509 certificate:

Code: Select all

..../IBM/InformationServer/jdk/bin/keytool -import -file /tmp/your_x509_certificate_file -alias your_cert_alias -keystore your_datastage_custom_truststore
b) Create trust store properties text file, e.g. datastage_truststore_properties.txt

c) Encrypt the custom trust store password:

Code: Select all

.../IBM/InformationServer/ASBNode/bin/encrypt.sh your_datastage_custom_truststore_password
d) Add trust store type and password entries in the properties file (the trust store type can be: default/custom/both):

Code: Select all

 truststore=both
 password=your_encrypted_truststore_password
Note: The default IBM Java trust store is here (its password is 'changeit'):

Code: Select all

..../IBM/InformationServer/jdk/jre/lib/security/cacerts
e) Add DataStage custom trust store parameters to dsenv.

Code: Select all

DS_TRUSTSTORE_LOCATION=/your_custom_truststore_location/your_datastage_custom_truststore; export DS_TRUSTSTORE_LOCATION
DS_TRUSTSTORE_PROPERTIES=/your_custom_truststore_properties_file_location/datastage_truststore_properties.txt; export DS_TRUSTSTORE_PROPERTIES
Since the parameters are in dsenv they apply to all projects and jobs. Alternatively you can define them at the project or job level.

The above configuration worked for us and it has the added benefit of not exposing the trust store password in the odbc.ini connection entry.
ASU Developer
ASU_ETL_DEV
Premium Member
Premium Member
Posts: 43
Joined: Tue Sep 09, 2008 1:56 pm

Post by ASU_ETL_DEV »

I am reopening this post because the solution I had provided ended up being incorrect. Below is what actually worked for me.


The DataDirect SQL Server driver I am using (branded_odbc/lib/VMsqls00.so) does not support truststores in jks format, which is what the <keytool> command creates. Instead it supports the PKCS12 format.
https://knowledgebase.progress.com/arti ... ticle/9009

I created the jks truststore and then converted it to PKCS12.
https://knowledge.symantec.com/support/ ... id=SO17201


To start, cd to the directory (truststore_path) where you want to create the truststore.

CREATE TRUSTSTORE IN JKS FORMAT AND IMPORT ROOT CERT

Code: Select all

truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -import -file wrk_path/x509cert_root.cer -alias sql_server_all_root -keystore truststore.jks
IMPORT ADDITIONAL CERT

Code: Select all

truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -import -file wrk_path/x509-reverse.cer -alias sql_server_all_reverse -keystore truststore.jks
LIST TRUSTSTORE CERTS

Code: Select all

truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -list -v -keystore truststore.jks
CONVERT TRUSTSTORE FROM JKS TO PKCS12

Code: Select all

truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -importkeystore -srckeystore truststore.jks -destkeystore truststore.p12 -srcstoretype JKS -deststoretype PKCS12 -deststorepass truststore_password
LIST TRUSTSTORE CERTS

Code: Select all

truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -list -v -keystore truststore.p12 -storetype PKCS12



The following parameters need to be populated in the odbc connection entry, in .odbc.ini:
EncryptionMethod=1
TrustStore=truststore_path/truststore.p12
TrustStorePassword=truststore_password
ValidateServerCertificate=1

The configuration of the DS_TRUSTSTORE_LOCATION and DS_TRUSTSTORE_PROPERTIES parameters I had mentioned in the previous post does not work as a substitute for the TrustStore/TrustStorePassword parameters above.
Therefore the password stays hardcoded in the file and that is not the optimal solution.

If you know of a better solution please post it here.
Thanks.
ASU Developer
Post Reply