PART 4: Implementing Oracle Database Single Sign-on Using Kerberos, Active Directory, and Oracle CMU

Posted in: Technical Track

This is the fourth and final article in a four-part 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 third article covered Implementing Oracle Database Active Directory Password Synchronization Using Oracle CMU.

The second common configuration option that Oracle customers are often interested in is a true single sign-on option for Oracle Database-based applications.

For clarity, single sign-on really means that a new credential prompt is not required to sign into an application. Simply put, a user might provide a credential to log into their desktop (i.e. a Windows Active Directory username and password). That sign-on process results in a token being issued and cached by the OS, and then that token is used to authenticate access for other resources such as network drives or applications.

External reference: https://en.wikipedia.org/wiki/Single_sign-on

For Oracle Database, Kerberos is a common authentication token-based system that can be leveraged with Centrally Managed Users (CMU) to provide a single sign-on experience.

There are a few minor catches however:

  1. The logon process must generate and cache a Kerberos ticket-granting ticket (TGT) that the Oracle software stack can use for authentication requests.
  2. The application must support and be able to pass empty string credentials. If the application throws an error if a NULL username or password is provided, then this will present a problem. For testing, we’ll use SQLPlus so this won’t be an issue for this proof of concept.

Active Directory One-time Configuration Steps

In this test environment, Active Directory is the Kerberos Authentication server. And generally, Active Directory and most Windows and Linux servers (including the Oracle Linux 7.7 images used in this testbed) should have Kerberos Version 5 already installed – if not, it needs to be added.

In Kerberos, users are known as or referred to as “principals”. The “service principal” is named in the format:

kservice/kinstance@REALM

For Oracle and CMU it is typically:

oracle/<DB Server FQDN>@<domain>

And therefore, for this example, the actual principal name will be:

oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET

For the CMU setup, we could make service principals in Active Directory for each Oracle database server (typically) or one common one if preferred.

To add the necessary principal (aka “user”) to Active Directory we could use the “Active Directory Users and Computers” GUI or, once again, just use a simple PowerShell command run from the Domain Controller DC1 such as:

New-ADUser `
   -Name "dbserv1" `
   -UserPrincipalName "dbserv1.ad1.cmuvnc.oraclevcn.com@stagecoach.net" `
   -DisplayName "dbserv1.ad1.cmuvnc.oraclevcn.com" `
   -Description "ServiceOracle Kerberos Service Principal." `
   -Path "CN=Managed Service Accounts,DC=stagecoach,DC=net" `
   -ChangePasswordAtLogon $false `
   -PasswordNeverExpires $true `
   -CannotChangePassword $true `
   -Enabled $true `
   -AccountPassword(Read-Host -AsSecureString "Initial Password:")

Again, putting the service account user in the “Managed Service Accounts” folder in Active Directory is a preference to reduce clutter – it is in no way mandatory.

And again we can do a simple PowerShell query to confirm that the user was created successfully:

dsquery user -name dbserv1

Sample output:

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

Or via “Active Directory Users and Computers” if preferable:

Before we’re done with the prerequisite AD setup, we need to extract and copy the “service key table” for the Kerberos Principal we just added.

Still running commands from the Windows Domain Controller DC1, extract the service key table for Kerberos using the Windows ktpass utility. For example:

ktpass.exe -princ oracle/<DB Server FQDN>@<domain> `
   -mapuser <DB Server FQDN>@<domain> `
   -crypto all `
   -pass * `
   -out .\$Env:COMPUTERNAME.keytab

The “*” is used to prompt for the Service Principal’s password instead of hard coding it in the command.

Sample output:

PS C:\Users\Administrator> ktpass.exe -princ oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET `
>>    -mapuser dbserv1.ad1.cmuvnc.oraclevcn.com `
>>    -crypto all `
>>    -pass * `
>>    -out .\$Env:COMPUTERNAME.keytab
Targeting domain controller: DC1.STAGECOACH.NET
Successfully mapped oracle/dbserv1.ad1.cmuvnc.oraclevcn.com to dbserv1.
Type the password for oracle/dbserv1.ad1.cmuvnc.oraclevcn.com:
Type the password again to confirm:
Password successfully set!
WARNING: pType and account type do not match. This might cause problems.
Key created.
Key created.
Key created.
Key created.
Key created.
Output keytab to .\DC1.keytab:
Keytab version: 0x502
keysize 81 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x1 (DES-CBC-CRC) keylength 8 (0x7f0ecde9d02397a2)
keysize 81 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x3 (DES-CBC-MD5) keylength 8 (0x7f0ecde9d02397a2)
keysize 89 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x17 (RC4-HMAC)
keylength 16 (0x8b2318524d2e3e2e31885afc21024cf5)
keysize 105 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x12 (AES256-SHA1) keylength 32 (0x536fa1677dd224d7510d81b86d74a602ee7fc25902a7969ed3a98ac546a88cb8)
keysize 89 oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET ptype 0 (KRB5_NT_UNKNOWN) vno 3 etype 0x11 (AES128-SHA1) keylength 16 (0x7d8600b56ca085d86e933888aea389ae)
PS C:\Users\Administrator>

Lastly, copy the exported keytable file to the Database Server (DBSERV1). For example, using scp from the Active Directory domain controller:

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

Sample output:

PS C:\Users\Administrator> scp .\$Env:computername.keytab opc@10.0.1.101:/tmp/
DC1.keytab                                                                            100%  467     0.5KB/s   00:00
PS C:\Users\Administrator>

That should conclude the required prerequisite setup on the Active Directory domain controller. Unlike with password-authenticated CMU implementations, this time we did not need to extend the Active Directory schema, install any additional software, or reboot our domain controllers.

Database Home One-time Configuration Steps

Like with password-based authentication and directory synchronization, some one-time setup in the database and the database home is required (as the “oracle” OS user).

First, we need to ensure that a few relevant database parameters are not set:

. oraenv <<< XE

echo "
show parameter os_authent_prefix
show parameter remote_os_authent
" | sqlplus -s / as sysdba

Likely, at least OS_AUTHENT_PREFIX is set to a default value of “ops$”, and, hence, needs to be adjusted. Since it is not a dynamic parameter, a database restart must be included for parameter changes to come into effect:

. oraenv <<< XE

echo "
alter system set os_authent_prefix='' scope=spfile;
alter system reset remote_os_authent;
shutdown immediate
startup
" | sqlplus -s / as sysdba

Next, the SQLNET.ORA file on the database server must be adjusted to provide the relevant Kerberos settings including where to access the copied key table file.

The SQLNET.AUTHENTICATION_KERBEROS5_SERVICE parameter refers to the ‘oracle/‘ part added to the server principal name when ktpass.exe was run.

The SQLNET.KERBEROS5_CONF points to a location for a configuration file while SQLNET.KERBEROS5_KEYTAB is the key table file copied from the domain controller.

Hence, the required SQLNET.ORA file updates becomes:

. oraenv <<< XE

mkdir -p ${ORACLE_HOME}/network/admin/kerberos
cp /tmp/*.keytab ${ORACLE_HOME}/network/admin/kerberos/keytab
oklist -k -t -old ${ORACLE_HOME}/network/admin/kerberos/keytab

echo "
SQLNET.KERBEROS5_KEYTAB=${ORACLE_HOME}/network/admin/kerberos/keytab
SQLNET.KERBEROS5_CONF=${ORACLE_HOME}/network/admin/kerberos/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
" >> ${ORACLE_HOME}/network/admin/sqlnet.ora

Other optional and related parameters (including a backup authentication method) can be included if required – refer to the Oracle CMU documentation.

The last part of the setup is to configure the aforementioned configuration file (still using the sample domain “STAGECOACH.NET” – update as required):

cat <<EOT >> ${ORACLE_HOME}/network/admin/kerberos/krb5.conf
[libdefaults]
default_realm = STAGECOACH.NET

[realms]
STAGECOACH.NET = {
  kdc = DC1.STAGECOACH.NET:88
}

[domain_realm]
.`dnsdomainname` = STAGECOACH.NET
`dnsdomainname` = STAGECOACH.NET
EOT

cat ${ORACLE_HOME}/network/admin/kerberos/krb5.conf

And when testing from the local database server, we need to ensure that SQLNET.WALLET_OVERRIDE=TRUE is not set or is commented out:

sed -i.bak '/^SQLNET.WALLET_OVERRIDE/ s/^SQLNET.WALLET_OVERRIDE/\#SQLNET.WALLET_OVERRIDE/' ${ORACLE_HOME}/network/admin/sqlnet.ora

grep SQLNET.WALLET_OVERRIDE ${ORACLE_HOME}/network/admin/sqlnet.ora

Creating Database Users to use Kerberos Authentication

Again, using the “Simon” test Active Directory user created previously, we can create an IDENTIFIED EXTERNALLY database user. For example:

echo "
alter session set container=XEPDB1;
create user \"SIMON@STAGECOACH.NET\" identified externally;
grant create session to \"SIMON@STAGECOACH.NET\";
grant select on v_\$database to \"SIMON@STAGECOACH.NET\";
" | sqlplus -s / as sysdba

To perform an initial test, from the database server DBSERV1, we need to manually obtain the TGT since we did log into the server with an Active Directory session to automatically obtain the TGT. To obtain tickets manually, we can use the okinit and oklist utilities (Oracle-specific versions of standard Kerberos utilities kinit and klist) which are provided in the Oracle Home:

okinit <Active Directory User>
oklist

Sample output:

[oracle@dbserv1 ~]$ okinit simon

Kerberos Utilities for Linux: Version 18.0.0.0.0 - Production on 26-NOV-2019 18:52:34

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

Configuration file : /opt/oracle/product/18c/dbhomeXE/network/admin/kerberos/krb5.conf.
Password for simon@STAGECOACH.NET:
[oracle@dbserv1 ~]$ oklist

Kerberos Utilities for Linux: Version 18.0.0.0.0 - Production on 26-NOV-2019 18:52:45

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

Configuration file : /opt/oracle/product/18c/dbhomeXE/network/admin/kerberos/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: simon@STAGECOACH.NET

Valid starting     Expires            Service principal
11/26/19 18:52:39  11/27/19 04:52:39  krbtgt/STAGECOACH.NET@STAGECOACH.NET
        renew until 11/27/19 18:52:34
[oracle@dbserv1 ~]$

By running those commands manually, we obtained the Kerberos TGT for the “simon@strategicdbs.com” user without logging into the OS as that user.

Of course, this is a temporary measure since we are, at this point, testing from the database server while logged in using a different OS user than the one we want to use to connect to the database. When we test from the Windows and Linux application servers, the experience should be seamless, meaning not needing to manually run these commands to obtain the required ticket.

If the okinit command fails, it may be because of a firewall or DNS resolution issue. 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

Once the TGT is obtained, we can test the full connection and authentication. In this example we provide no username and password in the connection string – instead the Oracle client is relying on the Kerberos ticket:

echo "
set heading off
select 'DB_NAME (from v\$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v\$database;
" | sqlplus -s /@ORCL

Sample output:

[oracle@dbserv1 ~]$ echo "
> set heading off
> select 'DB_NAME (from v\$database) : '||name,
>        'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
>        'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
>        'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
>        'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
>        'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
>        'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
>   from v\$database;
> " | sqlplus -s /@ORCL

DB_NAME (from v$database) : XE
SESSION_USER              : SIMON@STAGECOACH.NET
AUTHENTICATED_IDENTITY    : simon@STAGECOACH.NET
AUTHENTICATION_METHOD     : KERBEROS
AUTHENTICATION_TYPE       : NETWORK
LDAP_SERVER_TYPE          :
ENTERPRISE_IDENTITY       : simon@STAGECOACH.NET


[oracle@dbserv1 ~]$

From the above we can see that everything worked as expected as the authentication type was “NETWORK” and the authentication method “KERBEROS”. But this test was from the actual database server. A more realistic scenario requires testing from the Linux and Windows application servers.

Client Setup on Linux and Testing with a Domain User

Unfortunately, when testing with Kerberos authentication, additional Oracle client software setup is required:

  1. The Oracle Instant Client will no longer suffice – we must now do a proper Oracle Client installation though we can minimize the installed components.
  2. The client software’s SQLNET.ORA file must be adjusted to include Kerberos-related parameters.
  3. A Kerberos configuration file must be created.

These are all additional setup steps required on the client software end which were not required for CMU password-based authentication.

And like with the XE database software, the full Oracle Client media requires an authenticated download and, hence, cannot be done in an elegant programmatic command. Therefore, manually download the Oracle Client software and transfer to the application servers.

On the Linux, application server, first prepare the server for the Oracle software. As “root”:

# Quick shortcut: use the DB pre-install RPM as a quick method to install required dependencies:
yum install -y oracle-database-preinstall-19c

mkdir -p /u01/app/oracle/product/19.0.0/client_1
mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/oracle_software
chown -R oracle:oinstall /u01
chmod -R 775 /u01
 
cat <<EOT >> /home/oracle/.bash_profile
   
#  Oracle RDBMS Settings:
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\${ORACLE_BASE}/product/19.0.0/client_1
export PATH=\${ORACLE_HOME}/bin:\${PATH}
EOT

Then as the “oracle” user install the full client using a customized response file for reusability (if required):

# Assumes that the client ZIP file has been installed into the current working directory:
unzip LINUX.X64_193000_client.zip -d /u01/app/oracle_software/

cp /u01/app/oracle_software/client/response/client_install.rsp ~/client_install.rsp

sed -i '/^oracle.install.client.installType/ s~oracle.install.client.installType=$~oracle.install.client.installType=Custom~' ~/client_install.rsp
sed -i '/^oracle.install.client.customComponents/ s~oracle.install.client.customComponents=$~oracle.install.client.customComponents="oracle.sqlplus:19.0.0.0.0","oracle.rdbms.oci:19.0.0.0.0","oracle.network.aso:19.0.0.0.0","oracle.network.client:19.0.0.0.0"~' ~/client_install.rsp
sed -i '/^UNIX_GROUP_NAME/ s~UNIX_GROUP_NAME=$~UNIX_GROUP_NAME=oinstall~' ~/client_install.rsp
sed -i '/^INVENTORY_LOCATION/ s~INVENTORY_LOCATION=$~INVENTORY_LOCATION=/u01/app/oraInventory~' ~/client_install.rsp
sed -i '/^ORACLE_HOME/ s~ORACLE_HOME=$~ORACLE_HOME='${ORACLE_HOME}'~' ~/client_install.rsp
sed -i '/^ORACLE_BASE/ s~ORACLE_BASE=$~ORACLE_BASE='${ORACLE_BASE}'~' ~/client_install.rsp

diff /u01/app/oracle_software/client/response/client_install.rsp ~/client_install.rsp

/u01/app/oracle_software/client/runInstaller -silent -waitforcompletion -responseFile ~/client_install.rsp

Once the client installation is complete, run the required root script as “root”:

/u01/app/oraInventory/orainstRoot.sh

Reverting back to the “oracle” user, again the required Kerberos parameters must be added to the SQLNET.ORA file:

mkdir -p ${ORACLE_HOME}/network/admin/kerberos

echo "
SQLNET.KERBEROS5_CONF=${ORACLE_HOME}/network/admin/kerberos/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
" >> ${ORACLE_HOME}/network/admin/sqlnet.ora

cat ${ORACLE_HOME}/network/admin/sqlnet.ora

And of course the Kerberos configuration file must be created:

cat <<EOT >> ${ORACLE_HOME}/network/admin/kerberos/krb5.conf
[libdefaults]
default_realm = STAGECOACH.NET

[realms]
STAGECOACH.NET = {
  kdc = DC1.STAGECOACH.NET:88
}

[domain_realm]
.`dnsdomainname` = STAGECOACH.NET
`dnsdomainname` = STAGECOACH.NET
EOT

cat ${ORACLE_HOME}/network/admin/kerberos/krb5.conf

That should be all of the required Linux Client setup completed. And this point, we can test using the Active Directory “Simon” user.

From any machine, log into the Linux Application server as the test AD domain user. For example:

ssh "simon@stagecoach.net"@10.0.1.102

To truly achieve single sign-on, we don’t want to have to manually run additional commands such as okinit used earlier. Instead we want to simply log into the OS and that’s all. Consequently, it is essential to validate that we have a Kerberos ticket stored in a FILE credential cache that the Oracle Client software can use using the Linux klist command. We can easily confirm by just running the Linux klist command.

Example output:

[simon@STAGECOACH.NET@applinux1 ~]$ klist
Ticket cache: FILE:/tmp/krb5cc_747601111
Default principal: simon@STAGECOACH.NET

Valid starting       Expires              Service principal
11/27/2019 01:09:41  11/27/2019 11:09:41  krbtgt/STAGECOACH.NET@STAGECOACH.NET
        renew until 12/04/2019 01:09:41
[simon@STAGECOACH.NET@applinux1 ~]$

If the output from klist shows “KEYRING”, we need to adjust /etc/krb5.conf and restart the sssd service as per the steps described in Part 2 of this article series.

To use SQLPlus for an actual connection test, we might need to manually set our ORACLE_HOME variable and adjust our PATH variable for the test user since we never configured .bash_profile or any other environment configuration files:

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/client_1
export PATH=${ORACLE_HOME}/bin:${PATH}

Then we can perform a simple test:

echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

And the connection should succeed. For example:

[simon@STAGECOACH.NET@applinux1 ~]$ echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1
USER is "SIMON@STAGECOACH.NET"
[simon@STAGECOACH.NET@applinux1 ~]$

Going a step further with a more elaborate test showing additional connection property details:

echo "
set heading off
select 'DB_NAME (from v\$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v\$database;
" | sqlplus -s -L /@//dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

Sample output:

[simon@STAGECOACH.NET@applinux1 ~]$ echo "
> set heading off
> select 'DB_NAME (from v\$database) : '||name,
>        'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
>        'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
>        'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
>        'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
>        'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
>        'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
>   from v\$database;
> " | sqlplus -s -L /@//dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

DB_NAME (from v$database) : XE
SESSION_USER              : SIMON@STAGECOACH.NET
AUTHENTICATED_IDENTITY    : simon@STAGECOACH.NET
AUTHENTICATION_METHOD     : KERBEROS
AUTHENTICATION_TYPE       : NETWORK
LDAP_SERVER_TYPE          :
ENTERPRISE_IDENTITY       : simon@STAGECOACH.NET


[simon@STAGECOACH.NET@applinux1 ~]$

Client Setup on Windows and Testing with a Domain User

Similarly, on the Windows application server APPWIN1, the Oracle Instant Client will no longer suffice and we must instead install the full Oracle Client. Assuming that the required media has been manually downloaded and transferred to the APPWIN1 server, it can be installed from a PowerShell window (logged in as the Administrator user) using commands such as:

# Assumes that the client ZIP file has been installed into the current working directory:
expand-archive -path '.\WINDOWS.X64_193000_client.zip' -destinationpath $Env:TEMP

cp $Env:TEMP\client\response\client_install.rsp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "oracle.install.client.installType=$","oracle.install.client.installType=Custom"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "oracle.install.client.customComponents=$","oracle.install.client.customComponents=oracle.sqlplus:19.0.0.0.0,oracle.rdbms.oci:19.0.0.0.0,oracle.network.aso:19.0.0.0.0,oracle.network.client:19.0.0.0.0"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "ORACLE_HOME=$","ORACLE_HOME=C:\Oracle\product\19.0.0\client_1"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "ORACLE_BASE=$","ORACLE_BASE=C:\Oracle"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

cat $HOME\Desktop\client_install.txt | %{$_ -replace "oracle.install.IsBuiltInAccount=$","oracle.install.IsBuiltInAccount=true"} > $HOME\Desktop\client_install.tmp
mv -force $HOME\Desktop\client_install.tmp $HOME\Desktop\client_install.txt

Get-Content .\client_install.txt | out-file -encoding ASCII .\client_install.rsp
diff (cat $Env:TEMP\client\response\client_install.rsp) (cat $HOME\Desktop\client_install.rsp)

# Run installer using response file:
& "$Env:TEMP\client\setup.exe" -silent -waitforcompletion -responseFile $HOME\Desktop\client_install.rsp

After installation completes, add the necessary Kerberos parameters to the SQLNET.ORA file:

$ORACLE_HOME="C:\Oracle\product\19.0.0\client_1"
mkdir "$ORACLE_HOME\network\admin\kerberos"

echo @"
SQLNET.KERBEROS5_CC_NAME=MSLSA:
SQLNET.KERBEROS5_CONF=$ORACLE_HOME\network\admin\kerberos\krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
"@ | Add-Content "$ORACLE_HOME\network\admin\sqlnet.ora" -Encoding ASCII

cat "$ORACLE_HOME\network\admin\sqlnet.ora"

IMPORTANT: Note that the trailing “:” after the word MSLSA is required and is sometimes missing from Oracle documentation.

Then build the required Kerberos configuration file (note the hardcoded environment-specific values in the command – adjust as required):

echo @"
[libdefaults]
default_realm = STAGECOACH.NET

[realms]
STAGECOACH.NET = {
  kdc = DC1.STAGECOACH.NET:88
}

[domain_realm]
.ad1.cmuvnc.oraclevcn.com = STAGECOACH.NET
ad1.cmuvnc.oraclevcn.com = STAGECOACH.NET
"@ | Out-File "$ORACLE_HOME\network\admin\kerberos\krb5.conf" -Encoding ASCII

cat $ORACLE_HOME\network\admin\kerberos\krb5.conf

At this point, log out and back into the APPWIN1 server as the test user. In this case, logging into the APPWIN1 server as STAGECOACH\simon.

Then on a new PowerShell window and try connecting using a simple test command:

echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

At this point, you might be presented with a common error:

PS C:\Users\Simon> echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1
ERROR:
ORA-12638: Credential retrieval failed


SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
PS C:\Users\Simon>

ORA-12638 is a non-specific “catch-all” error message. For troubleshooting and tracing Kerberos connection problems see: Kerberos Troubleshooting Guide (Doc ID 185897.1)

In this case, the error is due to an Oracle bug related to the Kerberos ticket being issued with the “forwardable” flag enabled as evident by running oklist with the -f argument:

PS C:\Users\Simon> oklist -f

Kerberos Utilities for 64-bit Windows: Version 19.0.0.0.0 - Production on 26-NOV-2019 23:51:54

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : C:\Oracle\product\19.0.0\client_1\network\admin\kerberos\krb5.conf.
Ticket cache: MSLSA:
Default principal: simon@STAGECOACH.NET

Valid starting     Expires            Service principal
11/26/19 23:42:51  11/27/19 09:42:38  oracle/dbserv1.ad1.cmuvnc.oraclevcn.com@STAGECOACH.NET
        renew until 12/03/19 23:42:38, Flags: FRA
11/26/19 23:42:38  11/27/19 09:42:38  LDAP/DC1.STAGECOACH.NET/STAGECOACH.NET@STAGECOACH.NET
        renew until 12/03/19 23:42:38, Flags: FRAO
PS C:\Users\Simon>

Notice: “Flags: FRA“.

This can be resolved by logging in as the domain Administrator on the Active Directory domain controller DC1. If adjusting using the GUI utility “Active Directory Users and Computers”, check the “Account is sensitive and cannot be delegated” checkbox – it is unchecked by default:

Or use a PowerShell window and the following commands to verify the setting and adjust it:

# Check setting - default value is "False":
Get-ADUser -Identity <Domain User> -Properties AccountNotDelegated

# Adjust setting:
Set-ADAccountControl -AccountNotDelegated $True -Identity <Domain User>

# Validate that the change was made:
Get-ADUser -Identity <Domain User> -Properties AccountNotDelegated

Sample output:

PS C:\Users\Administrator> Get-ADUser -Identity simon -Properties AccountNotDelegated


AccountNotDelegated : False
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> Set-ADAccountControl -AccountNotDelegated $True -Identity simon
PS C:\Users\Administrator>
PS C:\Users\Administrator> Get-ADUser -Identity simon -Properties AccountNotDelegated


AccountNotDelegated : True
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>

Credit for identifying and resolving this specific issue goes to: https://www.spotonoracle.com/?p=451 . For additional details on this option and the meaning and implications see: https://blogs.technet.microsoft.com/poshchap/2015/05/01/security-focus-analysing-account-is-sensitive-and-cannot-be-delegated-for-privileged-accounts/

After logging out and back in again (still as the STAGECOACH\simon domain user), the “FRA” flag should no longer be present:

PS C:\Users\Simon> oklist -f

Kerberos Utilities for 64-bit Windows: Version 19.0.0.0.0 - Production on 26-NOV-2019 23:54:11

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : C:\Oracle\product\19.0.0\client_1\network\admin\kerberos\krb5.conf.
Ticket cache: MSLSA:
Default principal: simon@STAGECOACH.NET

Valid starting     Expires            Service principal
11/26/19 23:54:00  11/27/19 09:54:00  LDAP/DC1.STAGECOACH.NET/STAGECOACH.NET@STAGECOACH.NET
        renew until 12/03/19 23:54:00, Flags: RAO
PS C:\Users\Simon>

Though, notice that the ticket cache is MSLSA: meaning we can connect using the Kerberos ticket cached automatically by Windows without having to manually obtain (which is exactly what we want).

At the point, the connection can be re-tested and should work:

echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

Sample output:

PS C:\Users\Simon> echo "show user" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1
USER is "SIMON@STAGECOACH.NET"
PS C:\Users\Simon>

If you’re still receiving the ORA-12638 error it may be due to username case sensitivity. The authenticated identity and the Kerberos ticket must match exactly, meaning that logging into Windows as STAGECOACH\Simon and STAGECOACH\SIMON might fail while logging in a STAGECOACH\simon works.

To validate the case details of the Active Directory domain user, from the Domain Controller DC1 either validate in the “Active Directory Users and Computers”:

Or from PowerShell:

After logging in to the APPWIN1 server using the proper case username, the connection test should succeed. The TGT (and username case) can be validated using the Oracle provided oklist utility:

Notice it’s only the database that reports the user in all upper case as it was created that way.

Repeating with the more elaborate options to show additional connection properties:

echo "
set heading off
select 'DB_NAME (from v`$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v`$database;
" | sqlplus -s /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

Sample output:

Next Steps

Now that CMU is configured for Kerberos-based authentication, more advanced mappings can be tested:

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

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

email

Authors

Interested in working with Simon? Schedule a tech call.

About the Author

Simon describes himself as a technology enthusiast who is passionate about collecting and sharing interesting database tips. If you want to see his eyes light up, let him teach you something new. Based out of Calgary, Alberta, Simon is known for his contributions to various online Oracle communities, and being very thorough in his work. A self-proclaimed stereotypical Canadian, Simon can be found watching hockey with his family in his spare time.

14 Comments. Leave new

sudhamani Mudgal
June 10, 2020 5:39 am

Hi Simon,

using kerberos authentication, I am able to connect to user SIMON@STAGECOACH.NET which is created externally;

Is it possible to map the user SIMON@STAGECOACH.NET. to connect to some shared schema using CMU kerberos authentication method . ? Please suggest

I followed below steps:

1. created user group say “sales_group” in Active Directory users
2. Added user MSUDHAMANI@LOCALDOMAIN.COM as a member of group ” sales_group”
3. create user sales identified globally as ‘cn=sales_group,cn=Users,dc=LOCALDOMAIN,dc=COM’;

4. connect to sqlplus /@ORCLPDB1

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 9 03:46:12 2020

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

ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service

Reply

Shared schema for Kerberos authentication is described in MOS Doc ID 2462012.1. For your specific issue, it could be an issue/bug with your RDBMS installation. You don’t show what version you are using – just that your client sqlplus is 12.2 – presumably the server is 18c+. See recommendation for your specific error in that same MOS Doc. Also a handy reference for troubleshooting is MOS Doc ID 185897.1.

Reply
sudhamani Mudgal
June 10, 2020 9:25 am

Thanks Simon ! For quick reply.
Yes, the database is Oracle 18C.

Reply

In my test environment (based on the setup instructions provided in this blog series), I was able to successfully connect to the shared schema using Kerberos authentication.

On my domain controller:

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

name
----
Domain Users
ORA_VFR_12C
sales_group

PS C:\Users\Administrator>

In my DB instance:

SQL> select username,password,external_name from dba_users where username = 'SALES_GROUP';

USERNAME     PASSWORD     EXTERNAL_NAME
------------ ------------ ------------------------------------------------
SALES_GROUP  GLOBAL       cn=sales_group,cn=Users,dc=stagecoach,dc=net

SQL>

Then testing from another server (Linux for simplicity):

[simon@STAGECOACH.NET@applinux1 ~]$ echo "
set heading off
select 'DB_NAME (from v\$database) : '||name,
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'AUTHENTICATION_TYPE       : '||sys_context('USERENV','AUTHENTICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
  from v\$database;
" | sqlplus -s -L /@dbserv1.ad1.cmuvnc.oraclevcn.com:1521/XEPDB1

DB_NAME (from v$database) : XE
SESSION_USER              : SALES_GROUP
AUTHENTICATED_IDENTITY    : simon@STAGECOACH.NET
AUTHENTICATION_METHOD     : KERBEROS_GLOBAL
AUTHENTICATION_TYPE       : NETWORK
LDAP_SERVER_TYPE          : AD
ENTERPRISE_IDENTITY       : cn=Simon Pane,cn=Users,dc=STAGECOACH,dc=NET

[simon@STAGECOACH.NET@applinux1 ~]$

^ Works !

Reply
sudhamani Mudgal
June 10, 2020 11:31 am

Wow! Thanks Simon.

It sounds silly, but i followed only article PART 4: Implementing Oracle Database Single Sign-On Using Kerberos, Active Directory, And Oracle CMU. :P

installing opwdintg.exe password verifier and adding the user to group to ORA_VFR_12C will suffice ?

Do i have to follow all the steps mentioned in the PART 3: Implementing Oracle Database Active Directory Password Synchronization Using Oracle CMU.

Thanks in advance !

Shouldn’t need to: the password filter and the associated VFR groups is really just for the directory synchronization implementation. Since Oracle needs to “trap” password changes and store its own hash value of the new password.

sudhamani Mudgal
June 11, 2020 4:07 pm

can you show how the sales_group user has been created ?

I showed already: sales_group is simply a security group in active directory. Nothing more. AD user “simon” then added to that security group. And in the DB defined externally with the external_name as shown.

sudhamani Mudgal
June 11, 2020 10:36 am

Hi Simon,

I am facing same issue ORA-28030: Server encountered problems accessing LDAP directory service.
I have enabled the trace to check whats going wrong.
When i connect to database with USER (user created externally) the trace file doesn’t refer to any wallet location.
Whereas when i connect to database with User (created globally) , it locates for wallet file, sslconf.ora
/opt/oracle/product/18c/dbhome_1//ldap/admin/sslconf.ora

Users created globally does refer to wallet ?

However, i have deleted the wallet directory manually.

———————-trace file————
[11-JUN-2020 11:10:59:784] /opt/oracle/product/18c/dbhome_1//ldap/admin/sslconf.ora
[11-JUN-2020 11:10:59:784] sslconf.ora file could not be opened. Continuing.

[11-JUN-2020 11:10:59:784] nzosReadConfFile: exit
[11-JUN-2020 11:10:59:784] nzdcpgp_get_parameter: entry
[11-JUN-2020 11:10:59:784] nzsuppgv_get_value: entry
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: entry
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: parameter “WALLET_LOCATION” does not exist.
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: exit
[11-JUN-2020 11:10:59:784] nzsuppgv_get_value: exit
[11-JUN-2020 11:10:59:784] nzdcpgp_get_parameter: exit
[11-JUN-2020 11:10:59:784] nzdcpgp_get_parameter: entry
[11-JUN-2020 11:10:59:784] nzsuppgv_get_value: entry
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: entry
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: parameter “oss.source.my_wallet” does not exist.
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: exit
[11-JUN-2020 11:10:59:784] nzsuppgv_get_value: exit
[11-JUN-2020 11:10:59:784] nzdcpgp_get_parameter: exit
[11-JUN-2020 11:10:59:784] nzupawp_apply_wrl_policy: entry
[11-JUN-2020 11:10:59:784] nzupgew_get_environ_wrl: entry
[11-JUN-2020 11:10:59:784] Environment Variable not found or empty value.
[11-JUN-2020 11:10:59:784] Using wallet locator from caller argument ..
[11-JUN-2020 11:10:59:784] nzdtrsr_store_certreq: entry
[11-JUN-2020 11:10:59:784] nzsuppwl_wallet_lookup: entry
[11-JUN-2020 11:10:59:784] nzsuppgv_get_value: entry
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: entry
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: parameter “WALLET_LOCATION” does not exist.
[11-JUN-2020 11:10:59:784] nzsuppgp_get_parameter: exit
[11-JUN-2020 11:10:59:784] nzsuppgv_get_value: exit
[11-JUN-2020 11:10:59:784] nzsuppgfd_get_file_data: entry
[11-JUN-2020 11:10:59:784] nzsuppgs_get_string: entry
[11-JUN-2020 11:10:59:784] nzsuppaf_assemble_filename: entry
[11-JUN-2020 11:10:59:784] snzdafn_assemble_filename: entry
[11-JUN-2020 11:10:59:784] nzsuppwl_wallet_lookup: Resulting value:
Parameter “WALLET_LOCATION”
Method: “FILE”
Filename: “/opt/oracle/admin/ORCLCDB/865627F415A45E75E0550250560119BB/wallet/ewallet.p12”
[11-JUN-2020 11:10:59:784] nzsuppwl_wallet_lookup: exit
[11-JUN-2020 11:10:59:784] nzsuppwl_wallet_lookup: entry

Reply
Bert vdrHorst
July 22, 2020 8:54 am

Hi Simon,
Thanks for the clear explanations.
Regarding the second mentioned “minor” catch in your begining of this doc: ==> The application must support and be able to pass empty string credentials…
Do you know if (and how) Oracle Forms applications could be used i.c.w. SSO/Kerberos/CMU?

Reply

Afraid I don’t know about Forms and Kerberos – would definitely work with the Directory Sync flavor of CMU instead though.

Reply

Hi Simon, we createe below AD groups in Database for CMU kerberos authentication and followed below steps on PDB2.

create user cmu_connect identified globally as ‘CN=cmu_connect,OU=Groups,OU=InfraTeam,DC=LOCALDOMAIN,DC=local’;
GRANT create session TO cmu_connect;
GRANT SELECT ON v_$database TO cmu_connect;
CREATE cmu_role IDENTIFIED GLOBALLY AS ‘CN=cmu_connect,OU=Groups,OU=InfraTeam,DC=LOCALDOMAIN,DC=local’;

we assined cmuone user to cmu_connect group AD

while accessing AD user in Database are getting below error for kerberos authentication

[oracle@LOCALDOMAINhydoud4 ~]$ okinit cmuone

Kerberos Utilities for Linux: Version 19.0.0.0.0 – Production on 12-SEP-2020 00:08:19

Copyright (c) 1996, 2019 Oracle. All rights reserved.

Configuration file : /etc/krb5.conf.
Password for cmuone@LOCALDOMAIN.LOCAL:
[oracle@LOCALDOMAINhydoud4 ~]$ oklist

Kerberos Utilities for Linux: Version 19.0.0.0.0 – Production on 12-SEP-2020 00:08:30

Copyright (c) 1996, 2019 Oracle. All rights reserved.

Configuration file : /etc/krb5.conf.
Ticket cache: FILE:/usr/tmp/krbcache
Default principal: cmuone@LOCALDOMAIN.LOCAL

Valid starting Expires Service principal
09/12/20 00:14:23 09/12/20 10:14:23 krbtgt/LOCALDOMAIN.LOCAL@LOCALDOMAIN.LOCAL
renew until 09/19/20 00:14:14
[oracle@LOCALDOMAINhydoud4 ~]$ sqlplus /@pdb2;

SQL*Plus: Release 19.0.0.0.0 – Production on Sat Sep 12 00:08:38 2020
Version 19.3.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied

Could you please provide your guidance on this.

Regards,
Sainath A

Reply

Hi SImon,

Could you please provide your guidance on Kerberos-based authentication for groups

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

Regards,
Sainath A

Reply

I would recommend using AD security groups. Definitely for the authentication part – to offload all of that user management away from the DB. As for using security groups for authorization – I think it depends on your organization requirements. Probably makes sense also.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *