Entra ID Integration: How To Rotate Service Principal Credential

Oracle database Entra ID integration enables centralization of authentication, authorization and user life cycle management of Oracle database users and service accounts.  Service accounts use the service principal authentication flow, where a client secret is added to the Entra ID web application for the database client service account. This secret is loaded into an Oracle 23ai (or newer) wallet on the host where the client application will use the wallet to authenticate to the target database with Entra ID integration. 

The secret loaded into the Entra ID web application expires after a specified amount of time where the default is 1 year. Therefore, it will be important from an operationalization perspective to setup a system to regularly update the secret in both the Entra ID web application and the Oracle wallet.  This is typically initially setup interactively through the Entra ID web console and the orapki tool on the Oracle database.  However, most database customers would prefer to script this operational task so via the command line.

Fortunately, the combination of the Microsoft Azure CLI and Oracle 23ai client together can realize this objective in just 2 steps.

1. First, you request a credential secret request and capture the resulting secret returned.

az ad app credential reset --id <appId> --display-name app_secret --years <n>

2. Second, you update the wallet where the client app resides

orapki secretstore modify_entry -wallet <dir> -pwd <pw> -alias oracle.security.azure.credential.<appId> -secret <secret>

Let's consider a working example.

First, you download and install the Microsoft Azure CLI.

For example on Oracle/RedHat/CentOS 9 Linux:

sudo rpm --import https://packages.microsoft.com/keys/microsoft.asc
sudo dnf install -y https://packages.microsoft.com/config/rhel/9.0/packages-microsoft-prod.rpm
sudo dnf install azure-cli

Next, you download and extract Oracle full or instant client because it includes orapki command for wallet management.  Login and download 12ai full client (V1044258-01.zip) from https://edelivery.oracle.com.

unzip -qo /u01/bits/V1044258-01.zip -d /u01/23ai_fullclient

Here is the script that I threw together to demonstrate an example of how you could automate updating the service account secret in Azure and the local wallet.

Important notes:

1. This script is not supported by Oracle or me. It is only provided as an example.

2. You must be logged into Azure CLI tool.  This script builds that into the flow if you are not already logged in.

3. If you copy the wallet to a remote host, you will need to re-enable auto_logon of the wallet on the new host.

Here's the sample script:

#!/bin/bash
wDir="$1"
appId="$2"
expiration="$3"
now=$(date +'%Y%m%d%H%M%S')

# Show usage if appId is not provided
if [ -z "${appId}" ]
then
   echo "Usage: $0 <db_client_app_id>"
   exit 1
fi

testLogin=$(az account show 2> /dev/null)
if [ -z "${testLogin}" ]
then
   echo "Login to Auzre for az CLI tool:"
   az login --allow-no-subscriptions --only-show-errors
fi

# Set default expiration at 10 years
if [ -z "${expiration}" ];then expiration=10;fi

# Securely read in the Oracle wallet password
echo -e "Enter wallet password: \c"
while IFS= read -r -s -n1 char
do
  [[ -z $char ]] && { printf '\n'; break; }
  if [[ $char == $'\x7f' ]]
  then
      [[ -n $wpw ]] && wpw=${wpw%?}
      printf '\b \b'
  else
    wpw+=$char
    printf '*'
  fi
done

# Path set from blog post demo
PATH=$PATH:/u01/23ai_fullclient/bin

# Reset secret and capture new password
secret=$(az ad app credential reset --only-show-errors --id ${appId} --display-name app_secret --years ${expiration} 2>> $0-${now}.log |jq -r '.password')
if [ -z "${secret}" ]
then
   echo "ERROR: Failed to create secret. See: $0-${now}.log"
   exit 1
fi

# Create wallet if one does not exist
if [ -e "${wDir}/ewallet.p12" ]
then
   walletOp='modify_entry'
else
   walletOp='create_entry'
   echo "Create wallet ${wDir}"
   orapki wallet create -wallet "${wDir}" -pwd "${wpw}"  -auto_login >> $0-${now}.log 2>&1
fi

# Load the wallet with  the secret
echo "Load secret into wallet"
orapki secretstore ${walletOp} -wallet "${wDir}" -pwd "${wpw}" -alias oracle.security.azure.credential.${appId} -secret "${secret}" >> $0-${now}.log 2>&1
rc=$?

if [ "${rc}" -ne 0 ]
then
   echo "ERROR: Failed to load secret into wallet. See: $0-${now}.log"
   exit 1
fi

echo "Secret loaded into wallet ${wDir}"

Here is a sample invocation:

$ /u01/rotate_secret.sh /u01/swallet 186f231c-830e-4513-9b64-34f341848050
Login to Auzre for az CLI tool:
To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code FTB7E3DR5 to authenticate.

Retrieving tenants and subscriptions for the selection...

[Tenant and subscription selection]

No     Subscription name          Subscription ID                       Tenant
-----  -------------------------  ------------------------------------  ------------------------------------
[1] *  N/A(tenant level account)  7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1  7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1

The default is marked with an *; the default tenant is '7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1' and subscription is 'N/A(tenant level account)' (7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1).

Select a subscription and tenant (Type a number or Enter for no changes): 1

Tenant: 7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1
Subscription: N/A(tenant level account) (7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)

[Announcements]
With the new Azure CLI login experience, you can select the subscription you want to use more easily. Learn more about it and its configuration at https://go.microsoft.com/fwlink/?linkid=2271236

If you encounter any problem, please open an issue at https://aka.ms/azclibug

Enter wallet password: *********
Load secret into wallet
Secret loaded into wallet /u01/swallet