Seamless authentication to the Oracle Database with SQLDeveloper, 23ai JDBC drivers and Azure Entra ID

In a previous blog back in 2023, we integrated the latest SQLDeveloper 23 to utilize Entra ID issued OAuth2 tokens that allowed us to authenticate to an OCI Oracle Database – Autonomous Database Serverless. If you were one of the many who took the time to explore this feature, then chances are you likely had to develop a nifty token acquisition method in the form of a custom script which was designed to help simplify the login flow for your database administrators.  Fortunately for us all, database security product management and engineering teams were already hard at-work developing complementary client-side libraries to enhance seamless and secure access to the database.

23ai JDBC seamless authentication with OCI IAM and Azure AD

These enhanced JDBC libraries have introduced some fundamental changes that extend the clients ability to retrieve the required configuration parameters for a database connection through several mechanisms. These new features are an addition to the existing connection string methods that exist in previous versions of the JDBC drivers, which you are most familiar with. You’ll also find in the official documentation that the out-of-the-box common library contains the file-based and http-based config providers.

If you want to learn more about the other features, the blog above continues to further explain how the new JDBC drivers have been built for extensibility.  It also highlights the methods to centrally locate database connection information using various multi-cloud services such as OCI Object Storage, OCI Vault, Azure App Config, and more.

Along with the config provider, we will be also be utilizing a Resource Provider. As explained in the blog, a Resource Provider accepts and uses the parameters from the config parameters for brokering the authentication to the identity provider. For this demonstration, we are using Entra ID as our token provider for identity which means we will need to build and utilize the Azure resource provider extensions.

Setup

For this blog, I picked up the latest release of SQLDeveloper 24 here.

The first step that we need to do is to acquire and build the JDBC extensions from the Oracle public github repository.  You can either clone this repository using git or simply just download the entire bundle.

https://github.com/oracle/ojdbc-extensions/tree/v1.0.1

Next, you will need to build the project from root of the project. In this case, you will make sure you are inside the correct directory – ojdbc-extensions-1.0.1.  Once you are in the directory, if you list the contents – you should see the following.

First off, you will need to make sure you have Apache Maven installed which is an open source build utility. This utility is responsible for building, retrieving dependencies, and installing the libraries into your local maven repository.

mvn clean install

If you notice that during the maven build, it does download quite a few dependencies from public repositories.  Most importantly, it downloads the core Azure SDK and a number of other dependencies that the Azure SDK relies upon.  The Oracle JDBC resource provider for Azure makes use of the Azure SDK in order to facilitate the interaction for login – much similar to how the Azure CLI works.

If the build is successful, then you can continue to move to the following steps.

Note: I chose to use the branch version 1.0.1 since I know it has been stable for a few months – chances are newer versions will be available as development continues.  The version and dependencies are important because it could potentially impact the libraries that are referenced in the SQLDeveloper configuration performed later.

Update SQLDeveloper 24 configuration file

Now that you have all of the libraries that are needed for the SQLDeveloper runtime, we will be updating the SQLDeveloper 24 configuration to load those newly acquired libraries into runtime at the next startup of the application.  By default, the libraries will be in your home directory in a .m2 repository.

Next I will need to identify the SQLDeveloper configuration location, because I need to add the following AddJavaLibFile entries into the SQLDeveloper product configuration file. Since I am working on a MacOS, the default product configuration file is in the following location:

~/.sqldeveloper/24.3.1/product.conf

Configure the OCI API Gateway to host Database Connection Details

For this blog, I chose to use the HTTP-based config provider because it also demonstrates how you can store database connection information in a centrally accessible and secure location – almost like looking up an address in a phonebook.  This provides a simple, cloud-native way to host this information and highlight the use of the HTTP config provider. However, you may opt to use another web server or another config provider type such as Azure App Config, Azure Secrets, OCI Secrets, or File-based.

First, you will need to have a database that has been configured and enabled for Azure Entra ID tokens.  For simplicity, I already have a database that is configured to accept Entra ID tokens which I used in my previous blogs.  If you need assistance – you can find the configuration steps documented here.  

Then, you will need to contruct the contents of JSON configuration payload for SQLDeveloper to retrieve and interpret.

Service_Name – this can obtained from the OCI Database Connection 

TOKEN_AUTH – AZURE_INTERACTIVE, this instructs the JDBC resource provider to launch a browser authentication to obtain the OAuth2 token

AZURE_DB_APP_ID_URI – this comes from the Azure Entra ID Portal under App Registrations

To achieve the goal of hosting this configuration in a centrally accessible location – the easiest way I could think to create a publicly accessible database connection is to setup a simple OCI API Gateway.  I used the following documentation to create that endpoint to host the following JSON data.

Setup an API Gateway with a Stock Response

As you can see above – after creating a Public API Gateway instance, I created a specific deployment that was of a Stock Response.  In addtion, I also added a header name to identify the Content-Type as application/json.  Once I save the deployment – it will give me a specific fully-qualified public URL that I can use for the next step.

Browser Check

Before you move on to some testing, you may want to verify that you have a browser or preferred browser configuration in the SQLDeveloper Application.

If you open Preferences/Settings – you can add any browsers that may not have been detected by SQLDeveloper and also specify which browser you would like to use as your default browser for Entra ID authentication.  Note: This may be different default browser that you specify for the rest of your desktop

Setup the SQLDeveloper Connection

If you have made it this far – then we are on the home stretch.  Next, we will create a new connection in SQLDeveloper. You will notice that the connection parameters look slightly different than before – as we will not be referencing a token file located on the file system.  In addition – you can see that the JDBC connection string has a new context – config-https.  This, config-https, instructs the JDBC driver to use the JDBC config provider to build the rest of the database connection details via a HTTP-based service.  Here, I have specified the API Gateway endpoint which I created in the previous section.

Test the Connection and Authentication

Upon selecting either the Test or Connect button – this should trigger a browser to open on your desktop.  Then, if you have properly configured the Entra ID enterprise application for the Database authentication – you will be sent to a login page.

Upon successful authentication and authorization to the Azure Entra ID Enterprise Application – you should be redirected to a page that looks similar to below.  This is informing you that you have completed the authentication flow – and an OAuth2 token has been returned to the awaiting SQLDeveloper application. 

If everything worked properly, then you then will be presented a blank SQL Worksheet that you can start to execute statements.  Here I am showing that my user is set to the Shared Schema User “BURTMACK”, and that the enterprise authentication that is tied to this session is the user principal that I used to authenticate to Azure Entra ID.

Additional Tips

This use case employs an Autonomous Database – Serverless that has been configured to allow the use of a walletless one-way TLS connection to the database.  If you are enforcing the use of mTLS – that will require the an additional configuration parameter to specify the location of the local Oracle Wallet that contains the keys to complete the mTLS connection  

This use case leverages the config-http service provider extension which was designed to highlight the enhancements for 23ai JDBC libraries.  With that said – you can still use the local TNS files (tnsnames.ora) and jdbc properties when specifying your database configuration with the 23ai client libraries.

Conclusion

Oracle Cloud Infrastructure has been engineering leading solutions in the multicloud era. Since support for the Oracle Database and Microsoft Entra ID was released 2022, we have received tremendous adoption and feedback around this capability. This blog demonstrates just one small example of the dedication for making multicloud a natural fit for our customers. On a larger scale, Oracle has recently furthered the commitment to mulitcloud across the OCI portfolio – with the announcements of Oracle Database@Azure, Oracle Database@GCP and Oracle Database@AWS.

As I mentioned – this is just one example of how to leverage the multicloud integrations and technology partnerships.  Stay tuned for more content around database authentication:

SQLDeveloper Extension for Microsoft VSCode

OCI IAM Interactive Login with SQL Developer 24 and SQLDeveloper for VSCode

Lastly, do not miss the Database Security Office Hours featured monthly with product updates, best practices and new features