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

16 min read
Feb 19, 2020

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 = "" `
-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 :

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.



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@ .\Desktop\

Sample output:

PS C:\Users\Administrator> scp opc@ .\Desktop\
The authenticity of host ' (' 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 '' (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:


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@

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

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_DEFAULT_ADMIN_CONTEXT = "DC=stagecoach,DC=net"

[oracle@dbserv1 ~]$
[oracle@dbserv1 ~]$ cat ${ORACLE_HOME}/ldap/admin/dsi.ora

[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 - Production
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 - Production
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Oracle Secret Store entries:
Trusted Certificates:
[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

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/ >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
(echo > /dev/tcp/DC1/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
(echo > /dev/tcp/ >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
(echo > /dev/tcp/ >/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 " DC1" >> /etc/hosts

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

[root@dbserv1 ~]# (echo > /dev/tcp/ >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
[root@dbserv1 ~]# (echo > /dev/tcp/DC1/636) >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
[root@dbserv1 ~]# (echo > /dev/tcp/ >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
[root@dbserv1 ~]# (echo > /dev/tcp/ >/dev/null 2>&1 && echo "OPEN" || echo "CLOSED"
[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:AD4

Though for Oracle DBAs, it's probably simpler and more practical (plus more scalable) to add the user to the security group using a very simple PowerShell command:

Add-ADGroupMember -Identity "ORA_VFR_12C" -Members simon

And to verify that the user has been added:

Get-ADPrincipalGroupMembership "simon" | select name

Sample output:

PS C:\Users\Administrator> Get-ADPrincipalGroupMembership "simon" | select name

Domain Users

PS C:\Users\Administrator>

After adding the user to the verifier security group, the user's password must be changed. After changing, a new hash value will be recorded in the orclCommmonAttribute field in Active Directory which can easily be validated with:

Get-ADUser -Identity "simon" -properties orclCommonAttribute

Sample output:

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

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
orclCommonAttribute : {MR-SHA512}jJIvfDRufD2s4huee1ipYBpQhF2+2msmRe11Myfh2jHNBisDRHjSZDh3n531YRYy3wCGTipT8YqwuvHAExLjf93TUHD4lTuUDpMBqgrMorw=
SamAccountName : simon
SID : S-1-5-21-4278349699-2454951225-2679278977-1113
Surname : Pane
UserPrincipalName : simon@STAGECOACH.NET

PS C:\Users\Administrator>

If the orclCommonAttribute is blank or isn't shown, then the setup isn't complete. Ensure that the user is in the ORA_VFR_12C security group and then reset the user's password.

If you prefer to check the user through a GUI, then the "Active Directory Explorer" utility from the Microsoft SysInternals site can be downloaded from: This utility (which is just a simple, stand-alone executable not requiring an installer or reboot) shows properties like the DistinguishedName and orclCommonAttribute fields which the standard "Active Directory Users and Computers" utility does not.

To programmatically download "Active Directory Explorer" (which is optional - only do so if desired):

wget -OutFile .\
expand-archive -path '.\' -destinationpath 'C:\Users\Administrator\Desktop'

# Then run:


Once you know the Active Directory distinguished name and are sure that the password filter has recorded a password hash in the orclCommonAttribute field, the user can be created in the database and tested.

Going back to the Database Server DBSERV1, adding to the database is simple. It just requires use of the IDENTIFIED GLOBALLY option:

CREATE USER simon IDENTIFIED GLOBALLY AS 'CN=Simon Pane,CN=Users,DC=stagecoach,DC=net';
GRANT create session TO simon;
GRANT SELECT ON v_$database TO simon;

For testing convenience, rather than using an EZconnect string, we can create an Oracle Net entry the service:

cat << EOT >> ${ORACLE_HOME}/network/admin/tnsnames.ora
cat ${ORACLE_HOME}/network/admin/tnsnames.ora

And finally, we can test the connection with:

sqlplus "STAGECOACH\simon"@ORCL

Or using an alternative format:

sqlplus ""@ORCL

Or even simply with:

sqlplus simon@ORCL

Sample output:

[oracle@dbserv1 ~]$ sqlplus "STAGECOACH\simon"@ORCL

SQL*Plus: Release - Production on Mon Nov 25 21:54:21 2019

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Enter password:
Last Successful login time: Mon Nov 25 2019 21:48:31 +00:00

Connected to:
Oracle Database 18c Express Edition Release - Production

SQL> show user

SQL> exit
Disconnected from Oracle Database 18c Express Edition Release - Production
[oracle@dbserv1 ~]$

Hence, things are working well from the database server. The next logical thing is to test from the other Linux and Windows secondary servers.

Testing from the Application Servers

Testing from either the Linux or Windows application server is simple. No extra client (or app server) setup is required as the clients simply communicate with the database server as normal using the normal protocols. It's the database server DBSERV1 that is configured to communicate with Active Directory. The database does all of the authentication and/or authorization work, not the client. So there's really nothing to set up on the client.

For simplicity we'll use an Oracle Net EZconnect connection string with the FQDN of the DB Server. We can even do this from the "opc" user for simplicity.

(NOTE: There is additional client setup/configuration required and the OS login used when connecting does matter when CMU is implemented using Kerberos, but not for password based authentication.)

Test a simple connection using a known credential. For example:

sqlplus system@//

For a more elaborate test, we can use SQLPlus and enhance the command to:

  1. Leverage SYS_CONTEXT and the USERENV namespace to obtain additional connection properties.
  2. Connect using our Active Directory username and password.

Example Bash shell command:

echo "
set heading off
select 'DB_NAME (from v\$database) : '||name,
from v\$database;
" | sqlplus -s -L simon/Oracle123@//

Sample output:

[opc@applinux1 ~]$ echo "
> set heading off
> select 'DB_NAME (from v\$database) : '||name,
> 'SESSION_USER : '||sys_context('USERENV','SESSION_USER'),
> from v\$database;
> " | sqlplus -s -L simon/Oracle123@//

DB_NAME (from v$database) : XE

[opc@applinux1 ~]$

And that's it - note the values shown above. Proof that we connected from a simulated Linux application server to the database but using a global password authenticated against Active Directory.

Testing using PowerShell from the Windows test server APPWIN1 also works. Only real change is the character used to negate special characters in the shell (i.e. negating the "$" from being a special character):

Hence, the Windows-formatted equivalent command becomes:

echo "
set heading off
select 'DB_NAME (from v`$database) : '||name,
from v`$database;
" | sqlplus -s -L simon/Oracle123@//

Sample output:


The conclusion that nothing special was required on the application server side - we didn't even need to configure the SQLNET.ORA or TNSNAMES.ORA. Of course, doing so might be desirable for other purposes.

Next Steps

Now that CMU is set up and working using password-based authorization and directory synchronization to Active Directory, more of the CMU features can be experimented with. For example:

  • Authentication via Active Directory security groups and Oracle database "shared schemas".
  • Authorization via database "global roles" mapping to AD security groups.

And of course, independently setting up and testing CMU with Kerberos authentication as detailed in the fourth and final article in this series.

If you’ve enjoyed this deep dive, you may be interested in learning about the benefits of CMU to discover whether this simplified user management is right for you and your business

Get Email Notifications

No Comments Yet

Let us know what you think