[Cas 5.1.8] Unable to connect to Database for authentication.

[Ranga Nadha]

Hi Everyone,

We are using CAS 5.1.8 and I am trying to connect to the database for authentication purpose.

I have followed the below procedure but I am getting exceptions.

• Downloaded the CAS 5.1.8
  
• Modify POM.xml with the following dependency
  

<dependency>
 <groupId>org.apereo.cas</groupId>
 <artifactId>cas-server-support-jdbc</artifactId>
 <version>${cas.version}</version>
 <exclusions>
 <exclusion>
 <artifactId>log4j-slf4j-impl</artifactId>
 </exclusion>
 <exclusion>
 <groupId>org.codehaus.groovy</groupId>
 <artifactId>groovy-jsr223</artifactId>
 </exclusion>
 </exclusions>
</dependency>
<dependency>
 <groupId>org.apereo.cas</groupId>
 <artifactId>cas-server-support-jdbc-drivers</artifactId>
 <version>${cas.version}</version>
</dependency>
<dependency>
 <groupId>org.apereo.cas</groupId>
 <artifactId>cas-server-support-jdbc-authentication</artifactId>
 <version>${cas.version}</version>
</dependency>

• Build cas project using mvn clean install.
  
• Take the war file and deploy in tomcat.
  
• In the extracted cas war.
  
• Go to Tomcat/webapps/cas/WEB-INF/classes/appliation.properties and add the below properties.
  

cas.authn.jdbc.query[0].sql=SELECT * FROM USERS WHERE USER_ID=?
cas.authn.jdbc.query[0].url=jdbc:oracle:thin:@xxx.xxxx.local:1521:db12c
cas.authn.jdbc.query[0].dialect=org.hibernate.dialect.Oracle12cDialect
cas.authn.jdbc.query[0].user=xxxx
cas.authn.jdbc.query[0].password=xxxx
cas.authn.jdbc.query[0].driverClass=oracle.jdbc.OracleDriver
cas.authn.jdbc.query[0].fieldPassword=PASSWORD
cas.authn.jdbc.query[0].passwordEncoder.type=DEFAULT
cas.authn.jdbc.query[0].passwordEncoder.characterEncoding=UTF-8
cas.authn.jdbc.query[0].passwordEncoder.encodingAlgorithm=SHA1
cas.authn.jdbc.query[0].passwordEncoder.secret=
cas.authn.jdbc.query[0].passwordEncoder.strength=16


cas.authn.accept.users=
logging.level.org.apereo=DEBUG

I am getting the below error

2018-08-21 21:00:02,828 DEBUG [org.apereo.cas.adaptors.jdbc.config.CasJdbcAuthenticationConfiguration] - <Created and mapped principal attributes [{givenName=givenNa

me, sn=sn, cn=commonName}] for [jdbc:oracle:thin:@xxxx.xxxx.local:1521:dev12c]...>

2018-08-21 21:00:02,870 ERROR [com.zaxxer.hikari.HikariConfig] - <Failed to load class of driverClassName oracle.jdbc.OracleDriver in HikariConfig class classloader

ParallelWebappClassLoader

  context: cas

  delegate: false

----------> Parent Classloader:

java.net.URLClassLoader@13221655

>

2018-08-21 21:00:02,870 ERROR [org.apereo.cas.configuration.support.Beans] - <Error creating DataSource: [Failed to load class of driverClassName [oracle.jdbc.Oracle

Driver] in either of HikariConfig class loader or Thread context classloader]>

Can any one help to resolve this issue.

Thanks in advance,

Thanks,

Pandu Ranga T.

[Andy Ng]

Hi Pandu,

I have also encounter a similar problem before (mine is with another database driver), and I find out that I need to include your database driver into your pom, which is also likely to be your problem.

You can try the following:

- find the driver by seaching "oracle.jdbc.Oracle maven"

 - I find this "https://mvnrepository.com/artifact/com.oracle/ojdbc14"

- Copy one of the artifacts version into your pom.xml

- Restart and see if the error is fixed

Hopefully this helps you!

Cheers!

- Andy

[Colin Wilkinson]

As Andy Ng has already stated you need to include the database driver, oracle have there own maven repository that all you need is logon credentials to use. Just be careful oracle include alot of dependencies that are not required and actually cause problems. Specifically exclude "com.oracle.jdbc.xmlparserv2" it complains about a missing dependency.

Oracle maven repository

[Ranga Nadha]

Hi Andy/Colin,

Thanks for the reply, I have added the OJDB dependency in the pom.xml and the exception is gone.

Now CAS is connecting to the database for authentication purpose.

But the problem is we have a custom password encoder, MD5,SHA-256 based on the third party application rule the encoding format will decide.

So, our requirement is to call the third party application passwordEncoding class for the encodingAlgorithm.

Could you please tell me how to set the third-party class in the application.properties for passwordEncryption.

Thanks,

Pandu Ranga T.

[Robert Bond]

Here is what we added for oracle setup:

sadf

<dependency>

<groupId>org.apereo.cas</groupId>

<artifactId>cas-server-support-jdbc-drivers</artifactId>

<version>${cas.version}</version>

</dependency>

<dependency>

<groupId>com.oracle.jdbc</groupId>

<artifactId>ojdbc8</artifactId>

<version>12.2.0.1</version>

</dependency>

Next add the ojdbc8.jar into the folder jdbc/oracle/ojdbc8.jar

We are using Oracle for just attribute resolution and AD LDAP for credentials. 

Here is our attribute resolution for Oracle:

# # Oracle Database Attribute query

cas.authn.attributeRepository.jdbc[0].attributes.GOBUMAP_UDC_ID= UDC_IDENTIFIER

cas.authn.attributeRepository.jdbc[0].username= GOBTPAC_EXTERNAL_USER

cas.authn.attributeRepository.jdbc[0].singleRow= true

cas.authn.attributeRepository.jdbc[0].sql= select GOBUMAP_UDC_ID from GOBUMAP INNER JOIN GOBTPAC on GOBTPAC.GOBTPAC_PIDM = GOBUMAP.GOBUMAP_PIDM where upper(GOBTPAC_EXTERNAL_USER)=upper(?)

cas.authn.attributeRepository.jdbc[0].GOBTPAC_EXTERNAL_USER= uid

cas.authn.attributeRepository.jdbc[0].healthQuery= SELECT 1 FROM DUAL

cas.authn.attributeRepository.jdbc[0].isolateInternalQueries= false

cas.authn.attributeRepository.jdbc[0].url= jdbc:oracle:thin:@//hostname:1521/PROD.example.edu

cas.authn.attributeRepository.jdbc[0].dialect= org.hibernate.dialect.Oracle12cDialect

cas.authn.attributeRepository.jdbc[0].user= casUDCuser

cas.authn.attributeRepository.jdbc[0].password= asdf

cas.authn.attributeRepository.jdbc[0].driverClass= oracle.jdbc.OracleDriver

Hope this helps!

[Andy Ng]

Hi Pandu,

The requirement of "Custom Password Encoder with MD5,SHA-256 based on the third party application" doesn't seems to be available out of the box yet in latest CAS version, 

[as seem here: "https://apereo.github.io/cas/5.3.x/installation/Configuration-Properties-Common.html#password-encoding"]

What you can try is create your custom password encoding by following the paragraph under the link above. I do think it will take some time to implement such feature.

Cheers!

- Andy

[Ranga Nadha]

Hi Andy,

Thanks, for the reply.

I saw that link, but is there any example to implement the encoding and configuring it in the application.properties. 

Thanks,

Pandu Ranga T.