Share this
by Simon Pane on Feb 24, 2020 10:06:00 AM
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 ` |
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 |
Or via "Active Directory Users and Computers" if preferable:
.png?width=700&height=434&name=download%20(2).png)
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> ` |
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 ` |
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/ |
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 |
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 |
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 |
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
|
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
|
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 " |
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> |
Sample output:
[oracle@dbserv1 ~]$ okinit simon |
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 " |
Sample output:
[oracle@dbserv1 ~]$ echo " |
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:
- The Oracle Instant Client will no longer suffice - we must now do a proper Oracle Client installation though we can minimize the installed components.
- The client software's SQLNET.ORA file must be adjusted to include Kerberos-related parameters.
- 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: |
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: |
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
|
And of course the Kerberos configuration file must be created:
cat <<EOT >> ${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 |
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 |
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 |
Going a step further with a more elaborate test showing additional connection property details:
echo " |
Sample output:
[simon@STAGECOACH.NET@applinux1 ~]$ echo " |
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: |
After installation completes, add the necessary Kerberos parameters to the SQLNET.ORA file:
$ORACLE_HOME="C:\Oracle\product\19.0.0\client_1" |
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 @" |
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 |
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 |
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": |
Sample output:
PS C:\Users\Administrator> Get-ADUser -Identity simon -Properties AccountNotDelegated |
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 |
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 |
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 " |
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.
Comments (1)