JDBC Password Consumer with JBoss
Article ID: 43196
Updated On:
Products
Issue/Introduction
A note on how CAJDBC driver works and using the correct SQL Server JDBC driver.
CAJDBCDriver works by essentially wrapping around the SQL Server JDBC driver (or Oracle JDBC driver). The connection string CAJDBC uses tells it what class to use and what url to use:
@@@<class>@@@<url>
e.g.
@@@com.microsoft.sqlserver.jdbc.SQLServerDriver@@@jdbc:sqlserver://<server>:<port>;DatabaseName=<database>
When a connection is made, CAJDBCDriver checks out the password and then passes the connection url with the correct login details to the SQL Server JDBC driver to handle the database. The data is then passed back to the CAJDBCDriver which in turn passes it to the application requesting it - unless there is an exception during any part of this processes.
It is imperative that you use the correct SQL Server jdbc driver (sqljdbc?.jar) for the version of java jboss is running under. Using an incorrect driver may work when connecting directly to SQL Server and not using CAJDBC driver, but will not work when using the CAJDBC driver.
For example, using sqljdbc.jar with a java version greater than 1.5 causes an exception, this is noted by Microsoft (see link below). However, despite the exception the SQL Server jdbc driver returns the correct data. So, if you are connecting to SQL Server directly with the wrong driver everything appears to work fine, despite the exception - which you did not notice as it is buried in server.log and as the application is working you are not looking for it.
The CAJDBC driver, however, does not ignore the exception thrown by the SQL Server JDBC driver so stops and re-throws the exception and therefore does not return data.
The java version vs SQL Server jdbc driver version information is documented at the link below at the time of writing:
https://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx
How To
In this scenario there are three servers involved:
1. <ENTM SERVER>
The server where Enterprise Manager is installed.
2. <SQL SERVER>
The server running SQL Server. It has the following logins <SQL ADMIN USER> (probably "sa") and the login <JBOSS SQL USER> which is used by the JBoss app to login to this sql server.
3. <JBOSS SERVER>
Is the server running the application on JBoss. There is the basic assumption that the application is already working connecting directly to SQL server on <SQL SERVER>, logging in to sql server with login <JBOSS SQL USER>. A PIM client/agent is installed on this server with PUPM integration with the distribution server set to <ENTM SERVER> (see below) and the local Administrator is an Access Control user (this will be the case if the PIM agent was installed by local\Administrator)
Create The SQL Server Endpoint
Log onto the ENTM, create a Privileged Account Endpoint for sqlserver:
1) On the ENTM server, Privileged Accounts -> Endpoints -> Create Endpoint
2) Select "Create a new object of type Endpoint" and press "OK"
On the General Tab (Note that you will need to change <SQL SERVER ENDPOINT NAME>, <SQL ADMIN USER>, <SQL ADMIN USER PASSWORD>, <server> and <port>):
Name: <SQL SERVER ENDPOINT NAME>
Endpoint Type: MS SQL Server
User Login: <SQL ADMIN USER>
Password: <SQL ADMIN USER PASSWORD>
URL: jdbc:sqlserver://<server>:<port>
Host: sqlserver
Discover <JBOSS SQL USER> account on <sqlserver>
This account is used by the jboss application to connect to the database:
1) Privileged Accounts -> Discovery -> Discover Privileged Accounts Wizard
Search for Accounts of Endpoint Type: MS SQL Server
Where "Endpoint Name": <SQL SERVER ENDPOINT NAME>
Press the "Search" button
2) Look for and select "<JBOSS SQL USER>", and press "Next".
3) Press "Finish"
4) Make sure you can checkout and checkin the privileged account you just created before continuing.
Create the password consumer:
1) Privileged Accounts -> Password Consumers -> Create Password Consumer
2) Select "Create a new object of type Password Consumer" and press "OK"
3) On the General tab:
Name: <JBOSS USER PASSWORD CONSUMER>
Consumer Type: Database (ODBC/JDBC/OLEDB/OCI)
Application Path: *
4) On the Privileged Accounts tab, press "Add Accounts"
Search for, find and select "<JBOSS SQL USER>".
Press Ok.
5) On the Hosts tab, select "Only the specified hosts and host groups below:"
Press the "..." button, and search for and select <JBOSS SERVER>.
6) On the Users tab,
Type in <HOST>\Administrator, where <HOST> is the hostname of <JBOSS SERVER>, and press add.
7) Press submit.
Configure jboss to use the password consumer
1) Stop JBoss
2) Copy the following files to <JBoss>\server\default\lib
<Access Control>\SDK\JDBC\CAJDBCDriver.jar
<Access Control>\SDK\JDBC\CAPUPMClientCommons.jar
<Access Control>\SDK\JDBC\jsafeFIPS.jar
3) Copy the following file to <JBoss>\server\default\deploy
<Access Control>\SDK\JDBC\CAJDBCService.sar
4) Edit the datasource configuration for the application:
Modify:
<connection-url>jdbc:sqlserver://<server>:<port>;DatabaseName=<database></connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
To:
<connection-url>@@@com.microsoft.sqlserver.jdbc.SQLServerDriver@@@jdbc:sqlserver://<server>:<port>;DatabaseName=<database></connection-url>
<driver-class>com.ca.ppm.clients.jdbc.CAJDBCDriver</driver-class>
5) Setup JBoss to run under local\Administrator (Note, you can use a different user, but you will need to figure out the permissions needed to run jboss and also add the user via selang). This is because SYSTEM, as an internal windows user, cannot be setup to retrieve a password via PUPM. JBoss can be setup to be executed either as a service or from the command line using <jboss>\bin\run.bat.
If you are using <jboss>\bin\run.bat, log in as local\Administrator and execute <jboss>\bin\run.bat.
If you are running jboss as a service, open "Services", right click -> Properties on the JBoss service. On the "Log On" tab, select "This account:", click browse and seach for, find and select the local Administrator. Press ok.
6) Delete <jboss>\server\default\tmp and <jboss>\server\default\work. For debugging purposes it might be good to remove/move the logs in <jboss>\log so new logs are created.
7) Start JBoss
8) Make sure the PIM agent is running, seosd -start
9) When JBoss has finished starting, look at the processes in Task Manager and make sure that JBoss' java.exe process is running as local\Administrator as set in step 5
10) Test the application
Hints for if it does not work
Was the application was working properly before you started integrating with SAM/PUMP?
Make sure you can checkout and checkin the <JBOSS SQL USER> privileged account.
If you get errors in the browser like the below, you are using the wrong SQL Server jdbc driver.
2016-05-13 01:19:46,261 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[localhost].[/Test_Jboss].[jsp]] Servlet.service() for servlet jsp threw exception org.jboss.resource.JBossResourceException: Apparently wrong driver class specified for URL: class: com.ca.ppm.clients.jdbc.CAJDBCDriver, url: jdbc:sqlserver://<server>:<port>;DatabaseName=<database>
If you get errors in the browser like:
org.apache.jasper.JasperException: javax.servlet.ServletException: org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '<USER>'. ClientConnectionId:7dd79f20-bf9f-4107-8fb9-e317612591d3); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '<USER>'. ClientConnectionId:7dd79f20-bf9f-4107-8fb9-e317612591d3))
It is probably due to not having permission to checkout the account. Make sure you can checkout and checkin the account.
To trace the PUPM agent.
1) Stop the PIM agent:
secons -s
2) Make sure no AgentManager.exe processes are running (use processes tab in Task Manager)
3) Using regedit, set HKLM\SOFTWARE\ComputerAssociates\AccessControl\PUPMAgent\OperationMode to 2
(NOTE: set it back to 1 to turn trace off, after you are done)
4) Start PIM:
seosd -start
This will create a trace log <AccessControl>\Data\PUPMAgent\PUPMAgent_Trace.log
After testing the application, looking through PUPMAgent_Trace.log is much help in determining the problem. You need to look for RESPONSE_CODE, FAILURE_CODE and FAILURE_MESSAGE.
For example:
18-May-2016 21:57:09: _ProcessCheckoutReply> RESPONSE_CODE --> REJECT
18-May-2016 21:57:09: _ProcessCheckoutReply> FAILURE_CODE --> 14
18-May-2016 21:57:09: _ProcessCheckoutReply> FAILURE_MESSAGE --> Native user and/or native group aren not allowed to access the requested account using this password consumer
The above indicates that JBoss is not running under an AccessControl user (like, for example, local\Administrator) with permission to checkout the account (Users tab in the password consumer).
Looking for messages like the below, can be used to determine what you need to use in the Hosts and Users tabs of the password consumer:
23-May-2016 15:22:21: HandleJavaPlgDatabaseRequest> User name: <HOST>\<USER>
...
23-May-2016 15:22:21: _AddMessageProperties> SOURCE_HOST --> <HOST> was added.
Environment
Component:
Resolution
Feedback
