PART 3: Implementing Oracle Database Active Directory Password Synchronization using Oracle CMU

Posted in: Technical Track

This is the third in a four-part article series related to testing Oracle Database 18c Centrally Managed Users (CMU) by leveraging the Oracle Cloud Infrastructure (OCI) for Oracle DBAs to create a lab or testbed. The second article covered Configuring Windows And Linux Servers For CMU Testing.

Implementing Oracle Centrally Managed Users (CMU) via “password synchronization” with Active Directory (AD) is the most practical configuration for many use cases as the authentication process from the perspective of the application remains unchanged. Applications still prompt for credentials – the key difference is that the database communicates with Active Directory for password validation.

Consequently, the database must be configured so that it can securely communicate with AD and that database users and groups are created accordingly. This article walks through how to configure and test at both the AD and database level.

Active Directory One-time Configuration Steps

The first requirement is to have an Active Directory user that the database software will use to communicate with AD. When the database receives an authorization request (i.e. a “database logon”) it needs to talk to AD to validate the user-provided credentials. Hence, a user is required for Oracle Database software to AD interaction.

As usual, this user can be added via the “Active Directory Users and Computers” GUI utility. However, for simplicity, a PowerShell equivalent command can be used.

On the Domain Controller DC1, open a PowerShell window as Administrator. Then a new user, in this case called “orasync” can be created. (IMPORTANT: update the UserPrincipalName and Path to show your actual domain vs the STAGECOACH.NET domain being used in this example):

New-ADUser `
   -Name = "orasync" `
   -UserPrincipalName = "orasync@stagecoach.net" `
   -DisplayName = "Oracle Service Directory User" `
   -Description = "Service account for Oracle Database authentication." `
   -Path = "CN=Managed Service Accounts,DC=stagecoach,DC=net" `
   -ChangePasswordAtLogon = $false `
   -PasswordNeverExpires = $true `
   -CannotChangePassword = $true `
   -Enabled = $true `
   -AccountPassword(Read-Host -AsSecureString "Initial Password:")

Putting the service account user in the “Managed Service Accounts” folder in Active Directory is a preference to reduce clutter. It’s in no way mandatory.

We can do a simple confirmation that the user was created using the PowerShell command dsquery:

dsquery user -name orasync

Sample output:

PS C:\Users\Administrator> dsquery user -name orasync
"CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET"
PS C:\Users\Administrator>

A more detailed output can be provided using the PowerShell command Get-ADUser:

Get-ADUser -Identity "orasync" -properties DistinguishedName

Sample output:

PS C:\Users\Administrator> Get-ADUser -Identity "orasync" -properties DistinguishedName


DistinguishedName : CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET
Enabled           : True
GivenName         :
Name              : orasync
ObjectClass       : user
ObjectGUID        : 0335e42e-f597-4bd8-a6dd-5f41e75177bf
SamAccountName    : orasync
SID               : S-1-5-21-4278349699-2454951225-2679278977-1108
Surname           :
UserPrincipalName : orasync@stagecoach.net


PS C:\Users\Administrator>

These same results can also be confirmed using the GUI utility “Active Directory Users and Computers”:

The permissions that this user requires on the Active Directory side are not overly clear from the official documentation. However, the following simple dsacls commands seem to provide what is required (again, change the domain name as required):

dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" /I:P /G "STAGECOACH\orasync:WP;lockoutTime"
dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" /I:P /G "STAGECOACH\orasync:RP"

The dsacls commands produce a lot of output which can be ignored.

In the previous article, an SSH public-private keypair for the Domain Controller DC1 was created. Since we will need to copy files to and from the database server, the domain controller’s public key should be copied to the database server.

On the Active Directory Domain Controller DC1, first display and copy the public key:

Copy the public SSH key from the Windows host to the database server DBSERV1 into the ~/.ssh/authorized_keys file for the “opc” user. Be careful not to include carriage returns or end of line markers when copying.

Example:

Even though the database software is installed on Linux, the Oracle Home contains a Windows executable file for the “Oracle Password Filter”:

On the Active Directory domain controller DC1, the Oracle Password Filter executable can be copied from the database server DBSERV1 using scp run from the PowerShell window:

scp opc@10.0.1.101:/opt/oracle/product/18c/dbhomeXE/bin/opwdintg.exe .\Desktop\

Sample output:

PS C:\Users\Administrator> scp opc@10.0.1.101:/opt/oracle/product/18c/dbhomeXE/bin/opwdintg.exe .\Desktop\
The authenticity of host '10.0.1.101 (10.0.1.101)' can't be established.
ECDSA key fingerprint is SHA256:xv4oVqv05BeQAPbc3fiVDl5K7P+MGtyeDY9Wo0T9cfc.
Are you sure you want to continue connecting (yes/no/[fingerprint])?
Warning: Permanently added '10.0.1.101' (ECDSA) to the list of known hosts.
opwdintg.exe                                                                          100%  183KB 131.6KB/s   00:01
PS C:\Users\Administrator>
PS C:\Users\Administrator> dir .\Desktop\opwdintg.exe


    Directory: C:\Users\Administrator\Desktop


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/10/2019   5:11 PM         187392 opwdintg.exe


PS C:\Users\Administrator>

After copying, run the executable:

.\Desktop\opwdintg.exe

The utility runs in a Command Shell window automatically:

And ends with a prompt to reboot the domain controller:

After the Domain Controller DC1 instance reboots, log in again and once again open a PowerShell window.

The installation added three new security groups to Active Directory which can be viewed from both the command line and the GUI “Active Directory Users and Computers”.

The three groups are related to the different password verifiers that may be required and can be confirmed from the PowerShell command line using:

Get-ADGroup -Filter {name -like "ORA*"} | Select Name,ObjectClass,GroupCategory

Sample output:

PS C:\Users\Administrator> Get-ADGroup -Filter {name -like "ORA*"} | Select Name,ObjectClass,GroupCategory

Name        ObjectClass GroupCategory
----        ----------- -------------
ORA_VFR_11G group            Security
ORA_VFR_12C group            Security
ORA_VFR_MD5 group            Security


PS C:\Users\Administrator>

Or using the “Active Directory Users and Computers” utility:

To make communications secure, the domain controller’s certificate must be exported and provided to the database server. This can be done relatively easily using some basic PowerShell commands:

# Extract details of the Server's self-issued certificate:
$Cert = Get-ChildItem Cert:\LocalMachine\My | `
   Where-Object {$_.subject -match [Environment]::GetEnvironmentVariable("computername")+"."+[Environment]::GetEnvironmentVariable("userdnsdomain")}

# Export the certificate to a .cer file
Export-Certificate -Cert $Cert -FilePath .\$Env:computername.cer -Type CERT -Force

Sample output:

PS C:\Users\Administrator> $Cert = Get-ChildItem Cert:\LocalMachine\My | `
>>    Where-Object {$_.subject -match [Environment]::GetEnvironmentVariable("computername")+"."+[Environment]::GetEnvironmentVariable("userdnsdomain")}
PS C:\Users\Administrator>

PS C:\Users\Administrator> Export-Certificate -Cert $Cert -FilePath .\$Env:computername.cer -Type CERT -Force


    Directory: C:\Users\Administrator


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/10/2019   6:16 PM           1525 DC1.cer


PS C:\Users\Administrator>

Or the same can be done using the Windows Certification Authority tool (maybe use this if there are any complexities or issues with the above PowerShell commands):

Lastly, copy the exported certificate file to the database server to later be imported. For example, again using SCP run from a Powershell window on the DC1 server:

scp .\$Env:computername.cer opc@10.0.1.101:/tmp/

Database Home One-time Configuration Steps

The database home must be configured so that it can communicate with the Active Directory Domain Controller. The location of the domain controller(s) is specified in the dsi.ora file. And the associated DSI_DIRECTORY_SERVERS parameter can reference the domain controller(s) via either internal IP address or FQDN.

Configure the dsi.ora file in the default location, running as the “oracle” user on the database server DBSERV1:

. oraenv <<< XE

cat <<EOT > ${ORACLE_HOME}/ldap/admin/dsi.ora
DSI_DIRECTORY_SERVERS = (dc1.ad1.cmuvnc.oraclevcn.com:389:636)
DSI_DEFAULT_ADMIN_CONTEXT = "DC=stagecoach,DC=net"
DSI_DIRECTORY_SERVER_TYPE = AD
EOT

cat ${ORACLE_HOME}/ldap/admin/dsi.ora

Sample output:

[oracle@dbserv1 ~]$ . oraenv <<< XE
ORACLE_SID = [XE] ? The Oracle base remains unchanged with value /opt/oracle
[oracle@dbserv1 ~]$

[oracle@dbserv1 ~]$ cat <<EOT > ${ORACLE_HOME}/ldap/admin/dsi.ora
> DSI_DIRECTORY_SERVERS = (dc1.ad1.cmuvnc.oraclevcn.com:389:636)
> DSI_DEFAULT_ADMIN_CONTEXT = "DC=stagecoach,DC=net"
> DSI_DIRECTORY_SERVER_TYPE = AD
> EOT

[oracle@dbserv1 ~]$
[oracle@dbserv1 ~]$ cat ${ORACLE_HOME}/ldap/admin/dsi.ora
DSI_DIRECTORY_SERVERS = (dc1.ad1.cmuvnc.oraclevcn.com:389:636)
DSI_DEFAULT_ADMIN_CONTEXT = "DC=stagecoach,DC=net"
DSI_DIRECTORY_SERVER_TYPE = AD

[oracle@dbserv1 ~]$

An Oracle credential wallet file must be created to securely hold the Active Directory credential (created above) and the server’s certificate.

On the database server, DBSERV1 as the “oracle” user, create a new wallet that will contain the credentials for the orasync user and the certificate needed for secure communication with the domain controller (run each command individually as the orapki and mkstore commands require a wallet password):

. oraenv <<< XE

mkdir -p ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
cd ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet

orapki wallet create -wallet . -auto_login

mkstore -wrl . -createEntry ORACLE.SECURITY.USERNAME orasync
mkstore -wrl . -createEntry ORACLE.SECURITY.DN "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET"

# Note: when prompted, your "secret/password" is the one for the Active Directory orasync user previously created
mkstore -wrl . -createEntry ORACLE.SECURITY.PASSWORD

Import the certificate transferred from the domain controller into the same Oracle wallet using:

orapki wallet add -wallet . -cert /tmp/*.cer -trusted_cert

Sample output:

[oracle@dbserv1 wallet]$ orapki wallet add -wallet . -cert /tmp/*.cer -trusted_cert
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Cannot modify auto-login (sso) wallet
Enter wallet password:
Operation is successfully completed.
[oracle@dbserv1 wallet]$

And validate that everything is in the wallet as expected:

orapki wallet display -wallet .

IMPORTANT: There is a trailing period in the above command indicating that the wallet files are in the current working directory.

Sample output showing that there is a DN, PASSWORD, USERNAME, and trusted certificate saved in the wallet:

[oracle@dbserv1 wallet]$ orapki wallet display -wallet .
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DN
ORACLE.SECURITY.PASSWORD
ORACLE.SECURITY.USERNAME
Trusted Certificates:
Subject:        CN=DC1.STAGECOACH.NET
[oracle@dbserv1 wallet]$

The database needs to know where to find this wallet file which we specify in the SQLNET.ORA file:

cat <<EOT >> ${ORACLE_HOME}/network/admin/sqlnet.ora
WALLET_LOCATION=
  (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=
      (DIRECTORY=${ORACLE_BASE}/admin/${ORACLE_SID}/wallet)
    )
 )
SQLNET.WALLET_OVERRIDE=TRUE
EOT

And we also need to, at the very minimum, adjust the LDAP_DIRECTORY_ACCESS initialization parameter (we would also need to adjust other parameters as administrative privilege [aka SYSDBA] access via directory synchronization is required):

echo "ALTER SYSTEM SET ldap_directory_access='PASSWORD' SCOPE=both;" | sqlplus -s / as sysdba

Sample output:

[oracle@dbserv1 wallet ~]$ echo "ALTER SYSTEM SET ldap_directory_access='PASSWORD' SCOPE=both;" | sqlplus -s / as sysdba

System altered.

[oracle@dbserv1 wallet ~]$

A common source of problems with CMU directory synchronization comes down to firewall issues. Even though a hostname or IP was specified in the dsi.ora file, communication may be redirected to a different socket and consequently we should ensure that the LDAP ports are accessible regardless of whether IP addresses or hostnames or FQDNs are used.

Still on the database server DBSERV1, test all permutations for accessing the CMU testbed domain controller:

(echo > /dev/tcp/10.0.1.100/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
(echo > /dev/tcp/DC1/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
(echo > /dev/tcp/DC1.STAGECOACH.net/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
(echo > /dev/tcp/dc1.ad1.cmuvnc.oraclevcn.com/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"

If any of the above tests do not resolve and return the “OPEN” message, then there’s a firewall or routing problem. In lieu of configuring a separate DNS environment, for testing purposes, it’s easiest to simply update /etc/hosts file as the “root” user:

echo "10.0.1.100 DC1.STAGECOACH.net DC1" >> /etc/hosts

Then re-test access to the LDAP ports. Sample output:

[root@dbserv1 ~]# (echo > /dev/tcp/10.0.1.100/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
OPEN
[root@dbserv1 ~]# (echo > /dev/tcp/DC1/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
OPEN
[root@dbserv1 ~]# (echo > /dev/tcp/DC1.STAGECOACH.net/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
OPEN
[root@dbserv1 ~]# (echo > /dev/tcp/dc1.ad1.cmuvnc.oraclevcn.com/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
OPEN
[root@dbserv1 ~]#

At this point, the one-time setup in the RDBMS home is complete.

Creating a Database User and Testing from the Database Server

We’re now ready to configure the database and test authentication through CMU. To minimize involved components, it’s easiest to first test from the database server.

As a prerequisite, an Active Directory test user must exist. In this article we’re using a sample user called “Simon”. If needed, create a test user using either the New-ADUser PowerShell command as shown previously or through the “Active Directory Users and Computers” GUI tool:

What’s not shown through the GUI is the user’s “Distinguished Name” (DN) which will be required when adding to the Oracle database.

It’s easy to extract the AD DN of the test user from a PowerShell window on the DC1 server:

Get-ADUser -Identity "simon" -properties DistinguishedName

Sample output:

PS C:\Users\Administrator> Get-ADUser -Identity "simon" -properties DistinguishedName


DistinguishedName : CN=Simon Pane,CN=Users,DC=STAGECOACH,DC=NET
Enabled           : True
GivenName         : Simon
Name              : Simon Pane
ObjectClass       : user
ObjectGUID        : 7d925663-3d81-46be-be78-95618550f2dc
SamAccountName    : simon
SID               : S-1-5-21-4278349699-2454951225-2679278977-1113
Surname           : Pane
UserPrincipalName : simon@STAGECOACH.NET


PS C:\Users\Administrator>

The top line of output is what will be relevant when adding the user to the Oracle database.

But before the database can authenticate against Active Directory, the Oracle password filter (installed into the domain controller) must catch, hash, and store a shadow copy of the user’s password. Consequently:

  1. The user must be added to one of the newly created Active Directory security groups such as ORA_VFR_12C.
  2. The user’s password must be changed.

Until both steps are done, the database user (once created) will fail to authenticate.

The user can be added the group through the “Active Directory Users and Computers” utility if using a GUI is preferable: