Assumptions
- The Privileged Account, Endpoint and Password Consumer are already set up and working in the ENTM
- A PIM Endpoint is set up with PUPM integration to the ENTM (and is working) on the server hosting Websphere
- Websphere is running under a user that is also an Access Control user, i.e. in selang, e.g. local\Administrator.
- This is tested with with Websphere 8.5 developer edition
- The correct SQL Server driver (sqljdbc*.jar) has been obtained for the version of Java Websphere 8.5 developer edition is running under (IBM Java 1.6). At the time of writing this is explained here: https://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx and you need to use sqljdbs4.jar from SQL Server JDBC driver version 4.2.
Install The Jar Files
1. Make a folder on the Websphere server to store the jar files needed for the driver.
I am going to use C:\DBCP for this document.
2. Copy CAJDBCSqlDriverWrapper.jar from the attached CAJDBCSqlDriverWrapper.zip file to C:\DBCP.
This is a very simple A JDBC driver I wrote that wraps CAJDBCDriver.jar to make sure that the SQL Server driver is loaded (there did not seem to be a way to get websphere to do this unlike every other app server). Please note that this is provided as is and is not maintained by myself of CA Technologies. To this end the source code and build scripts are included.
3. Download the jars necessary for Apache Commons DBCP 1.4:
commons-dbcp-1.4-bin.zip
commons-pool-1.6-bin.zip
At the time of writing these are located at the following urls:
https://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
http://commons.apache.org/proper/commons-pool/download_pool.cgi
Extract commons-dbcp-1.4\commons-dbcp-1.4.jar from commons-dbcp-1.4-bin.zip and commons-pool-1.6\commons-pool-1.6.jar to C:\DBCP
4. Download and install the SQL Server JDBC driver version 4.2.
Other versions may also work, but I used 4.2 when testing this document. At the time of writing this is located here:
https://msdn.microsoft.com/en-us/library/mt683464(v=sql.110).aspx
The SQL Server jdbc driver is very particular with regards to the version of java it is run under, see:
https://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx
Websphere 8.5 Developer version uses IBM Java version 1.6, so according to the table in the above url, the required jar is sqljdbc4.jar. Using any other sqljdbc*.jar will probably fail.
Copy sqljdbc4.jar to C:\DBCP
5. Copy the CA jars to C:\DBCP
<AccessControl>\SDK\JDBC\CAJDBCDriver.jar
<AccessControl>\SDK\JDBC\CAPUPMClientCommons.jar
<AccessControl>\SDK\JDBC\jsafeFIPS.jar
6. We should now have the following files in C:\DBCP:
CAJDBCDriver.jar
CAJDBCSqlDriverWrapper.jar
CAPUPMClientCommons.jar
commons-dbcp-1.4.jar
commons-pool-1.6.jar
jsafeFIPS.jar
sqljdbc4.jar
Configure The JDBC Provider and Data source
1. In Websphere Administrative Console, create the JDBC Provider:
Resources -> JDBC -> JDBC Providers, "press New..." button
Database Type: User-defined
Implementation class name: org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS
Name: CAJDBC
Press "Next"
In "Class path" enter:
C:\DBCP\CAJDBCDriver.jar;C:\DBCP\CAJDBCSqlDriverWrapper.jar;C:\DBCP\CAPUPMClientCommons.jar;C:\DBCP\commons-dbcp-1.4.jar;C:\DBCP\commons-pool-1.6.jar;C:\DBCP\jsafeFIPS.jar;C:\DBCP\sqljdbc4.jar
Press "Next"
Press "Finish"
Press "Save" in the "Save directly to the master configuration." message in "Messages" at the top.
2. In Websphere Administrative Console, create the Data source:
Resources -> JDBC -> Data Sources, "press New..." button
Data source name: mydatasource
JNDI name: myjndiname
Press "Next"
Select "Select an existing JDBC Provider", and select CAJDBC
Press "Next"
Use the defaults (unless you have a compelling reason not to), so press "Next"
The defaults are:
Data store helper class name: com.ibm.websphere.rsadapter.GenericDataStoreHelper
"Use this data source in container managed persistence (CMP)" checked
Press Next
Press Finish
Press "Save" in the "Save directly to the master configuration." message in "Messages" at the top.
3. In Websphere Administrative Console, set the custom properties for "mydatasource"
Resources -> JDBC -> Data Sources, click on "mydatasource"
Click on "Custom properties" under "Additional Properties"
Set the value for the following custom properties, changing <HOST>, <PORT>, <DBNAME> and <USER> as required:
driver: naunton.space.jdbc.CAJDBCSqlDriverWrapper.DriverWrapper
url: @@@com.microsoft.sqlserver.jdbc.SQLServerDriver@@@jdbc:sqlserver://<HOST>:<PORT>;databaseName<DBNAME>
user: <USER>
Press "Save" in the "Save directly to the master configuration." message in "Messages" at the top.
4. Test
Resources -> JDBC -> Data Sources, click on "mydatasource", press "Test connection".
This should return successfully.