Keycloak docker container not working with Oracle DB

Configuring the server
1

Hi, I’m trying to use the keycloak docker image (https://hub.docker.com/r/jboss/keycloak/) with an Oracle DB but I’m stuck.

The Oracle DB is a PDB, which apparently isn’t currently supported because the connection string in the oracle/change-database.cli is as follows:

jdbc:oracle:thin:@${env.DB_ADDR:oracle}:${env.DB_PORT:1521}:${env.DB_DATABASE:XE}${env.JDBC_PARAMS:}

so uses the :<SID> syntax and is not possible to switch to the /<SERVICE_NAME> syntax that is necessary to connect to PDBs. So I cloned the repository and I altered the oracle/change-database.cli file setting the connection string to:

jdbc:oracle:thin:@${env.DB_ADDR:oracle}:${env.DB_PORT:1521}${env.ORACLE_DB_SEPARATOR::}${env.DB_DATABASE:XE}${env.JDBC_PARAMS:}

(i.e. changed :${env.DB_DATABASE:XE} to ${env.ORACLE_DB_SEPARATOR::}${env.DB_DATABASE:XE}. I then modified the docker-entrypoint.sh from this:

    oracle)
        DB_NAME="Oracle"
        ;;

to this:

    oracle)
        DB_NAME="Oracle"
        if [[ -z ${_ORACLE_DB_SEPARATOR:-} ]]; then
            ORACLE_DB_SEPARATOR=":"
        fi
        ;;

which should take care to specify the default env var.

My docker-compose.yml then specifies the following variables:

      DB_VENDOR: ORACLE
      DB_ADDR: <MY-IP>
      DB_PORT: 1521
      DB_DATABASE: keycloak
      DB_USER: keycloak
      DB_PASSWORD: password
      KEYCLOAK_USER: admin
      KEYCLOAK_PASSWORD: admin
      ORACLE_DB_SEPARATOR: "/"

So this should yield a connection string of:

jdbc:oracle:thin:@<MY-IP>:1521/keycloak

Using sqlplus I cannot connect the user keycloak/password using the connection url @<MY-IP>:1521/keycloak so, IMHO, this should work… but it doesn’t:

keycloak_1     | Caused by: javax.resource.ResourceException: IJ031084: Unable to create connection
keycloak_1     |        at org.jboss.ironjacamar.jdbcadapters@1.4.22.Final//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:345)
keycloak_1     |        at org.jboss.ironjacamar.jdbcadapters@1.4.22.Final//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:352)
keycloak_1     |        at org.jboss.ironjacamar.jdbcadapters@1.4.22.Final//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:287)
keycloak_1     |        at org.jboss.ironjacamar.impl@1.4.22.Final//org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1322)
keycloak_1     |        at org.jboss.ironjacamar.impl@1.4.22.Final//org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:499)
keycloak_1     |        at org.jboss.ironjacamar.impl@1.4.22.Final//org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:632)
keycloak_1     |        at org.jboss.ironjacamar.impl@1.4.22.Final//org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:604)
keycloak_1     |        at org.jboss.ironjacamar.impl@1.4.22.Final//org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:624)
keycloak_1     |        ... 50 more
keycloak_1     | Caused by: java.sql.SQLException: Listener refused the connection with the following error:
keycloak_1     | ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
keycloak_1     |  
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
keycloak_1     |        at org.jboss.ironjacamar.jdbcadapters@1.4.22.Final//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:321)
keycloak_1     |        ... 57 more
keycloak_1     | Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
keycloak_1     | ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
keycloak_1     |  
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:271)
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.net.ns.NSProtocol.connect(NSProtocol.java:317)
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
keycloak_1     |        at com.oracle.jdbc@12.2.0.1.0//oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
keycloak_1     |        ... 61 more

The Oracle PDB keycloak should be up:

$ lsnrctl status 

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 01-DEC-2020 12:09:46

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                01-DEC-2020 10:37:12
Uptime                    0 days 1 hr. 32 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/6020f0c150b5/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=6020f0c150b5)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/IRP/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "IRP" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
Service "IRPXDB" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
Service "b565c8c1ba6b097fe053020011ac99c9" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
Service "keycloak" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
The command completed successfully

Other info:


LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 01-DEC-2020 12:10:27

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "IRP" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:10 refused:0 state:ready
         LOCAL SERVER
Service "IRPXDB" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: 6020f0c150b5, pid: 2192>
         (ADDRESS=(PROTOCOL=tcp)(HOST=6020f0c150b5)(PORT=37305))
Service "b565c8c1ba6b097fe053020011ac99c9" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:10 refused:0 state:ready
         LOCAL SERVER
Service "keycloak" has 1 instance(s).
  Instance "IRP", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:10 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
2

How did you solve that?
I have same problem

3

I was able to make keycloak container work with Oracle with the following:

  1. Clone the Keycloak container definition

  2. copy the standalone.xml configuration

  3. edit the configuration for the KeycloakDS datasource. I replaced the connection url with something like:

    <connection-url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=${env.DB_PROTOCOL:TCP})(PORT=${env.DB_PORT:1521})(HOST=${env.DB_ADDR:oracle}))(CONNECT_DATA=(SERVICE_NAME=${env.DB_DATABASE})))${env.JDBC_PARAMS:}</connection-url>

setting <driver>oracle</driver> and <check-valid-connection-sql>SELECT 1 FROM dual</check-valid-connection-sql>.

In the <drivers> section I replaced the org.postgresql.jdbc with:

<driver name="oracle" module="com.oracle.jdbc">
    <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
</driver>

I also have a copy of standalone.xml for postgres.

My Dockerfile looks like this:

ARG db_type

FROM jboss/keycloak:12.0.4 as base

USER jboss

COPY custom_theme $JBOSS_HOME/themes/custom_theme

# custom keystore
COPY dev_keycloak.jks $JBOSS_HOME/standalone/configuration/keystores/dev_keycloak.jks

FROM base AS keycloak-oracle

# oracle_standalone.xml modified as described above, same with the -ha version.
COPY oracle_standalone.xml $JBOSS_HOME/standalone/configuration/standalone.xml
COPY oracle_standalone-ha.xml $JBOSS_HOME/standalone/configuration/standalone-ha.xml
COPY ojdbc8.jar $JBOSS_HOME/modules/system/layers/base/com/oracle/jdbc/main/driver/ojdbc.jar

FROM base AS keycloak-postgres

COPY pg_standalone.xml $JBOSS_HOME/standalone/configuration/standalone.xml
COPY pg_standalone-ha.xml $JBOSS_HOME/standalone/configuration/standalone-ha.xml

FROM keycloak-${db_type} AS final

I use a multi-stage dockerfile to select oracle/postgres via the db_type build argument, but if you only care about oracle you can simplify that.

Hope this helps!

1 Like