Create a new application in Azure AD for use with APEX social sign in. During the creation of the app, we will need a "Redirect URI". We can use multiple redirect URIs if the Azure AD application is used for multiple APEX applications. - Redirect URI: https://my-app.example.com/ords/apex_authentication.callback We need the following information from the Azure AD application. - Directory (tenant) ID : This will be substituted into some of the URLs below. - Application (client) ID: This is referred to as the Client ID below. - Client Secret: This is referred to as the Client Secret below. The rest of the configuration is APEX related.

Download only the root certificates from these URLs. At the time of writing there were two different root certificates for each URL. I saved the certificates as "digicert-root.cer" and "digicert-root2.cer". Create a wallet on the database server. Add the certificates to the wallet. Don't worry if they are already present.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

https://login.microsoftonline.com/
https://graph.microsoft.com/v1.0/me
https://www.office.com/

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

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

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

Enter the wallet details for the APEX instance. - Log in to the "INTERNAL" workspace. - Manage Instance > Instance Settings. - Click on the "Wallet" tab. - Enter details. Wallet Path: file:/home/oracle/wallet Wallet Password: MyWalletPassword (this can be blank for an auto-login wallet) - Wallet Path: file:/home/oracle/wallet - Wallet Password: MyWalletPassword (this can be blank for an auto-login wallet) - Click the "Apply Changes" button.

Social sign in uses web service calls, so we have to make sure the workspace can cope with lots of we service requests. - Log in to the "INTERNAL" workspace. - Manage Instance > Security > Workspace Isolation. - Set "Maximum Web Service Requests" to 1000000, or an appropriate value for your needs. - Click the "Apply Changes" button.

We create a network ACL for access to the two Azure URLs. Amend the ACL principal to the relevant value for your APEX version.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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;
/

Add the OAuth2 web credentials to access Azure AD. - Log into the workspace that owns the application. - Depending on the APEX version you will go to "Shared Components > Credentials" or "Shared Components > Web Credentials". - Click the "Create" button. - Add Details Name: AzureAD MyApp Static Identifier: AzureAD MyApp Authentication Type: OAuth2 Client Credentials Flow OAuth Scope : blank ClientID or Username: {Client ID from the Azure AD application} Client Secret or Password: {Client Secret from the Azure AD application} Verify Client Secret or Password: {Client Secret from the Azure AD application} - Name: AzureAD MyApp - Static Identifier: AzureAD MyApp - Authentication Type: OAuth2 Client Credentials Flow - OAuth Scope : blank - ClientID or Username: {Client ID from the Azure AD application} - Client Secret or Password: {Client Secret from the Azure AD application} - Verify Client Secret or Password: {Client Secret from the Azure AD application} - Click the "Create" button.

If we need additional AD attributes, we need somewhere to store them. In this example we are using application items. - Shared Components > Application Items - Click the "Create" button. - Name: AD_USERNAME - Scope: Application - Click the "Create Application Item" button. Repeat for all attributes. (AD_USERNAME, AD_FIRSTNAME, AD_LASTNAME, AD_EMAIL, AD_GRAPHQL).

Create an authentication scheme for your application. Substitute the "Directory (tenant) ID" from Azure AD into the URLs where directed. - Shared Components > Authentication Schemes - Click the "Create" button, then the "Next" button. - Enter details. Name: AzureAD MyApp Scheme Type: Social Sign-In Reference Master Authentication Scheme From: blank (only visible on edit) Credential Store : AzureAD MyApp Authentication Provider: Generic OAuth2 Provider Authorization Endpoint URL: https://login.microsoftonline.com/{Directory (tenant) ID}/oauth2/v2.0/authorize Token Endpoint URL: https://login.microsoftonline.com/{Directory (tenant) ID}/oauth2/v2.0/token User Info Endpoint URL : https://graph.microsoft.com/v1.0/me Token Authentication Method: Basic Authentication and Client ID Body Scope: User.Read Authentication URI Parameters: blank Username Attribute: userPrincipalName Convert Username To Upper Case: No Additional User Attributes: blank - Name: AzureAD MyApp - Scheme Type: Social Sign-In - Reference Master Authentication Scheme From: blank (only visible on edit) - Credential Store : AzureAD MyApp - Authentication Provider: Generic OAuth2 Provider - Authorization Endpoint URL: https://login.microsoftonline.com/{Directory (tenant) ID}/oauth2/v2.0/authorize - Token Endpoint URL: https://login.microsoftonline.com/{Directory (tenant) ID}/oauth2/v2.0/token - User Info Endpoint URL : https://graph.microsoft.com/v1.0/me - Token Authentication Method: Basic Authentication and Client ID Body - Scope: User.Read - Authentication URI Parameters: blank - Username Attribute: userPrincipalName - Convert Username To Upper Case: No - Additional User Attributes: blank - Click the "Create Authentication Scheme" button. - Edit the newly created Authentication Scheme to edit the "PL/SQL code" parameter. Only visible on edit. The "PL/SQL Code" option allows us to set the values of application items returned by the GraphQL call. Some parameters are available by default but some are not. The "Additional User Attributes" setting implies you can use this to return parameters that aren't present by default in the "v1.0" API, but it only seems to return default parameters, regardless of the setting. The "POST_AUTHENTICATE" procedure below sets the application items with the default values. It also makes an additional call to return the local AD username (onPremisesSamAccountName). Set the "PL/SQL Code" value as follows. - Amend the following settings. Post-Authentication Procedure Name: post_authenticate Switch in Session: Enabled - Post-Authentication Procedure Name: post_authenticate - Switch in Session: Enabled - Click the "Apply Changes" button.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

procedure post_authenticate is
  l_clob  clob;
begin
  -- Defaults
  :AD_FIRSTNAME := apex_json.get_varchar2('givenName');
  :AD_LASTNAME  := apex_json.get_varchar2('surname');
  :AD_EMAIL     := apex_json.get_varchar2('mail');

  -- Custom
  begin
    l_clob := apex_web_service.make_rest_request(
      p_url         => 'https://graph.microsoft.com/v1.0/me?$select=onPremisesSamAccountName',
      p_http_method => 'GET'
    );
    :AD_GRAPHQL  := l_clob;
    :AD_USERNAME := json_value(l_clob, '$.onPremisesSamAccountName');
  exception
    when others then
      :AD_GRAPHQL  := dbms_utility.format_error_backtrace;
      :AD_USERNAME := null;
  end;
end post_authenticate;

The application authentication should work using Azure AD to authentication now.

If you get errors, do the following from the App Builder. - Click on the administration icon (a person holding a spanner) and select the "Monitor Activity" menu option. - Click the "By View" link. - Find an occurrence of the incident, and click on the "Debug ID" link for the incident. - Read down the log, and try to identify the error. For more information see: - Social Sign In with Azure - Oracle APEX Social Sign-in Authentication Scheme - APEX : All Articles Hope this helps. Regards Tim...