We are refreshing development databases from production using storage thin cloning. Everything works automatically and part of refresh procedure is also running some SQLPlus scripts, using OS authentication, as SYS.

One database also has APEX, that uses APEX Social Login feature to give users single sign-on for both APEX Builder and the end user application. You can read here how to set it up using Azure SSO. But since APEX is fully inside the database, this means that the production SSO credentials get copied over during database refresh.

I would like to have a SQL script that would run as part of the database refresh procedure, that would replace the APEX SSO credentials. And I’d like that script to run as SYS.

We have been using Single-Sign On for APEX applications for a long time, way before there was social login feature available in APEX itself. We implemented it on web server level in front of APEX. But in order to simplify the webserver setup (mainly to disable sticky load balancer setup) and since APEX for quite some time can do some SSO protocols now internally – we finally moved to APEX social sign-in – using OpenID connector. I blogged about it here earlier.

Enabled it last evening, everything worked fine over night (low use), and suddenly, this morning, when the real use begins, application users started getting the following login failures:

The HTTP request to “https://login.microsoftonline.com/<tenancy_id>/v2.0/.well-known/openid-configuration” failed.

Quite un-informative… why did it fail? This URL is the OpenID discovery URL and the message comes from APEX, not Azure SSO. A few quick checks:

• The URL works, and it returns proper 200 OK status code, even from the database server
• If there were any TLS errors, then the error message would be different
• No new messages in APEX_DEBUG_MESSAGES since the issue started
• Nothing in APEX_WEBSERVICE_LOG
• Nothing in alert.log 🤔
• Changing APEX instance proxy did not help
• TLS wallet was accessible and good
• The error is returned ONLY to application users, even though builder uses the same OpenID Discovery URL – and APEX builder login works without any issues
• And every developer swore, that noone has changed anything today

As a side-note – the end-user applications are quite heavily used and there are hundreds of internal users.

Finally I started creating new Authentications scheme using Generic OAuth2 instead and they seemed to connect to Azure services without issues. But then messages started appearing in APEX_DEBUG_MESSAGES (what triggered that DEBUG logging started writing? accessing applications via Builder?) and hidden deep in messages I found the gem:

Exception in "GET https://login.microsoftonline.com/azure_tenancy_id/v2.0/.well-known/openid-configuration":
Error Stack: ORA-20001: You have exceeded the maximum number of web service requests per workspace. Please contact your administrator.

Looks like, at least in APEX 23.1, calls to OpenID discovery URL count towards the APEX workspace web service request limiter, after increasing the limit the authentication started working again.

exec APEX_INSTANCE_ADMIN.SET_PARAMETER('MAX_WEBSERVICE_REQUESTS', 100000);

According to the documentation MAX_WEBSERVICE_REQUESTS limits the number of outgoing web service requests from each workspace within 24 hours, default 1000.

Problem solved, but it was quite confusing to troubleshoot due to lack of more detailed error messages. I really do not expect authentication hitting a rate limiter and then being completely silent about the underlying cause.