solidDB Help : Samples : External authentication for JDBC connections sample
External authentication for JDBC connections sample
This sample demonstrates the configuration steps (on Windows) that are required to externally- authenticate solidDB users who use a JDBC connection to connect to the solidDB server.
The files for this sample are located in the soliddb-installdir\samples\jdbc directory.
Note To run this sample, the following conditions must be true:
▪ Your solidDB license file must have the solidDB LLA component enabled and the LLA library (ssolidacver.dll) installed. Note that the solidDB server does not have to be an LLA server in order to configure external authentication, however, the LLA library includes functions that the solidDB JDBC driver requires in order for a JDBC client user to be externally-authenticated, and so the LLA library must be installed on the JDBC client.
▪ You must install JDK 1.7 or later and set the PATH and CLASSPATH settings for the Java compiler.
▪ You must have an operating system account on the system where you run the samples, and know the user name, password and domain name.
For general information about running the samples, see Samples.
The example includes the following steps:
▪ configuring external authentication for the solidDB server, solidDB tools (and ODBC clients), and JDBC client,
▪ creating a database with an internally-authenticated database administrator,
▪ creating an externally-authenticated user using solidDB SQL Editor (solsql),
▪ compiling a sample application (samples\jdbc\sample1.java),
▪ connecting to solidDB server with a JDBC connection as an externally-authenticated user.
To run the sample, complete the following steps:
Preparing the environment
1 In order not to affect the authentication associated with the other JDBC samples (see solidDB JDBC driver samples), copy the samples\jdbc folder to samples\jdbc_extauth.
In this example, the solid.ini file in samples\jdbc_extauth\run functions as both the server-side and client-side configuration file.
Note The [Client] section parameters are not needed for JDBC connections. However, the parameter settings are needed if you want to test that you can connect to solidDB server with solsql as an internally-authenticated user (see step 2).
Running the sample
1 Start solidDB server and create a new database with an internally-authenticated DBA with user name dba and password dba by running the following commands from the command prompt:
cd solidDB-installdir\samples\jdbc_extauth\run ..\..\bin\solid -c run -Udba -Pdba -Cdba
The solidDB server starts, listening at tcp 2315.
2 Connect to the solidDB server by using the DBA account. by running the following command:
..\..\bin\solsql -c run "tcp 2315" dba dba
If the solsql connection fails with the error message SQLAllocEnv, check that the solsql working directory contains the solid.ini file with the Client.UseCryptoLib parameter set correctly (and the Client.CryptoLibPath parameter if the OpenSSL libcrypto library is not available on the operating system path), see Client section.
3 Create an externally-authenticated user by running the following command in solsql:
CREATE USER your-username IDENTIFIED EXTERNALLY;
4 Check the authentication types of the users by querying the SYS_USERS system table.
For example:
SELECT ID, NAME, AUTHENTICATION FROM SYS_USERS;
ID NAME AUTHENTICATION -- ---- -------------- 1 DBA 0 4 your-username 1 2 rows fetched.
Value 1 in the column AUTHENTICATION means that the user is authenticated externally.
5 Restart solsql and log in as the externally-authenticated user by running the following command:
solsql "tcp 3315" your-usernameyour-password
6 Modify sample1.java by adding the following JDBC properties:
props = new Properties(); props.put("StatementCache","32"); // existing property in sample1.java props.put("solid_crypto_path","solidDB-installdir\bin"); props.put("solid_use_strong_encryption", "yes");
7 Compile sample1.java. by running the following command:
javac sample1.java
8 Execute the sample application by running the following command:
If the login details are correct, the application continues with the following messages:
Attempting to connect :jdbc:solid://host:port/your-username/your-password SolidDriver succesfully connected. Query executed and result set obtained. Obtaining metadata information.
Metadata information for columns is as follows:
Column i:1 TABLE_SCHEMA,12,WVARCHAR Column i:2 TABLE_NAME,12,WVARCHAR ... Row 89 : _SYSTEM SYS_SYNC_REPLICA_PROPERTIES BASE TABLE Row 90 : _SYSTEM SYS_BACKGROUNDJOB_INFO BASE TABLE Result set dumped. Sample application finishes.
In some environments, you might need to provide the OpenSSL libcrypto and LLA library path by using the -Djava.library.path when you start the application. For example: