Configuring Connections: IBM DB2 With Security Mechanism

Versions: 8.0+

DB2 Security Mechanism Values

Security Mechanism	ID	Description
CLEAR_TEXT_PASSWORD_SECURITY	3	User ID and password
USER_ONLY_SECURITY	4	User ID only
ENCRYPTED_PASSWORD_SECURITY	7	User ID, encrypted password
ENCRYPTED_USER_AND_PASSWORD_SECURITY	9	Encrypted user ID and password
KERBEROS_SECURITY	11	Kerberos
ENCRYPTED_USER_AND_DATA_SECURITY	12	Encrypted user ID and encrypted security-sensitive data. This value applies to connections to DB2 for z/OS only.
ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY	13	Encrypted user ID and password, and encrypted security-sensitive data
PLUGIN_SECURITY	15	Plugin-in security
ENCRYPTED_USER_ONLY_SECURITY	16	Encrypted user ID

Configuring QuerySurge Connections: DB2 with Security Mechanism

When specifying a security mechanism that uses encryption, you will have to add an external encryption library to your QuerySurge installation, since the default does not support everything necessary. Here we will be adding the Bouncy Castle encryption libraries to QuerySurge:

1.    Download the Bouncy Castle library from:

http://central.maven.org/maven2/org/bouncycastle/bcprov-ext-jdk15on/1.51/bcprov-ext-jdk15on-1.51.jar

2.    Navigate to the <QuerySurge install dir>\agent\jdbc directory.

(In a standard installation, this directory path is:

C:\Program Files\QuerySurge\agent\jdbc.) 

Copy the bcprov-ext-jdk15on-1.51.jar file to this location. 

3.    Navigate to the <QuerySurge install dir>\java\lib\security\ directory and modify the java.security file by adding the following security provider and renumbering the existing providers:

security.provider.1=org.bouncycastle.jce.provider.BouncyCastleProvider

4.  Save the file and continue on to the Connection Setup.

Configuring QuerySurge Connections: DB2 with Security Mechanism

When you create a QuerySurge Connection, the Connection Wizard will guide you through the process. Different types of QuerySurge connections require different types of information.

For a DB2 Connection with encryption, you will need the following information (check with a DBA or other knowledgeable resource in your organization):

·         Database login credentials (ID and Password)

·         Server Name or IP address of the DB2 Server (e.g. db21.myserver.com, or 192.168.0.255)

·         The Port for your DB2 database (50000 is the default port)

·         The DB2 Database name

·         The DB2 Security Mechanism

Launch the Connection Wizard

1.       Log into QuerySurge as an Admin user.

2.       To configure a Connection, select Configuration > Connection in the Administrative View tree (at the left).

3.       Click on the Add button at the bottom left of the page to launch the Connection Wizard. Click Next.

Note: Check the Advanced Mode checkbox for access to advanced features.

4.       Provide a name for your connection. Select * All Other JDBC Connections as the Data Source. Click Next.



Note: The DB2 JDBC driver is not bundled with QuerySurge, so you will need to install the driver with all your Agents. See Adding a JDBC Driver to your QuerySurge Agent for instructions.

5.       Provide the Driver Class for the JDBC driver you are using, and then click Next.

For the IBM JCC Universal Driver the driver class is:
com.ibm.db2.jcc.DB2Driver

6.       Provide the connection information to your database. This includes the Connection URL (which may contain the Server name or IP address and the port), the login credentials, and a Test Query that will run to verify the Connection details. Then click Next.

Required fields for your Connection are marked by an *.

For the IBM JCC Universal Driver the driver class is:

jdbc:db2://<ServerName>:<Port>/<DatabaseName>:securityMechanism=<Security_Mech_ID>;

7.       Click on Test Connection.



Note: You must have an Agent running with the driver for this Connection deployed in order to test the Connection.

8.       Save on the Connection.

9.       Congratulations! You’ve created a QuerySurge Connection. Make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. (See Adding a JDBC Driver to your QuerySurge Agent for instructions.)