The Pitfalls of APEX Authentication with OAuth2

After another successful development and deployment of a client’s Oracle APEX application, the final part of the project was to implement an authentication scheme that linked to the client’s network user accounts. The client had just started using Azure AD as their user identity provider and we had implemented authentication with Azure via OAuth2 on some in-house applications so we thought it should be straightforward. Unfortunately, we came to find it was not and the instructions we followed when setting up the authentication on our own applications threw up a few issues that we had to resolve.

The instructions followed can be found at https://knowledgebase.mt-ag.com/ords/f?p=311:1:0::::P1_SEARCH:sso, but here are the high-level steps for setting up Oracle APEX authentication with OAuth2 using Azure AD are as follows:

1. Register the application with Azure – this will give you the IDs, secrets and endpoint URLs required to setup the authentication scheme
2. Create Access Control Lists (ACLs) on the database
3. Add the Baltimore Cybertrust Root certificate to the Oracle Wallet (found at https://www.digicert.com/kb/digicert-root-certificates.htm)
4. Ensure APEX is aware of the wallet location and password in the Instance Settings in the INTERNAL workspace

1. Create Web Credentials in the APEX Workspace with details of the Azure AD client and client secret

1. Create the Authentication Scheme within the APEX application

These six steps are all that should be needed to implement an authentication scheme in APEX that uses OAuth2 and Azure AD. There are many websites out there that give these instructions or similar, giving you the “happy path” to implementing this authentication scheme. But there were not many that went through potential issues you may encounter along the way and how to resolve them.

Now, because this was to be implemented on the client network where the application had been deployed to, we had limited visibility and access to the database servers and the Azure AD client and would involve two different client employees from two different departments in two different countries. That in itself caused a bit of a logistical problem.

The first issue we encountered returned the following errors:

ORA-29273: HTTP request failed

ORA-12535: TNS:operation timed out

This leads us to think there was an issue with the certificate not allowing the application to talk to the Azure AD client so this led us to investigate which certificate was present, was it installed correctly and in the right place and we had to involve Explorer DBAs who had experience dealing with this type of thing. We uninstalled and reinstalled the certificate stated in the instructions, but to no avail. We were then notified that the DB server was locked down, so we tested the ACLs to see if they could communicate to the relevant URLs and when they could not, we realised the issue was that the DB could not connect to the internet.

To get over this issue, the client DBA needed to create a Proxy server to allow the DB server to connect to the internet. Once this was created, the ACLs could then connect to their respective URLs. We retested the authentication and another error was returned:

ORA-29273: HTTP request failed

ORA-29024: Certificate validation failure

This meant we needed to look at the certificate again and involve a DBA from Explorer, again. After some research, the Explorer DBA found out there were a suite of certificates available from Microsoft here: https://docs.microsoft.com/en-us/microsoft-365/compliance/encryption-office-365-certificate-chains?redirectSourcePath=/en-us/article/office-365-certificate-chains-0c03e6b3-e73f-4316-9e2b-bf4091ae96bb&view=o365-worldwide. These were downloaded, but when it came to installing them into the Oracle Wallet we encounter further issues in that some of the certificates from the downloads did not install correctly and returned errors about not being able to install.

The reason for this, we found, was that they needed to be installed in a certain sequence. The Explorer DBA first installed the certificate suites found on the link above, Root certificates first followed by the Intermediate certificate suite after. Once this was completed and the certificates were installed, the authentication scheme worked perfectly and the client was very happy.

Author: Gavin Bell

Job Title: Development Consultant

Bio: Gavin is a Development Consultant at DSP-Explorer. Having attained a BSc in Computing he embarked on a career working with Oracle technologies and specialising in SQL and PL/SQL programming. In previous roles he has worked with Oracle Forms and Reports (v9-12), Oracle Retail (v13) and APEX (v4.2-18.2). Here at DSP-Explorer, he is part of a highly skilled development team providing APEX development solutions and training to both UK and international businesses.