Configuring SQLDeveloper to Centrally Authenticate Oracle Database Users with Azure Active Directory

May 26, 2023 | 5 minute read
Ty Stahl
Cloud Security Architect
Text Size 100%:

In recent months, I have had several colleagues and customers ask a variety of questions around the last post that I wrote on Azure AD Authentication for Applications with the Autonomous Database.  At the time - only the Autonomous Database Shared(ADB-S) supported the capability of authenticating users with AzureAD tokens. This initially prevented the flood gates from bursting given that the feature was only for a confined set of customers using ADB-S and had used AzureAD as their primary identity provider. 

With that said, the most prevalent question I would get from customers was the most obvious one - "When will the other database platforms support this feature?"  Gone are those days - with Alan's post earlier this month announcing support of AzureAD Tokens for Oracle Databases (19.16 and higher) for Autonomous, On-Premises, and Cloud Databases.  

For context, I will briefly touch on why this capability across all the various flavors of the Oracle Database is important. When managing a fleet of hundreds or thousands of databases - it is a security best practice to centralize your DBA identity and access controls within a mature Identity Governance framework.  Further, you want the authentication experience to be both seamless and consistent for your end users.  This prevents users from having a unique set of credentials for the hundreds databases, which leads to the infamous posted note on the back of a keyboard. If you are like most, you have employed one of the enterprise solutions that Oracle supports for this -  Enterprise User Security or Centrally Managed Users with Active Directory.  Generally, these solutions depend on on-premise systems which run in your data center or live on compute instances running within the cloud. Looking forwards to a cloud-identity ecosystem - it is now possible to migrate your databases to leverage AzureAD or OCI IAM as the authoritative source for users and roles within your database fleet - and this blog is going to walk through how to achieve this using SQLDeveloper 23c.

For this demonstration - I am using the Autonomous Database; this allows us to piggyback off of the configurations we already have from the previous blog post.

SqlDevAzureArch

I chose to use the latest and greatest SQLDeveloper 23c - which was released recently.  This ensures that we have the necessary JDBC thin drivers that can recognize the Azure OAUTH2 tokens, along with all of the other new features.

Once installed - we need to gather the parameters that are needed - mainly, the JDBC connection string.  For simplicity - I chose to use the ADB Wallet-less connection string for the connection type, which does not require me to download and use the wallet to establish a mutually authenticated session.  Let's grab that from the OCI console - here is a link to that blog.

We can now begin to setup our SQLDeveloper Connection.  First, select the Custom JDBC as the connection type, then we will take the connection from the ADB Connection console, and prefix it with - jdbc:oracle:thin:@

Also, ensure that OS Login is the selected as the Authentication Type.

ADBSqlConn

Lastly - under Advanced Settings - specify the following Database properties to instruct the JDBC driver where to find the Database Token.

AdvancedProps

Now, we need to get a AzureAD token to test the SQLDeveloper configurations.  I wanted to emulate a realistic and secure journey for a Database Admin who is about to operate on a Database system from either their secure thick-clients/laptops or from a Virtual Desktop Infrastructure (VDI) which could be a cluster of OCI compute instances.  For that, I am going to use the Azure CLI that will initiate the OAuth2 Authorization Code Flow with Azure AD.  One major benefit of using the Auth Code flow is that we can ensure to enforce multi-factor authentication or MFA on the user prior to receiving a token to connect to the database - this is a security best practice when dealing with any privileged application or data system.

Once you have the Azure CLI tools installed on your workstation, you can initiate the authentication executing from the command line.

 az login

This should trigger the default web browser to open; and you will be sent through your organizations authentication journey. (Let's hope it includes a MFA challenge!). Once you have successfully completed, you will get a message - You have logged into Microsoft Azure!

At this point, return to your command line window and execute the following command which will request a specific access token for the database and write it to the file location that you specified in SQLDeveloper advanced property.  The <Azure_App_Id> value will come from your Azure AD Console, it is the variable that was used when configuring the ADB instance to accept AzureAD tokens.

az account get-access-token --resource https://<Tenancy_Name>.onmicrosoft.com/<Azure_App_Id> --query accessToken --output tsv > /tmp/aad-tokens/token

GetAccessToken

Now that everything is in place - we can now test the connection in SQLDeveloper - let's first verify if successful.

Test ADB Connection

Then, we can run some simple SQL statements to check out our environment and user session.

userEnvQuery

Conclusion:

From an operational perspective, SQLDeveloper continues to be a powerful tool that will be utilized for years to come by developers and DBA's. That said - I am always a proponent of cloud native tooling, it is worth mentioning that SQLDeveloper also exists in OCI as a web-based service - more commonly referred to as Database Actions. 

From a security standpoint, centralized authentication and access management through a robust identity governance platform is the most efficient and secure way of controlling access to database systems.  Leveraging AzureAD to retrieve OAuth2 tokens means that an organization can leverage already existing conditional access controls and multi-factor authentication policies to ensure their user's are authentic and authorized appropriately.

Lastly - it is important to highlight that this solution is not just for those who have Azure AD - and it is not the only way to achieve cloud-based identity for your Oracle databases. In Alan's blog referred to before, he does highlight that OCI IAM is a complete, viable identity solution that can be used as an Identity Provider for the Oracle Database.

 

 

Ty Stahl

Cloud Security Architect


Previous Post

Cipher Suites, TLS Handshake in OCI Load Balancer

Amit Chakraborty | 7 min read

Next Post


Extending Oracle Fusion SaaS with Natural Language Processing (NLP) capabilities

Rekha Mathew | 7 min read