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

Posted in: Technical Track

eempty 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

IMPORTANT: When creating the user IDENTIFIED EXTERNALLY, we are not yet using CMU – EXTERNAL identification is for pre-CMU Kerberos authentication. To enable the CMU part, change to IDENTIFIED GLOBALLY AS ‘distinguished_name’. This is required for CMU authentication and authorization. But to start, it’s best to first implement pre-CMU Kerberos authentication, and then when that is working properly, switch into CMU Kerberos authentication and test the benefits that CMU brings via “shared schemas” and “global roles”.

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

Author

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.

30 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.

Hello Simon,

I am also in the same boat as sudhamani Mudgal and I think that part where what is infact needed to have DB talk to AD when using kerberos is kind of gray and confusing.

1. In my case I created a service ID on AD side. SVTORADMIN
2. Had Kerberos authentication working for a user called M41223 (which is also on AD) when I created the user as identified externally in the oracle DB.

Now I wanted to switch to global authentication removing DB account management from the DB and rely on AD managing the authentication and authorization for user M41223. For that this is what I did.

1. The kerberos settings (krb conf, ktpass,sqlnet.ora files) on the DB client and DB server are still intact and they are not altered at all.
2. Created a global group called G_ORA_DBRWUSER on AD side.
3. Added this group to user M41223 on AD side.
4. dropped the user M41223 which was earlier part of the DB.
5. Created a user called shared_user with AD CN name pointing to the group created above.

create user shared_user identified globally as ‘CN=G_ORA_DBRWUSER,OU=Users,OU=CORP,OU=LOBs,DC=silver,DC=com’;
grant create session to shared_user;
grant select on v_$database to shared_user;

6. Now trying to connect to the oracle db with username M41223 fails

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

Configuration file : /omsh1/client_19c/network/admin/kerberos/krb5.conf.
okdstry: No credentials cache found (filename: /tmp/krb5cc_file) while destroying cache.
[M41223@cilsdbclt0001 ~]$ okinit M41223

Kerberos Utilities for Linux: Version 19.0.0.0.0 – Production on 10-DEC-2020 16:14:43

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

Configuration file : /omsh1/client_19c/network/admin/kerberos/krb5.conf.
Password for M41223@SILVER.COM:
[M41223@cilsdbclt0001 ~]$ oklist -f

Kerberos Utilities for Linux: Version 19.0.0.0.0 – Production on 10-DEC-2020 16:14:56

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

Configuration file : /omsh1/client_19c/network/admin/kerberos/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_file
Default principal: M41223@SILVER.COM

Valid starting Expires Service principal
12/10/20 16:14:46 12/11/20 02:14:43 krbtgt/SILVER.COM@SILVER.COM
renew until 12/17/20 16:14:43, Flags: FPRIA

Now I try connecting it failes with ORA-01017 password.
[M41223@cilsdbclt0001 ~]$ echo “set heading off
select ‘DB_NAME (from v\$database) : ‘||name,
‘SESSION_USER : ‘||sys_context(‘USERENV’,’SESSION_USER’),
‘CURRENT_SCHEMA: ‘||SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’),
‘AUTHENTICATED_IDENTITY : ‘||sys_context(‘USERENV’,’AUTHENTICATED_IDENTITY’),
‘AUTHENTICATION_METHOD : ‘||sys_context(‘USERENV’,’AUTHENTICATION_METHOD’),
‘AUTHENTICATION_TYPE : ‘||sys_context(‘USERENV’,’IDENTIFICATION_TYPE’),
‘ENTERPRISE_IDENTITY : ‘||sys_context(‘USERENV’,’ENTERPRISE_IDENTITY’)
from v\$database;” | sqlplus -s /@cilorcld1050.sys.cigna.com:1602/adtst.cigna.com
ERROR:
ORA-01017: invalid username/password; logon denied

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
Sainath Aparacharla
November 13, 2020 7:03 am

HI Simon, when we okdstry we are getting No credentials cache file found

okdstry -old

Kerberos Utilities for Linux: Version 12.2.0.1.0 – Production on 13-NOV-2020 06:42:01

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

okdstry: Credential cache /tmp/krb5cc_10010 not found.
okdstry: No credentials cache file found

Could you please help me with this ?

Reply

HI Simon, when we okdstry we are getting No credentials cache file found

okdstry -old

Kerberos Utilities for Linux: Version 12.2.0.1.0 – Production on 13-NOV-2020 06:42:01

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

okdstry: Credential cache /tmp/krb5cc_10010 not found.
okdstry: No credentials cache file found

Could you please help me with this ?

Reply

Issue with the value set for SQLNET.KERBEROS5_CC_NAME in the sqlnet.ora file? Or a global setting issue in /etc/krb5.conf ?

Reply

Hi Simon – I wish all MOS articles were as systematic and well explained! – each of the 4 parts are excellent and if anyone wants to learn how to setup and configure CMU, these 4 articles should be treated as a ‘bible’! – great work and beers are surely on me the next time we can meet!

Reply

Thanks for the nice words. I really miss not travelling and presenting in person on this topic and others. Hope to be back to in-person conferences and events and meeting in 2022.

Reply

Now I switch back to external authentication it does work good., So what is missing here? I think we need to configure DB to talk to AD.. SO how is it done in case of using kerberos auth which is not clear as per the documentation provided either by oracle or the MOS notes.

SQL> create user M41223 identified externally as ‘M41223@SILVER.COM’;
grant create session to M41223;
grant select on v_$database to M41223;
User created.

SQL>
Grant succeeded.

SQL> create user M41223 identified externally as ‘M41223@SILVER.COM’;
grant create session to M41223;
grant select on v_$database to M41223;
User created.

SQL>
Grant succeeded.

SQL>

Grant succeeded.
[M41223@cilsdbclt0001 ~]$ echo “set heading off
select ‘DB_NAME (from v\$database) : ‘||name,
‘SESSION_USER : ‘||sys_context(‘USERENV’,’SESSION_USER’),
‘CURRENT_SCHEMA: ‘||SYS_CONTEXT(‘USERENV’, ‘CURRENT_SCHEMA’),
‘AUTHENTICATED_IDENTITY : ‘||sys_context(‘USERENV’,’AUTHENTICATED_IDENTITY’),
‘AUTHENTICATION_METHOD : ‘||sys_context(‘USERENV’,’AUTHENTICATION_METHOD’),
‘AUTHENTICATION_TYPE : ‘||sys_context(‘USERENV’,’IDENTIFICATION_TYPE’),
‘ENTERPRISE_IDENTITY : ‘||sys_context(‘USERENV’,’ENTERPRISE_IDENTITY’)
from v\$database;” | sqlplus -s /@cilsdbclt0001.sys.domain.com:1602/adtst.domain.com

DB_NAME (from v$database) : ADTST
SESSION_USER : M41223
CURRENT_SCHEMA: M41223
AUTHENTICATED_IDENTITY : M41223@SILVER.COM
AUTHENTICATION_METHOD : KERBEROS
AUTHENTICATION_TYPE : EXTERNAL
ENTERPRISE_IDENTITY : M41223@SILVER.COM

Reply

Hi Ravi, to troubleshoot try the following:

1) Focus only on the “EXCLUSIVE USER” to start, not the shared schema mapping to the group. For simplicity and to rule out AD group membership issues.
2) Don’t drop users – just ALTER them.
3) Try toggling between an older, non-CMU Kerberos authentication and CMU Kerberos

To expand on #3, I can switch to non-CMU Kerberos using “… INDENTIFIED EXTERNALLY AS ‘‘”. And back to Kerberos CMU using “… INDENTIFIED GLOBALLY AS ‘‘”. Examples of each:

alter user krb_simonp identified externally as ‘simonp@STAGECOACH.NET’;
alter user krb_simonp identified globally as ‘CN=Simon Pane,CN=Users,DC=STAGECOACH,DC=NET’;

When you use EXTERNALLY AS and connect you should see something like this:

PS C:\Users\simonp> echo “select user||’ ‘||sys_context(‘userenv’,’AUTHENTICATION_METHOD’) from dual;” | sqlplus -s -L /@ORCL

USER||”||SYS_CONTEXT(‘USERENV’,’AUTHENTICATION_METHOD’)
——————————————————————————–
KRB_SIMONP KERBEROS

PS C:\Users\simonp>

When you “turn on” CMU (via ALTER USER … INDENTIFIED GLOBALLY …) it should change to this:

PS C:\Users\simonp> echo “select user||’ ‘||sys_context(‘userenv’,’AUTHENTICATION_METHOD’) from dual;” | sqlplus -s -L /@ORCL

USER||”||SYS_CONTEXT(‘USERENV’,’AUTHENTICATION_METHOD’)
——————————————————————————–
KRB_SIMONP KERBEROS_GLOBAL

PS C:\Users\simonp>

In both the connected user was the same, but authentication method changed slightly.

If you want to be really sure that you’re using CMU or not, change the WALLET_LOCATION setting in the SQLNET.ORA or if you want a very quick way to test, just rename the wallet directory so the value pointed to in the SQLNET.ORA is invalid.

Bottom line: you want to make sure that Kerberos is working first. Then that CMU “exclusive user” CMU Kerberos is working. If when you make the switch to CMU Kerberos you still get the error try:

1. Testing the Wallet using ldapbind and ldapsearch commands. Ensure the username, password, DN, and certificate contained within it are valid.
2. SQLNET tracing.

Simon.

Reply

Thank you Simon.

Looks like there was some issue with how the entries were created in wallet. I dropped and recreated the wallet and things worked. I am not able to map user M41223 exclusively to its ID on AD side.

DB_NAME (from v$database) : ADTST
SESSION_USER : M41223
CURRENT_SCHEMA: M41223
AUTHENTICATED_IDENTITY : M41223@SILVER.COM
AUTHENTICATION_METHOD : KERBEROS_GLOBAL <======
AUTHENTICATION_TYPE : GLOBAL EXCLUSIVE <=======
ENTERPRISE_IDENTITY : cn=H42123,ou=Users,ou=CORP,ou=LOBs,dc=silver,dc=com

But I still cannot make it work with a global group mapped to shared user in the DB.

1. Dropped user M41223 from the DB.

2. Created a shared_user on the database mapped to the global group on AD
create user shared_user identified globally as 'CN=G_ORA_DBRWUSER,OU=Users,OU=CORP,OU=LOBs,DC=silver,DC=com';
grant create session to shared_user;
grant select on v_$database to shared_user;

3. Made sure M41223 is part of group G_ORA_DBRWUSER on AD side.

4. Re-initiated the kerberos auth from client side and tried making connection to the DB and it failed with invalid username password.

echo "set heading off
select 'DB_NAME (from v\$database) : '||name,
'SESSION_USER : '||sys_context('USERENV','SESSION_USER'),
'CURRENT_SCHEMA: '||SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
'AUTHENTICATED_IDENTITY : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
'AUTHENTICATION_METHOD : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
'AUTHENTICATION_TYPE : '||sys_context('USERENV','IDENTIFICATION_TYPE'),
'ENTERPRISE_IDENTITY : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
from v\$database;" | sqlplus -s /@cilorcld1050.sys.domain.com:1602/adtst.domain.com
ERROR:
ORA-01017: invalid username/password; logon denied

Reply

Hi Ravi, I would suggest either:

1) SQLNET tracing for additional details (more granular errors – 1017 is almost an umbrella error code at this point, many different underlying issues can be rolled up into 1017 at the user level)
2) Test the wallet and AD DC accessibility via ldapsearch and ldapbind commands (take the DB out of the picture)

For simplicity I would probably recommend starting with the second suggestion: test AD connectivity via ldapbind and ldapsearch including on port 636 using the wallet and enclosed certificate.

Reply

Thank you Simon, I will start looking into those.

DOes this ldapbind/ldapsearch needs to be run from client machine or DB server?

In my case I ran ldapbind which failed from both sides (client and db side) but with different errors on each.

Well thats a stupid question of mine…… the wallet is on the DB side so it would make sense to run them only from the DB server.

Reply

Yes. Try running ${ORACLE_HOME}/bin/ldapbind with both “-p 389” and “-p 636”. For the latter, include ‘-U 3 -W “file:“‘

Just to validate that the four pieces of information in the wallet at correct. And that a firewall or routing isn’t blocking. You could also try with ${ORACLE_HOME}/bin/ldapsearch and make sure some search data is being returned.

I would do that to rule out the wallet and access to the DCs (remember to repeat for each DC that you have listed in your dsi.ora file). Also keep the dsi.ora file in the same path (location) as the wallet.

Reply

I can get ldapbind to work with both ports and ldapsearch aswell.

ldapbind -h myldap.silver.com -p 636 -D “CN=ORAADMIN,OU=ServiceAccounts,DC=silver,DC=com” -w XXXXX -U 2 -W “file:/u01/cmu_wallet/” -P XXXXX
bind successful

->ldapbind -h myldap.silver.com -p 389 -D “CN=ORAADMIN,OU=ServiceAccounts,DC=silver,DC=com” -w xxxxxx
bind successful

so wondering where the issue really is.

Reply

Yay it worked…there was a typo in the shared users DN name in the user creation script which I used. Fixing it resolved the issue. Thank you for all of your wonderful work and help.

Reply

Fantastic!

Reply

Leave a Reply

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