Azure SQL connectivity from DataStage.

[rkashyap]

Connection to Azure SQL database from DataStage using JDBC with following connection string works ok:

Code: Select all

jdbc:ibm:sqlserver://azrdevstore.database.windows.net:1433;database=AZR_DEV;EncryptionMethod=SSL;ValidateServerCertificate=true;CryptoProtocolVersion=TLSv1.2;

Subsequently to establish ODBC connectivity using DataDirect driversV7.1.5 (default with 11.501), we added following lines to .odbc.ini (and corresponding entries in uvodbc.config):

Code: Select all

AZRDEVSTORE_AZURE_ODBC=DataDirect SQL Server Native Wire Protocol
.
.
.
[AZRDEVSTORE_AZURE_ODBC]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMsqls00.so
Description=DataDirect SQL Server Native Wire Protocol
Database=AZR_DEV
EncryptionMethod=1
HostName=azrdevstore.database.windows.net
PortNumber=1433
ValidateServerCertificate=1

However, the connection is failing with following error:

Code: Select all

>LOGTO TESTPRJ_DEV
>DS_CONNECT AZRDEVSTORE_AZURE_ODBC
Enter username for connecting to 'AZRDEVSTORE_AZURE_ODBC' DBMS [ex123]: azrdevadm@azrdevstore
Enter password for azrdevadm@azrdevstore:
SQLConnect error:   Status = -1   SQLState = 28000   Natcode = 0
[ODBC] [IBM(DataDirect OEM)][ODBC SQL Server Wire Protocol driver]Cannot load trust store.

Please share your thoughts.

[cdp]

According to almighty Google when you search for "Cannot load trust store", it seems you have stuff missing in your odbc.ini file

eg. from the first 2 search results:

Add the following connection options to the SQL Server DSN in the odbc.ini file
EncryptionMethod=1
TrustStore=xxxxx
TrustStorePassword=xxxxx
ValidateServerCertificate=1

http://knowledgebase.progress.com/articles/Article/9008

To setup ODBC SSL connectivity to Microsoft SQL Server outlined by DataDirect, do the following:

Use the CA signed certificates or self-signed certificates for SSL.
You will require two certificates with the type mentioned in step 1. One for the database machine and one for the client machine where the drivers are installed.
Setup the SQL Server database for SSL connectivity using the Database Machine certificate.
Copy the Client Machine certificate to a location that can be accessed by the driver on UNIX.
Note

It is not required to install the certificate on UNIX.
Create a Trust Store file.
Configure the following connection options in the odbc.ini file for the data source:

Configure "EncryptionMethod=" to the desired value ("1" for SSL, "6" for Request SSL, or "7" for Login SSL).
Configure "TrustStore=" entry in the ODBC data source to point to that truststore file (full path, including the file name, with extension).
Configure "TrustStorePassword=" entry in the ODBC data source (with the password set on the certificate, if there is one).

https://kb.informatica.com/solution/23/ ... 50287.aspx