Thursday, June 27, 2019

Oracle APEX Social Sign-On with Microsoft Azure Active Directory (Again)

As you probably know, in our industry nothing stays the same for long. Less than six months ago I posted about this topic and provided screen shots of the Microsoft UI for registering an application. That UI has been deprecated, due to be gone shortly. I also found that it lacked some of the features that can be found in the MS Azure portal. So, here is a revision to my January 31, 2019 post.

My co-worker Adrian Png has written a few great posts on social sign-on with APEX. I'd like to provide a quick cookbook with some interesting details here. If you get a chance, take a look at Adrian's posts:

Here is the quick cookbook.

On the Microsoft side, you will need a few things. I'll give examples below:

Redirect URI: [APEX URL]/apex_authentication.callback

You may also want a home page url, logo, terms of service and privacy policy urls. Those are optional though.

So, let's assume your APEX url is

Log in to the Azure Portal

Navigate to Azure Active Directory > App registrations

Click New Registration and fill it in

Name is anything you would like to identify your application.

Read the help on Supported account types and choose accordingly. Note: this is an improvement over the earlier registration option described in my January 2019 post.

Redirect URI is your APEX url followed by /apex_authentication.callback:

Copy your Application (Client) ID 

You will need it later

You can optionally add information under the branding section.

Navigate to Certificates and Secrets

Click New Client Secret and add it

Copy the value of your secret. NOTE: You won't have another opportunity to copy this. Make sure you get it now.

The application will need to be able to read minimal information to work. You need to either consent to this on your users' behalf, or allow them to do so themselves. In order to consent, you can edit the user settings and set the first item below to Yes.

If you want to consent on the users behalf, follow this document:

In particular, you would call the URL below, replacing the appropriate elements.

// Line breaks are for legibility only.


If you are just doing the Microsoft Azure Active Directory / Office365 portion, you are done. Just give the Application ID and Client Secret to your APEX developers and they will take it from here.

OK, on to the APEX installation portion.

Here's an important item...the client secret (or password) that we got from the application registration is a long string. I'll use this as my example: myMsFtsecret{hasSpecial~Characters. That's a great secret, but it won't work just as you see it. You'll get the following error:

AADSTS50012: Invalid client secret is provided. Trace ID: 830c95a3-9bb7-9de1-8a22dcca1600 Correlation ID: 572ef7ea--4329-9674-9e9e3468982a Timestamp: 2019-01-15 22:05:41Z

The problem is that the secret needs to be url encoded. So, let's do that first.

Log into the APEX builder and navigate to SQL Workshop > SQL Commands and do what needs to be done:

select apex_util.url_encode('myMsFtsecret{hasSpecial~Characters') the_stuff
  from dual

That's the stuff:

It's reasonable to think that the application ID might need to be encoded as well. I've never seen an application id with special characters, though.

Now navigate to App Builder > Workspace Utilities > Web Credentials. Create credential.

Enter the elements as described. It's worth reading the help.

I put the secret in the comments so you can see it.

Almost there. Now go to your application and create an authentication scheme.

Name: Your Choice

Scheme Type: Social Sign-In

Credential Store: MSAzureCred (the one you created)

Authorization Endpoint URL*:
   Note: depending on who you are allowing to log into your application, you may need to change /common/ to be /yourCompanyDomain/. For example, it may need to be

Token Endpoint URL*:
   Note: depending on who you are allowing to log into your application, you may need to change /common/ to be /yourCompanyDomain/. For example, it may need to be

User Info Endpoint URL:

Scope: User.Read

Username Attribute: userPrincipalName

Convert User Name to Upper Case: Yes   -- you'll thank me for this if you use APEX ACLs.

* This is the error mentioned above that you get if you use /common/ when you should use /yourDomain/:

Sign in
Sorry, but we’re having trouble signing you in.
AADSTS50194: Application 'your-app-id'(YourAppName) is not configured as a multi-tenant application. Usage of the /common endpoint is not supported for such applications created after '10/15/2018'. Use a tenant-specific endpoint or configure the application to be multi-tenant.

You might be done...if you have the root cert for already in your wallet. If not get your dba to add the certificate from to the Oracle wallet.

The other option is to follow Adrian's post (link at the top) to use a reverse proxy instead.

That does it! Maybe...

Another error you might get is:

Need admin approval
YourAppName needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.

If this happens, it is because your organization does not allow users to consent to the application reading their information. In this case, you will need to have an AD Admin (or someone with privs) log in to AD and either give users the ability to consent to the application reading data, or consent for everyone (see document below).

No comments: