Entra ID Client Credential Authentication Flow For Oracle Database Service Accounts

Entra ID is one of the new cloud native Oracle database authentication, authorization and user life cycle management architectures introduced in 2022.  In addition to authentication, authorization, and user life cycle management, this architecture also adds multi-factor authentication and unified password policy complexity with on premises Active Directory (AD) because most customers sync their users and groups with their Entra ID tenancy.

The main three Oracle Database authentication flows for Oracle database Entra ID integration include:

• Interactive authentication flow for the humans connecting from devices that support pop-up browser
• Device code authentication flow for humans connecting through jump hosts that do not support pop-up browser
• Client credential authentication flow for service accounts

This blog post focuses on the client credential authentication flow to provide a simplified recipe for setting up Oracle database client service accounts with Entra ID integration.

The configuration encompasses configuration in each of the following three areas:

• Entra ID
• New app role in the target database
• Service account app registration
• Service account pre-shared credential
• Add service account to target database allow app ingress rules
• Database Server
• Add service account
• Apply grants to service account
• Database Client
• Create wallet with pre-shared credential
• Add TNS entry for service account with AZURE_CREDENTIALS

In this example, lets assume that we are adding a service account for the Human Resources (HR) application.  The Entra ID App Role will be named app_hr.role.  The HR service account configuration in Entra ID will be named dbclient_service_hr.  The Oracle database server representation of this service account will be named app_hr.

Entra ID Configuration

1. Add app_hr.role “App role” to target database (hrdb) and confirm ownership

1.1 Login to Entra at https://entra.microsoft.com 

1.2 Navigate to Applications —> App registrations —> target database (hrdb) —> App roles

1.3 Click on “Create app role” and fill out form as follows followed by clicking “Apply”

Display name: app_hr.role

Allowed member types: Applications

Value: app_hr.role

Description: HR Application Role

Do you want to enable this app role? Yes (checked)

1.4 Click on “Owners” and confirm that your authenticated user is in the list of owners for this database (hrdb) web app registration

1.4.1 If your user is not listed, click on “Add owners”, search on your name, click checkbox next to yourself, and click “Select”

2. Register the database client service account as Entra ID web application

2.1 Register Oracle database client service account in Entra ID app registration

2.1.1 Login to Entra at https://entra.microsoft.com 

2.1.2 Navigate to Applications —> App registrations —> New registration

2.1.3 Enter the following and then click “Register”

Name: dbclient_service_hr

Supported account types: (Default) Single tenant

Redirect URI:   Leave blank as this is not used by client credential flow

2.2 Capture the app ID of the dbclient_service_hr app registration by clicking on “Overview” and copy the app id:

Application (client) ID: 186f231c-830e-4513-9b64-34f341848050

2.3 Add app ID to database API permissions

2.3.1 Navigate to Applications —> App registrations —> target database (hrdb) —> Expose an API

2.3.2 Click on “Add a client application”, paste the app ID from step 2.2, check all  “Authorized scopes” listed, and click “Add application”

2.4 Create client secret

2.4.1 Navigate to Applications —> App registrations —> dbclient_service_hr

2.4.2 Click on “Add a certificate or secret” under Client credentials

2.4.3 Click on “New client secret” and fill out form as follows followed by clicking “Add”

Description: hrapp_secret

Expires: Select value that aligns with your corporate standard. In this example, I selected 24 months.

2.4.4 Capture the secret for future use by clicking on “Copy to clipboard” of the hrapp_secrret value

hrapp_secret value: tb58Q~eDEfE.2ambJAg4qrTzDExq2Lqcw3cjebfy

2.5 Add App role 

2.5.1 Click on “App roles”

2.5.2 Click on “Create app role” and fill out form as follows followed by clicking “Apply”

Display name: app_hr.role

Allowed member types: Applications

Value: app_hr.role

Description: HR Application Role

Do you want to enable this app role? Yes (checked) 

2.6 Add application permission and grant admin consent for the domain

2.6.1. Click on API permissions

2.6.2 Click on “Add a permission”

2.6.3 Click on “My APIs” tab and click on target database (hrdb) to which this service account will be connecting

2.6.4 Click on “Application Permission”

2.6.5 Check the box of the app_hr.role “App role” created in step 1.3

2.6.6 Click on “Add permissions”

2.6.7 Click on “Grant admin consent for <domain>” and click on “Yes”

Oracle Database Server Configuration

3. Create or alter service account to database server (hrdb)

sqlplus system/Oracle123@pdb1
SQL> CREATE USER app_hr IDENTIFIED GLOBALLY AS 'AZURE_ROLE=app_hr.role';

or

sqlplus system/Oracle123@pdb1
SQL> ALTER USER app_hr IDENTIFIED GLOBALLY AS 'AZURE_ROLE=app_hr.role';

4. Grant requisite privileges to app.  For example, if we only want to grant read-only session privilege:

SQL> GRANT CREATE SESSION TO app_hr;

Oracle Database Client Configuration

5. Create wallet and add client secret referenced (step 2.4.4) for app ID (Step 2.2) 

C:
mkdir \u01\app\dbclient_service_hr
cd \u01\app\dbclient_service_hr
orapki wallet create -wallet . -pwd Oracle123 -auto_login
orapki secretstore create_entry -wallet . -pwd Oracle123 -alias oracle.security.azure.credential.<hrapp_id> -secret "<hrapp_secret_value>"

Confirm the that the secret was added to the wallet with:

cd \u01\app\dbclient_service_hr
orapki wallet display -wallet . -complete

Confirm the secret value was properly set with:

orapki secretstore view_entry -wallet . -pwd Oracle123 -alias oracle.security.azure.credential.<hrapp_id>

6. Add TNS record for service account using the service principal flow in tnsnames.ora that specifies the path to the wallet created in step 3 for the value of AZURE_CREDENTIALS.

HRDB_PDB1_APP_HR = 
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=
      (SSL_SERVER_DN_MATCH=TRUE)
      (SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")
      (WALLET_LOCATION=SYSTEM)
      (TOKEN_AUTH=AZURE_SERVICE_PRINCIPAL)
      (AZURE_CREDENTIALS=C:\u01\app\dbclient_service_hr)
      (TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)
      (CLIENT_ID=186f231c-830e-4513-9b64-34f341848050)
      (AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

Test Configuration

7. Confirm that the configuration is working properly by connecting with sqlplus using the HRDB_PDB1_SERVICE

C:\u01\app\fullclient_23_5\bin\sqlplus /@hrdb_pdb1_app_hr

Troubleshooting

See the ODB Entra ID Integration Troubleshooting Guide.

I hope you found this post helpful and informative.

Blessings!

Brad