Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.

Enforcing SSL (TLSv1.2) JDBC Connection via jTDS JDBC driver

Enforcing SSL (TLSv1.2) JDBC Connection via jTDS JDBC driver

With the introduction of TLSv1.2 support for MS SQL servers (MS Tech Doc) one may notice that the Identity Manager JDBC connector may stall trying to make SSL connection to the MS SQL database using the jTDS third party drivers. The driver trace may show something like below and no activity after that.
[10/22/16 15:18:04.032]:MSSQL-110 PT:Opening temporary Subscriber query file.
[10/22/16 15:18:04.038]:MSSQL-110 PT:Restricting file Permission for ./jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_0.t
[10/22/16 15:18:04.041]:MSSQL-110 PT:Restricting file Permission for ./jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_0.p
[10/22/16 15:18:04.044]:MSSQL-110 PT:Restricting file Permission for ./jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_0
[10/22/16 15:18:04.099]:MSSQL-110 PT:File './jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_0' opened.
[10/22/16 15:18:04.102]:MSSQL-110 PT:Opening temporary Publisher query file.
[10/22/16 15:18:04.110]:MSSQL-110 PT:Restricting file Permission for ./jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_1.t
[10/22/16 15:18:04.113]:MSSQL-110 PT:Restricting file Permission for ./jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_1.p
[10/22/16 15:18:04.115]:MSSQL-110 PT:Restricting file Permission for ./jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_1
[10/22/16 15:18:04.241]:MSSQL-110 PT:File './jdbc_a5e1357f-33e0-4c69-c283-7f35e1a5e033_1' opened.
[10/22/16 15:18:04.244]:MSSQL-110 PT:Connecting to database...
[10/22/16 15:18:04.245]:MSSQL-110 PT:BEGIN Open connection 'dedicated0'.

In this article a workaround will be explained on how to resolve the problem.

To begin with, this problem seems isolated to the jTDS third party drivers only at this time as explained in this external forum thread (jTDS Forum). A fix to this problem has been posted in the referred thread. !!! Take CAUTION and download the patched jTDS driver jar (download jar) at your own risk to test as I was unable to find an official jTDS patch that addresses the bug at the time of writing this !!!

In order for the Identity Manager connector to connect to the MS SQL server using SSL that forces TLS v1.2, follow the below mentioned steps.

Create a self signed cert that is accepted by the SQL server


s1

Make Identity manager aware of the SQL self signed cert


Start IIS manager on the SQL server (The needed roles and features for certificate services need to be installed on the SQL server)

1

Select the server in the left pane and scroll down in the right pane to the IIS section.

2

Select and double click "Server Certificates".

3

Right click the self signed certificate that was previously created and select View.

4

In the "Details" tab click "Copy to File".

5

The certification wizard will open, follow the prompts as shown below.

6

Select "No" to export private key.

7

Leave the format to be .cer and click Next.

8

Browse and select the folder where the cert file needs to be exported into and also provide the file name. Click Next to proceed.

9

Click Finish and the certificate should be exported successfully.

10

Copy the exported cert file to the Identity Manager server. In this case, Identity manager was running on linux and hence the below steps were followed.

Stop the JDBC connector in Identity Manager.

Login into the Identity manager server and navigate to the IDM jre folder (/opt/novell/eDirectory/lib64/nds-modules/jre/)

Execute " /opt/novell/eDirectory/lib64/nds-modules/jre/bin/keytool -import -alias winsql -file /root/Desktop/sqlcert.cer -keystore /opt/novell/eDirectory/lib64/nds-modules/jre/lib/security/cacerts" to import the SQL self signed cert.

Execute "/opt/novell/eDirectory/lib64/nds-modules/jre/bin/keytool -list -alias winsql -keystore /opt/novell/eDirectory/lib64/nds-modules/jre/lib/security/cacerts" to list the imported certificate.

Update jTDS jar and JDBC url



  1. Edit the Identity Manager JDBC connector's properties and match the JDBC URL to "jdbc:jtds:sqlserver://<dbhost>:<dbport>/<dbname>;ssl=require". For Eg: jdbc:jtds:sqlserver://192.168.1.110:1433/idm;ssl=require

  2. Stop eDirectory on the Identity Manager server

  3. Move the existing jTDS driver from IDM classes folder (mv /opt/novell/eDirectory/lib/dirxml/classes/jtds*.jar /tmp/)

  4. Copy the patched jTDS jar referenced above to the following folder on the Identity Manager server (/opt/novell/eDirectory/lib/dirxml/classes/)

  5. Restart eDirectory.

  6. Start JDBC driver (if not set to auto start)

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2016-11-03 15:00
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.