DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
ASU_ETL_DEV



Group memberships:
Premium Members

Joined: 09 Sep 2008
Posts: 43

Points: 451

Post Posted: Fri Jan 05, 2018 3:01 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: Single engine/service; xmeta on Oracle 12c RAC
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



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2790
Location: USA
Points: 21042

Post Posted: Mon Jan 08, 2018 10:19 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
ASU_ETL_DEV



Group memberships:
Premium Members

Joined: 09 Sep 2008
Posts: 43

Points: 451

Post Posted: Mon Jan 08, 2018 10:41 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 413
Location: New York City
Points: 4531

Post Posted: Thu Jan 11, 2018 6:43 am Reply with quote    Back to top    

Try the instructions in below link

http://www-01.ibm.com/support/docview.wss?uid=swg21676239

_________________
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Rate this response:  
Not yet rated
ASU_ETL_DEV



Group memberships:
Premium Members

Joined: 09 Sep 2008
Posts: 43

Points: 451

Post Posted: Tue Jan 23, 2018 11:02 am Reply with quote    Back to top    

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/knowledgecenter/en/SSZJPZ_11.3.0/com.ibm.swg.im.iis.conn.filecon.usage.doc/topics/filecon_t_enable_ssl_connect.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:
..../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:
.../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:
 truststore=both
 password=your_encrypted_truststore_password


Note: The default IBM Java trust store is here (its password is 'changeit'):
Code:
..../IBM/InformationServer/jdk/jre/lib/security/cacerts


e) Add DataStage custom trust store parameters to dsenv.
Code:
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
Rate this response:  
Not yet rated
ASU_ETL_DEV



Group memberships:
Premium Members

Joined: 09 Sep 2008
Posts: 43

Points: 451

Post Posted: Wed Apr 18, 2018 3:38 pm Reply with quote    Back to top    

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/articles/Article/9009

I created the jks truststore and then converted it to PKCS12.
https://knowledge.symantec.com/support/mpki-for-ssl-support/index?page=content&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:
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:
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:
truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -list -v -keystore truststore.jks


CONVERT TRUSTSTORE FROM JKS TO PKCS12
Code:
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:
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
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours