Encrypted connection to SQL Server
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 43
- Joined: Tue Sep 09, 2008 1:56 pm
Encrypted connection to SQL Server
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
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
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
-
- Premium Member
- Posts: 43
- Joined: Tue Sep 09, 2008 1:56 pm
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
-
- Premium Member
- Posts: 43
- Joined: Tue Sep 09, 2008 1:56 pm
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:
b) Create trust store properties text file, e.g. datastage_truststore_properties.txt
c) Encrypt the custom trust store password:
d) Add trust store type and password entries in the properties file (the trust store type can be: default/custom/both):
Note: The default IBM Java trust store is here (its password is 'changeit'):
e) Add DataStage custom trust store parameters to dsenv.
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.
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
c) Encrypt the custom trust store password:
Code: Select all
.../IBM/InformationServer/ASBNode/bin/encrypt.sh your_datastage_custom_truststore_password
Code: Select all
truststore=both
password=your_encrypted_truststore_password
Code: Select all
..../IBM/InformationServer/jdk/jre/lib/security/cacerts
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
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
-
- Premium Member
- Posts: 43
- Joined: Tue Sep 09, 2008 1:56 pm
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
IMPORT ADDITIONAL CERT
LIST TRUSTSTORE CERTS
CONVERT TRUSTSTORE FROM JKS TO PKCS12
LIST TRUSTSTORE CERTS
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.
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
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
Code: Select all
truststore_path>home_path/IBM/InformationServer/jdk/bin/keytool -list -v -keystore truststore.jks
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
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