How to enable Oracle Database Azure AD authentication

As of Oracle database release update 19.18 a new authentication method was made available utilizing Azure AD (Entry ID as its named by Microsoft recently) Oauth tokens. Using this method has the advantage of centralized authentication and authorization management and improving password management at the database. This article will describe how to get started with Oauth authentication for Oracle databases.

Introduction

Azure AD authentication for authenticating users and/or services to an Oracle Database can be used in cases when strong user authentication is required. By offloading the two factor authentication or other authentication methods to Azure AD the need for user passwords stored in the database is no longer there. Azure AD can both be used for real (interactive) user accounts and voor “service” accounts.

Oauth(2) token based authentication means that when a user/service/principal requests a token for something (eg. a database), it will be authenticated and if authorized receives a token signed by AD. The fact that the token is signed means no further checking is required, the contents of the token is trusted. It contains the resource it was meant for and additional information for that resource to map it to a user/principal within the application (or in this case, map it to a database user).

As of Oracle database release update 19.18 Azure AD authentication was added, to use it, both database and client software needs to be newer than this release. This functionality is not and will not be available in version 21c. Client software not fully prepared for token based authentication can still use it by loading the token from file.

To start using Azure AD authentication we need to make sure the database has a TCPS listener configured. As the Oauth token is transmitted over the network and this token grants the bearer access it is vital that this communication is not intercepted. The client software will not permit transmission of this token over an unsecured connection. Configuring a TCPS listener and handling all the wallet configuration will not be fully described here. If there is a self-signed certificate in use, make sure this certificate is provided to the client software in a wallet so the client can verify the server’s certificate. Additionally the database needs to fetch/verify the public certificate(s) for Azure AD, some internet access is required.

Azure AD configuration

The first step to configure Azure AD authentication we need to create an Application Registration in Azure AD. To do this, log on to the Azure Portal and go to Azure AD (or Entra ID)¹. Go to App registrations to create a new registration:

Follow the steps in the Oracle security guide making sure the API endpoint is a https:// url. The following screenshots demonstrate the end result of this configuration. Note there are two roles defined here, one for an application and the other one for users.

The users are granted access through a Enterprise App, there is the location an AD user or Group may be granted a role in the App registration.

When this configuration is done, a client App registration is also required, however this registration does not need a lot of configuration. It only needs to exist, and it’s uuid will need to be added to the list of permitted application id’s in the API page of the database server App registration.

This is all that’s required to make interactive users able to generate a token. A user would need the following information to be able to generate a token with a small program, or with the Azure CLI command:

• The Azure AD tenant ID
• The application uri
• The client ID
• username/password + 2FA (or a valid browser session)

For each separate application (service) which will authenticate using a client secret a separate App registration in Azure AD is required. This is just a App registration like the “normal” client App registration however, this one contains a client secret for authentication. The App registration is attached to a role within the Database App registration.

Go to API permissions to add the Database App registration’s API:

When this App registration authenticates, the role field in the token will contain the role AZURE_APP. Admin consent is in most cases required here, make sure the Admin consent is granted before trying.

Fetching a token

Before moving to the database a bit about getting the token from Azure AD. There are several methods of getting a token, it can be done using a library provided by Microsoft for several programming languages (MSAL, available for Python, NodeJS and others), the Azure CLI tool and finally it can be done using a web service call using curl. In either case a token is produced that can be saved in a file to be presented to the Oracle database. For this blog only a Python script will be demonstrated, please see the Microsoft documentation for more examples. For the next two examples a Python installation is required and the MSAL library is already installed in this Python environment.

Interactive user authentication

In this case a real user authenticates against AD and will be prompted for AD credentials and if necessary 2FA challenge. This works fine when the Python program runs on the user’s desktop. For authentication the user’s default browser will open to verify the AD session. When verified a token file is created.

from msal import PublicClientApplication
import sys

client_id='b6688016-e35a-4a2a-b1f0-466a7c83f84e' 
tenant_id='<<your tenant id>>'

scopes = [ 'https://amisconclusion.onmicrosoft.com/33843cb1-ec25-4e70-abc4-60340df6c652/connect' ]

app = PublicClientApplication(
  client_id = client_id,
  authority = "https://login.microsoftonline.com/" + tenant_id
)

acquire_tokens_result = app.acquire_token_interactive(
  scopes = scopes
)

if 'error' in acquire_tokens_result:
  print("Error: " + acquire_tokens_result['error'])
  print("Description: " + acquire_tokens_result['error_description'])
else:
  stdout_bu = sys.stdout
  with open('tokenfile', 'w') as tokenf:
      sys.stdout=tokenf
      print(acquire_tokens_result['access_token'])
  sys.stdout=stdout_bu

note that the relevant ID’s are all hardcoded in this example. The scope contains the full uri to the API endpoint defined in the App registration for the database. On successful authentication a token file is created.

Service (client secret) authentication

When a service connects to the database it can’t logon to Azure AD using username, password and a 2FA method. With a certificate or a client secret a service can authenticate itself to Azure AD. The client secret is generated by Azure AD and can only be viewed once. when doing this manual, make sure to immediately copy the secret to a safe place, like a (key)vault. In the below sample code it’s hardcoded. Do not do this in production.

import sys
import msal

my_client_id    = "4b7059d9-08cf-4237-916a-a18e2af5c464"
my_authority = "https://login.microsoftonline.com/<<the_tenant_id>>"
my_secret    = "THE_SECRET_FETCHED_FROM_THE_APP_REG"
my_scope     = [ "https://amisconclusion.onmicrosoft.com/33843cb1-ec25-4e70-abc4-60340df6c652/.default" ]

app = msal.ConfidentialClientApplication(
    my_client_id, 
    authority=my_authority,
    client_credential=my_secret
    )

result = None

result = app.acquire_token_silent(my_scope, account=None)

if not result:
    print("No suitable token exists in cache. Let's get a new one from AAD.")
    result = app.acquire_token_for_client(scopes=scope)

if "access_token" in result:
    stdout_bu=sys.stdout
    with open('tokenfile', 'w') as tokenf:
      sys.stdout=tokenf
      print(result.get("access_token"))
      sys.stdout=stdout_bu
else:
    print(result.get("error"))
    print(result.get("error_description"))
    print(result.get("correlation_id"))  

Inspecting your token

now that we have a fresh token we can attempt to view it. For the casual observer it just looks like a base64 encoded blob, but it can be decoded by a tool like jwt.io. To make sure your token works for the database there are 2 items to look at:

In above output, the “aud” field contains the App registrations uri, this will be the sign for the database that this token is indeed meant for the database. The roles field contains the role defined. This is matched in the database to map it to a database user.

A similar output is seen for a token generated by a real AD user, but this token has also some additional information available, like the user’s name and logon name.

Database configuration

To enable Azure AD token authentication in the database two parameters need to be set. These are PDB level parameters. In case of an Autonomous database, DBMS_CLOUD_ADMIN needs to be used as setting parameters is not directly permitted.

alter system set identity_provider_config = '{
  "application_id_uri": "https://amisconclusion.onmicrosoft.com/33843cb1-ec25-4e70-abc4-60340df6c652",
  "tenant_id": "<<tenant id>>",
  "app_id": "33843cb1-ec25-4e70-abc4-60340df6c652"}' scope=both;

alter system set identity_provider_type='AZURE_AD' scope=both;

-- same example but now for an Autonomous database
begin
  dbms_cloud_admin.enable_external_authentication(
    type   => 'AZURE_AD',
    params => json_object(
      'application_id_uri' value 'https://<<api_url>>/<<app_id>>",
      'tenant_id'          value '<<tenant id>>',
      'app_id'             value '<<app_id>>'
    ),
    force  => true
  );
end;
/

Next step is to create a user which has (global) external authentication enabled:

create user my_az_app_user identified globally as 'az_role=AZURE_APP';

With the above statement a user is created which will be identified by the Azure role “AZURE_APP”, providing a token with this role in the roles list will make the database connect the session as this user.

Similarly a direct mapping to a user instead of a role can be made as well:

create user martijn identified globally as 'azure_user=martijn.pronk@amisconclusion.onmicrosoft.com';

Connecting to a database with a token

This is the moment where everything comes together, after requesting a token we need to supply it to the application and connect to the database. In the next part a session with sqlplus will be setup using the token authentication. The first step is to make sure the environment is setup well, my advise is to setup a separate TNS_ADMIN location where a wallet is stored (in case of self-signed server certificates or a mTLS connection), a sqlnet.ora and a tnsnames.ora is provided. ² Lets start with the sqlnet.ora:

Can you act as an IoT data expert ChatGPT

Next we take a look at tnsnames.ora:

pdb23_az =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = ol8-vagrant)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb23)
    )
    (SECURITY=(SSL_SERVER_DN_MATCH=TRUE)
    (TOKEN_AUTH=OAUTH)
    (TOKEN_LOCATION="/vagrant/az_clientsecret/tokenfile"))
  )

It is important to note some items in above TNS entry:

• the protocol is TCPS;
• security settings define some additional requirements, the hostname of the server needs to match with the name in the provided certificate. This setting is required, if this setting is not there, an ORA-25704 error will be thrown;
• Token authentication is chosen and location (in a file) is provided;
• With the provided sqlnet.ora a wallet with the certificate for the host (ol8-vagrant) was available.

Using the /@ syntax of the sqlplus command line the token authentication tns entry is used. To check the result, the authentication_method variable of the userenv context contains the value TOKEN_GLOBAL.

Conclusion

To conclude this post, using Azure AD to authenticate your database users makes managing database password no longer required, authentication is taken care of by Azure AD. Also managing database users (onboarding and offboarding) can now be managed without interference from a DBA. This functionality is still in its early stages but we can expect more user friendly solutions when Oracle improves the client side of things.

1. Please note that for most Azure AD actions administrative privileges may be required. Contact your local Azure AD administrator. ↩︎
2. if a well known (public) CA certificate is used, all relevant information can also be placed in the tnsnames.ora file, or even on the command line in the connect string. ↩︎