The Oracle Wallet is an Oracle technology for storing authentication credentials and related information. In this article, we show two setup options for using the Oracle Wallet with the Oracle JDBC Thin driver for QuerySurge connections.
There are two basic options for this setup: either with a Wallet containing an alias for the credentials or with a Wallet containing the connection URL with the credentials (but no explicit alias; the connection URL functions as the alias). In this article, we describe both options. The setup in QuerySurge for either option uses the QuerySurge's Connection Extensibility feature (see this link for details).
Note: The following examples employ the Oracle JDBC Thin driver (version 18.3 or higher).
With an Alias-bearing Wallet
Oracle Wallets can be set up to contain an alias plus Oracle credentials. In this case, the Wallet alias (denoted as <alias> in the following) represents the access credentials in the Wallet. The Wallet files must be deployed to a known directory of the user's choice - indicated by <wallet dir> in the following.
This Wallet setup requires the following items:
- Oracle JDBC URL: jdbc:oracle:thin:/@<alias>?TNS_ADMIN=<wallet dir>
- A tnsnames.ora file, stored in <wallet dir>
- An ojdbc.properties file, stored in <wallet dir>
Oracle JDBC URL
The Oracle JDBC URL is constructed by substituting '<alias>' in the URL template above with the alias from the Wallet. Note that the substitution must be exact in all respects. Additionally, the '<wallet dir>' value in the template above must be substituted with the directory path to the Wallet files. Note that this is the path to a directory, not to a specific file. On Windows, the path should be constructed using forward-slashes, not the usual backslash. URL examples are:
The tnsnames.ora file
This file should be a standard Oracle tnsnames.ora file, which should be deployed to the <wallet dir> directory. The tnsnames.ora URL entry for the Wallet connection should use the same alias as is used in the Wallet. A template tnsnames.ora entry looks like:
<alias> = (DESCRIPTION = (ADDRESS = (PROTOCOL = <protocol>)(HOST = <host-or-IP>)(PORT = <port>))(CONNECT_DATA = (SERVER = <servicehandler>)(SERVICE_NAME = <servicename>)))
A sample tnsnames.ora entry using this template looks like:
myAlias = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE)))
Note that this entry starts with the alias from the Wallet; this must match the alias in your Wallet and the alias in the URL exactly. Also consider that your tnsnames.ora URL value may differ considerably from this example; you should consult an admin or knowledgeable user for the correct URL expression required for your database connection.
The ojdbc.properties file
The ojdbc.properties file should contain the following line with no modifications either to the file name or the file contents:
oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
This file should be deployed in the <wallet dir> directory.
Setup in QuerySurge
Setup is performed using the Connection Wizard Extensibility option, as noted above.
The Oracle driver class is: oracle.jdbc.driver.OracleDriver.
The URL information, constructed as above, should be entered in the Connection Wizard Extensibility option. It should look like the following:
Note: In the Connection Wizard, no credentials are entered, since the Wallet effectively is your credential.
With a URL-bearing Wallet
Another Wallet setup is for the Wallet to contain the Oracle URL (denoted <oracle url> in the following) along with the credentials. The Wallet files, as in the previous example, are deployed to a known directory of the user's choice - indicated by <wallet dir> in the following.
This Wallet setup requires the following items:
- Oracle JDBC URL: jdbc:oracle:thin:/@<oracle url>
- Java Options: -Doracle.net.wallet_location=<wallet dir>
Oracle JDBC URL
The Oracle JDBC URL is constructed by substituting '<oracle url>' in the URL template above with the URL from the Wallet. Note that the substitution must be exact in all respects. As noted in the example above, your URL value may differ considerably from the following example; you should consult an admin or knowledgeable user for the URL expression required for your database connection. A template URL looks like:
jdbc:oracle:thin:/@(DESCRIPTION = (ADDRESS = (PROTOCOL = <protocol>)(HOST = <host or IP>)(PORT = <port>))(CONNECT_DATA = (SERVER = <servicehandler>)(SERVICE_NAME = <servicename>)))
A sample URL using this template looks like:
jdbc:oracle:thin:/@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE)))
The Java Option Value
This setup requires setting a Java Option on the QuerySurge agent (see below for details). The template for this Option is:
-Doracle.net.wallet_location=<wallet dir>
Note that the option starts with a leading 'dash-capital-D' ("-D") followed by the name of the option ("oracle.net.wallet_location") with no space.
Sample option settings are:
Setup in QuerySurge
Setup is performed using the Connection Wizard Extensibility option, as noted previously.
The Oracle driver class is: oracle.jdbc.driver.OracleDriver.
The URL information, constructed as above, should be entered in the Connection Wizard Extensibility option. It should look similar to the following:
Note: In the Connection Wizard, no credentials are entered, since the Wallet effectively is your credential.
The Java Option (Windows) can be set in the QuerySurge agent configuration executable (QuerySurgeAgentw.exe), found in the QuerySurge agent installation directory. Launch the executable by double-clicking on it (you will need administrative rights). Click on the Java tab. Under Java Options , enter your option. Be careful to include the leading "-D". Click on the Apply button to save your entry, which should look like the following:
Note: In this setup, neither a tnsnames.ora nor a ojdbc.properties file is used.