Oracle Database 18c Password Authentication with Microsoft Active Directory

Oracle database 18c introduces a new feature 'Integration of Active Directory Services with Oracle Database'. It gives us a way to authenticate and authorize users directly with Microsoft Active Directory. Oracle database users and roles can map directly to Active Directory users and groups without using Oracle Enterprise User Security (EUS) or another intermediate directory service. Here, I am going to demonstrate how to make it work step by step.
 
Environment:

  Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
  Microsoft Active Directory Server - Windows Server 2012 R2

Step 1.  Create an Oracle Service Directory User Account in Microsoft Active Directory

Log into Microsoft Active Directory (AD) domain controller as administrator. Run command dsadd to create user oracle as Oracle Service Directory User Account in Microsoft Active Directory,

C:>dsadd user "cn=oracle,cn=Users,dc=lab,dc=dbaplus,dc=ca" -pwd oracle_4u
dsadd succeeded:cn=oracle,cn=Users,dc=lab,dc=dbaplus,dc=ca

Here, "cn=oracle,cn=Users,dc=lab,dc=dbaplus,dc=ca" is the Distinguished Name (DN) of the user oracle in Active Directory and oracle_4u is password of the user. If you are not sure what the DN should be in your Active Directory, just run following command to get the DN of administrator from your Active Directory,

C:>dsquery user -name administrator
"CN=Administrator,CN=Users,DC=lab,DC=dbaplus,DC=ca"

The returned string of command dsquery is the DN of user administrator, you can use the string as DN of new user oracle by replacing Administrator with oracle.

Step 2. Grant the Oracle service directory user account (oracle) required permissions accessing Active Directory user/group objects

The Oracle service directory user account created in step 1 (oracle) is used by Oracle database to access Active Directory and verify user login, which needs following Active Directory permissions granted to oracle,

   * Read properties (of the Active Directory user)
   * Write Lockout Time (property of the Active Directory user)

Active Directory user accounts and user groups are created under Active Directory object "Users" which DN is part of DN of Active Directory users ("CN=Users,DC=lab,DC=dbaplus,DC=ca" in my Active Directory).

C:> dsacls "CN=Users,DC=lab,DC=dbaplus,DC=ca" /I:S /G "oracle:RP;;user" "oracle:WP;LockoutTime;user"

Check if the permissions are granted,

C:> dsacls "CN=Users,DC=lab,DC=dbaplus,DC=ca"

The command output looks like,

Owner: LAB\Domain Admins
Group: LAB\Domain Admins
Access list:
Allow LAB\Domain Admins               SPECIAL ACCESS
<<Contents truncated>>
Permissions inherited to subobjects are:
Inherited to all subobjects
Allow LAB\Enterprise Admins           FULL CONTROL   <Inherited from parent>
<<Contents truncated>>
Inherited to user
<<Contents truncated>>
Allow LAB\oracle                      SPECIAL ACCESS
                                      READ PROPERTY
Allow LAB\oracle                      SPECIAL ACCESS for lockoutTime
                                      WRITE PROPERTY
<<Contents truncated>>
          
The command completed successfully

Step 3. Install the Password Filter and Extend the Microsoft Active Directory Schema

You can use the Oracle opwdintg.exe executable on the Active Directory server to install the password filter and extend the Active Directory schema. The executable is shipped with Oracle database software, and can be found from $ORACLE_HOME/bin directory on database server. It is not necessary to install Oracle software on Active Directory server.

1. Find out the executable on Oracle database server (e.g.  /u01/app/oracle/product/18.0.0/dbhome_1/bin/opwdintg.exe)

2. Using a secure method of copying (such as pscp.exe which can be downloaded with putty.exe from https://www.putty.org/), copy opwdintg.exe to a temporary directory (e.g. C:\temp) on Windows Active Directory Server.

C:\temp>pscp oracle@host01.lab.dbaplus.ca:/u01/app/oracle/product/18.0.0/dbhome_1/bin/opwdintg.exe ./
oracle@host01.lab.dbaplus.ca's password:
opwdintg.exe              | 183 kB | 183.0 kB/s | ETA: 00:00:00 | 100%

3. Ensure that the Windows OS language setting is English because opwdintg.exe requires English for the Windows OS, and also make sure you are logging into Windows server as Active Directory administrator.

4. Run the opwdintg.exe utility and answer the prompts as following,

   * Do you want to extend AD schema? [Yes/No]: Enter Yes.
   * Schema extension for this domain will be permanent. Continue? [Yes/No]:Enter Yes.
    Note:
        You can only extend the Active Directory schema from a Windows domain controller. If you try to extend the schema again, error messages appear, but you can ignore these errors.
        This step creates the following three verifier groups. If these groups already exist, then errors will appear, but you can ignore these errors.
            ORA_VFR_MD5 is required when the Oracle Database WebDAV client is used.
            ORA_VFR_11G enables the use of the Oracle Database 11G password verifier.
            ORA_VFR_12C enables the user of the Oracle Database 12C password verifier.
        Unless you have backed up the Active Directory schema, once extended, the Active Directory schema extension cannot be reverted.
   * Do you want to install Oracle password filter? [Yes/No]: Enter Yes.
   * The change requires machine reboot. Do you want to reboot now? [Yes/No]: Enter Yes.

5. Verify if opwdinth.exe utility created the verifier groups,

    C:\>dsquery group -name ora*
    "CN=ORA_VFR_11G,CN=Users,DC=lab,DC=dbaplus,DC=ca"
    "CN=ORA_VFR_12C,CN=Users,DC=lab,DC=dbaplus,DC=ca"
    "CN=ORA_VFR_MD5,CN=Users,DC=lab,DC=dbaplus,DC=ca"  

6. Verify if opwdinth.exe utility added an orclCommonAttribute attribute to the Active Directory schema for user accounts

    C:\>powershell
    Windows PowerShell
    Copyright (C) 2013 Microsoft Corporation. All rights reserved.
   
    PS C:\> get-ADUser oracle -Properties orclCommonAttribute
   
   
    DistinguishedName   : CN=oracle,CN=Users,DC=lab,DC=dbaplus,DC=ca
    Enabled             : True
    GivenName           : oracle
    Name                : oracle
    ObjectClass         : user
    ObjectGUID          : 070bb632-9ed2-455f-b410-7a70c7f9ad0d
    orclCommonAttribute :
    SamAccountName      : oracle
    SID                 : S-1-5-21-1071794079-706920406-590034058-1113
    Surname             :
    UserPrincipalName   : oracle@lab.dbaplus.ca  

Step 4. Create the dsi.ora file on database server

Create text file dsi.ora under directory  $ORACLE_HOME/network/admin, which includes following lines,

DSI_DIRECTORY_SERVERS = (DC01.lab.dbaplus.ca:389:636)
DSI_DEFAULT_ADMIN_CONTEXT = "dc=lab,dc=dbaplus,dc=ca"
DSI_DIRECTORY_SERVER_TYPE = AD

Here DC01.lab.dbaplus.ca is host name of Active Directory server (domain controller), and "dc=lab,dc=dbaplus,dc=ca" is for my domain lab.dbaplus.ca.

Step 5. Request an Active Directory Certificate for a Secure Connection

Run certutil utility on Active Directory Server to export the certificate,

C:\temp>certutil -ca.cert cacert.bin
CA cert[0]: 3 -- Valid
CA cert[0]:
-----BEGIN CERTIFICATE-----
<<Contents truncated>>
-----END CERTIFICATE-----
CertUtil: -ca.cert command completed successfully.
C:\temp>dir
 Volume in drive C has no label.
 Volume Serial Number is 0E1D-3E08
 Directory of C:\temp
2018-09-12  04:38 PM    <DIR>          .
2018-09-12  04:38 PM    <DIR>          ..
2018-09-12  04:38 PM               911 cacert.bin
2018-09-12  03:38 PM           187,392 opwdintg.exe
2018-09-11  03:50 PM           626,744 PSCP.EXE
               3 File(s)        815,047 bytes
               2 Dir(s)  44,127,707,136 bytes free

Copy certificate to database server,

C:\temp>pscp cacert.bin oracle@host01.lab.dbaplus.ca:/tmp
oracle@host01.lab.dbaplus.ca's password:
cacert.bin                | 0 kB |   0.9 kB/s | ETA: 00:00:00 | 100%

Step 6. Create the Wallet on database server for a Secure Connection

Log into database server as Oracle software owner (e.g. oracle), do not log as root.

1. Create wallet directory $ORACLE_BASE/admin/<DB_UNIQUE_NAME>/wallet,

mkdir -p /u01/app/oracle/admin/orcl/wallet

2. Create a new wallet

$ORACLE_HOME/bin/orapki wallet create -wallet /u01/app/oracle/admin/orcl/wallet -auto_login
Enter password: oracle_4u

3. Create the Oracle directory service account name for performing searches in Active Directory

$ORACLE_HOME/bin/mkstore -wrl /u01/app/oracle/admin/orcl/wallet -createEntry ORACLE.SECURITY.USERNAME oracle

Here, oracle is Active Directory user created in step 1.

4. Specify the DN of the Oracle directory service account name

$ORACLE_HOME/bin/mkstore -wrl /u01/app/oracle/admin/orcl/wallet -createEntry ORACLE.SECURITY.DN cn=oracle,cn=users,dc=lab,dc=dbaplus,dc=ca

Here, "cn=oracle,cn=users,dc=lab,dc=dbaplus,dc=ca" is the DN of Active Directory user oracle created in step 1.

5. Create the user password credential for the Oracle directory service account name

$ORACLE_HOME/bin/mkstore -wrl /u01/app/oracle/admin/orcl/wallet -createEntry ORACLE.SECURITY.PASSWORD oracle_4u

Here, oracle_4u is the password of Active Directory user oracle created in step 1.

6. Add the certificate of Active Directory server to the wallet

$ORACLE_HOME/bin/orapki wallet add -wallet /u01/app/oracle/admin/orcl/wallet -cert /tmp/cacert.bin -trusted_cert

Here, /tmp/cacert.bin file is created in step 5.

7. Verify the credentials

$ORACLE_HOME/bin/orapki wallet display -wallet /u01/app/oracle/admin/orcl/wallet

The output should be similar to the following:

Requested Certificates:
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DN
ORACLE.SECURITY.PASSWORD
ORACLE.SECURITY.USERNAME
Trusted Certificates:
Subject:        CN=lab-DC01-CA,DC=lab,DC=dbaplus,DC=ca

Step 7. Configure the Microsoft Active Directory Connection

1. Set LDAP_DIRECTORY_ACCESS parameter to PASSWORD,

ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = 'PASSWORD' SCOPE=SPFILE;

2. Set the LDAP_DIRECTORY_SYSAUTH parameter to YES if you want that users authenticated through Microsoft Active Directory can be granted privileges: SYSDBA,SYSOPER, SYSDG,SYSKM and SYSRAC. Otherwise, only locally authenticated users (not users authenticated through Microsoft Active Directory) can log in with these privileges.

ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES SCOPE=SPFILE;

3. Restart database

Step 8. Create database users password-authenticated with Microsoft Active Directory

1. Create Active Directory user dbuser01 on Active Directory server

c:\> dsadd user "cn=dbuser01,cn=Users,dc=lab,dc=dbaplus,dc=ca" -pwd oracle_4u -memberof "cn=ORA_VFR_12C,cn=Users,dc=lab,dc=dbaplus,dc=ca"

2. Trigger Password Filter installed in step 3 to generate Oracle compatible/specific password by running dsmod on Active Directory Server,

C:\>dsmod user "cn=dbuser01,cn=Users,dc=lab,dc=dbaplus,dc=ca" -pwd oracle_4u

It will populate the property orclCommonAttribute of user dbuser01 in Active Directory. Failed to run this command may cause following error while connecting to database,

ORA-28276: Invalid ORACLE password attribute.

3. Create database user dbuser01 (user name must be same as just created Active Directory user)

SQL> create user dbuser01 identified globally as 'cn=dbuser01,cn=users,dc=lab,dc=dbaplus,dc=ca';
SQL> grant create session to dbuser01;

Technically, following user-creating SQL without as clause also works (user name must be same as just created Active Directory user)

SQL> create user dbuser01 identified globally;

However, it is not first choice because many users complained that they got error ORA-01017 while creating user without as clause.

Step 9. Test the integration with new created user

[oracle@host01]$ sqlplus /nolog
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Oct 11 16:33:46 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
SQL> connect dbuser01/oracle_4u@orcl
Connected.
SQL>

Troubleshooting

1. No permission to read user entry in LDAP directory service

SQL> conn dbuser01@orcl
Enter password:
ERROR:
ORA-28300: No permission to read user entry in LDAP directory service.

The Oracle service directory user account is not granted proper Active Directory accessing permissions. Following Step 2 to grant permissions.

2. Invalid username/password when user name and password are correct

SQL> conn dbuser01@orcl
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Invalid DSI/wallet configuration.

3. Invalid ORACLE password attribute

SQL> conn dbuser01@orcl
Enter password:
ERROR:
ORA-28276: Invalid ORACLE password attribute.

If Oracle password filter is not installed, please install it following step 3. Then check whether the user is member of group ORA_VFR_12C by running dsget on Active Directory server as following,

C:\>dsget user "cn=dbuser01,cn=Users,dc=lab,dc=dbaplus,dc=ca" -memberof
"CN=ORA_VFR_12C,CN=Users,DC=lab,DC=dbaplus,DC=ca"
"CN=Domain Users,CN=Users,DC=lab,DC=dbaplus,DC=ca"

The result shows that user dbuser01 is member of both ORA_VFR_12C and Domain Users.

If the user is not member of group ORA_VFR_12C, add user to the group. Then check if the password is generated for Oracle which is saved as value of property "orclCommonAttribute" by running ldapsearch on database server,

[oracle@host01]$ ldapsearch -h DC01.lab.dbaplus.ca -p 389 -D "cn=oracle,cn=users,dc=lab,dc=dbaplus,dc=ca" -w oracle_4u -U 2 -W "file:/u01/app/oracle/admin/orcl/wallet/" -P oracle_4u -b "dc=lab,dc=dbaplus,dc=ca" -s sub "(sAMAccountName=dbuser01)" dn orclCommonAttribute
CN=dbuser01,CN=Users,DC=lab,DC=dbaplus,DC=ca
orclCommonAttribute=

or by running powershell command get_ADUser on Active Directory Server,

C:\>powershell get-ADUser dbuser01 -Properties orclCommonAttribute

DistinguishedName   : CN=dbuser01,CN=Users,DC=lab,DC=dbaplus,DC=ca
Enabled             : True
GivenName           :
Name                : dbuser01
ObjectClass         : user
ObjectGUID          : 839c60a8-eb11-4328-989a-95ac3c2b40f1
orclCommonAttribute :
SamAccountName      : dbuser01
SID                 : S-1-5-21-1071794079-706920406-590034058-1124
Surname             :
UserPrincipalName   :

Both commands show that the Oracle password is not generated because value of property orclCommonAttribute is empty. We need to reset user's password on Active Directory to set value of orclCommonAttribute by running dsmod on Active Directory Server,

C:\>dsmod user "cn=dbuser01,cn=Users,dc=lab,dc=dbaplus,dc=ca" -pwd oracle_4u
dsmod succeeded:cn=dbuser01,cn=Users,dc=lab,dc=dbaplus,dc=ca

C:\>powershell get-ADUser dbuser01 -Properties orclCommonAttribute

DistinguishedName   : CN=dbuser01,CN=Users,DC=lab,DC=dbaplus,DC=ca
Enabled             : True
GivenName           :
Name                : dbuser01
ObjectClass         : user
ObjectGUID          : 839c60a8-eb11-4328-989a-95ac3c2b40f1
orclCommonAttribute : {MR-SHA512}iVTWcjbe4B1OATDp3Ee0sraOrgCEYe9h5ghzhnwtnj/JVynUQB/Gr1yEPhvNmWW+jFlUH7AzdxsJU9q4B4tAub5lOh92uLoydRJhdhw8zLQ=
SamAccountName      : dbuser01
SID                 : S-1-5-21-1071794079-706920406-590034058-1124
Surname             :
UserPrincipalName   :

The property orclCommonAttribute is populated.

4. The system cannot find the file specified while running certutil

C:\temp>certutil -ca.cert cacert.bin
CertUtil: The system cannot find the file specified.

Certification Authority is not installed on the server. Install the Certification Authority(https://docs.microsoft.com/en-us/windows-server/networking/core-network-guide/cncg/server-certs/install-the-certification-authority)