Configure Azure AD Single Sign On for On-prem Oracle APEX

This article describes the steps to configure Azure AD single sign on for OP Oracle Apex application.

Reference links:

Table of Contents

Configure Oracle Database to Enable SSL Connection to Microsoftonline

Connection is established between OP Oracle DB and microsoft online. By default OP Oracle database doesn’t permit http calling to outside website through SSL connection. To do it, it is necessary to take following actions.

Download SSL certificate from microsoft website.
Create DB wallet and import certificates.
Create and assign ACL.
Verify HTTPS connection.

Download SSL Certificates.

Open microsoft website login.microsoftonline.com from Firefox, open certificate detail.

Choose Root CA

Download single certificate

Repeat above steps for another website graph.microsoft.com. Rename these 2 certificate files and place them in OS folder.

Create DB Wallet and Import Certificates

To enable access to website using SSL, it is necessary to import the websites’ SSL certificate into Oracle database wallet.

Create wallet file:

mkdir -p /home/oracle/wallet
cd /home/oracle/wallet

orapki wallet create -wallet /home/oracle/wallet -pwd MyWalletPassword -auto_login

Import the certificates. Repeat it for both websites.

$ORACLE_HOME/bin/orapki wallet add -wallet /home/oracle/wallet \
  -trusted_cert -cert "/home/oracle/wallet/digicert-root.cer" -pwd MyWalletPassword

Create and Assign ACL

In Oracle 11gR2, use following scripts to create and assign ACL.

Create ACL:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => 'APEX_200100_TO_AZURE.xml', -- File name
    description => 'allows APEX access to Azure',
    principal => 'APEX_200100', -- APEX DB user name, must be in upper case
    is_grant => TRUE,
    privilege => 'connect');
END;
/

Assgn ACL:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( -- Creates the first target host
    acl => 'APEX_200100_TO_AZURE.xml',
    host => 'login.microsoftonline.com');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( -- Creates the second target host
    acl => 'APEX_200100_TO_AZURE.xml',
    host => 'graph.microsoft.com');
END;
/

Assign wallet ACL. Make sure the wallet path start with “file:/“

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_wallet_acl ( -- Creates the first target host
    acl => 'APEX_200100_TO_AZURE.xml',
    wallet_path => 'file:/home/oracle/wallet');
END;
/

In Oracle 21c, use following scripts to create and assign ACL.

declare
  l_username varchar2(30) := 'APEX_200200';
begin
  dbms_network_acl_admin.append_host_ace(
    host => 'login.microsoftonline.com',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => l_username,
                        principal_type => xs_acl.ptype_db));

  dbms_network_acl_admin.append_host_ace(
    host => 'graph.microsoft.com',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => l_username,
                        principal_type => xs_acl.ptype_db));
  commit;
end;
/

Verify HTTPS connection.

To ensure above setups are completed without error, make a call to microsoft website using PLSQL:

Configure Azure AD to Create New Application for APEX SSO

Create New Application

Provide APEX callback URL in recirect URI list. During SSO, after authentication completes, Azure will redirect back to APEX application. If this is not setup, it may cause error during the redirect activity.