Easy setup of Kerberos Authentication for Oracle Databases

I have previously published a couple of blog posts related to Kerberos authentication for databases. In this post, I want to provide a simple, step-by-step tutorial for configuring Kerberos authentication. This tutorial is based on my lab setup within Oracle Cloud Infrastructure (OCI). Within this environment, I run both a database server and a corresponding Windows server configured as an Active Directory server. It should be noted that this tutorial is designed for a basic environment. The configuration must be adapted accordingly for special cases such as clusters, multiple AD forests or domains, OS configuration, etc.

Prerequisites and Requirements

Configuring Kerberos authentication for Oracle databases involves a number of tasks, each of which requires specific permissions. In my lab environment, of course, I have all the necessary permissions. In other environments, certain tasks may need to be delegated to other administrators. Essentially, the following steps need to be performed:

  • DB Server Install software component for Kerberos client tools as root user.
  • AD Server Create a service account in AD as a domain administrator.
  • DB Environment Configure the SQLNet environment as the Oracle user.
  • DB Instance Adjust the init.ora parameters and establish Kerberos accounts.

The subsequent Kerberos configuration relies on the following values:

  • AD Domain / KDC Realm: TRIVADISLABS.COM
  • AD Server / KDC: ad.trivadislabs.com (10.0.1.4)
  • Database Server (FQDN): db23.trivadislabs.com (10.0.1.23)
  • Database Server OS: Oracle Enterprise Linux 8 (Version: 8.8)
  • SamAccountName: db23
  • User Principal Name (UPN): db23.trivadislabs.com
  • Service Principle Name (SPN): oracle/db23.trivadislabs.com
  • Database SID: CDB23B with pluggable database PDB1B and PDB2B

Please note that for different environments and operating systems, the commands may need to be adjusted accordingly.

Step 1 Preparation Database Server

Command line commands to install Kerberos client utilities on OEL8/REL8

sudo dnf install krb5-workstation

Step 2 Service Account Configuration

The following steps should be performed on the AD server by a domain administrator or an administrative account with the required privileges. Essentially, the choice of the tool used for these tasks is not relevant; however, the following section describes only the relevant PowerShell commands.

If the service account already exists, we will delete it first. This step is optional. Nevertheless, it is not a bad idea to start the configuration from scratch. This will certainly make troubleshooting a bit easier.

$Hostname = "db23"
if (!(Get-ADUser -Filter "sAMAccountName -eq '$Hostname'")) {
  Write-Host "INFO : Service Account ($Hostname) does not exist."
} else  {
  Write-Host "INFO : Remove existing Service Account ($Hostname)."
  Remove-ADUser -Identity $Hostname -Confirm
}

The PowerShell command provided below is used to create a service account with the appropriate flags set to support Kerberos encryption using both AES 128 and 256-bit methods. This guarantees that the keytab file can be generated with the necessary encryption types for AES, and authentication will function properly with such a keytab file. Update the service account name, password and UserDN accordingly.

$ServiceName = "db23"
$UserBaseDN  = "cn=Users," + (Get-ADDomain).DistinguishedName
$DNSRoot     = (Get-ADDomain).DNSRoot

# get the credentials for the kerberos service account
$credential = Get-Credential -message 'Kerberos Service Account' -UserName $ServiceName
$ServiceUserParams = @{
    Name                    =   $credential.UserName
    DisplayName             =   $ServiceName
    SamAccountName          =   $ServiceName
    UserPrincipalName       =   "oracle/$ServiceName.$DNSRoot"
    Description             =   "Kerberos Service User for $ServiceName"
    Path                    =   $UserBaseDN
    AccountPassword         =   $credential.Password
    PasswordNeverExpires    =   $true
    Enabled                 =   $true
    KerberosEncryptionType  =   "AES256"
}

# create kerberos service account
New-ADUser @ServiceUserParams

The final step on Windows involves creating a Service Principal Name (SPN) for the service user. If ktpass.exe is used to generate the keytab file, this is done automatically. However, as we are creating the keytab file on the database server using ktutil, we need to create the Service Principal Name (SPN) manually using setspn.

$ServiceName = "db23"
$DNSRoot     = (Get-ADDomain).DNSRoot
setspn $ServiceName -s oracle/$ServiceName.$DNSRoot

Example output of the command.

PS C:\Windows\system32> setspn $ServiceName -s oracle/$ServiceName.$DNSRoot@$Domain
Checking domain DC=trivadislabs,DC=com

Registering ServicePrincipalNames for CN=db23,CN=Users,DC=trivadislabs,DC=com
        oracle/db23.trivadislabs.com
Updated object
PS C:\Windows\system32>

Step 3 Oracle SQLNet Configuration

It is recommended to set up the SQLNet configuration for each database server in the $TNS_ADMIN directory. This is especially important if you are working with multiple Oracle Homes. Otherwise, multiple Kerberos configurations must be maintained for each database server.

Add the following kerberos configuration section to you sqlnet.ora file. Adjust the path to keytab and krb5.conf file accordingly.

# ----------------------------------------------------------------
# Kerberos settings
# ----------------------------------------------------------------
SQLNET.AUTHENTICATION_SERVICES=(beq,tcps,kerberos5pre,kerberos5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.FALLBACK_AUTHENTICATION = TRUE
SQLNET.KERBEROS5_KEYTAB = /u01/app/oracle/network/admin/krb5.keytab
SQLNET.KERBEROS5_CONF = /u01/app/oracle/network/admin/krb5.conf
SQLNET.KERBEROS5_CONF_MIT=TRUE

Create a new Kerberos configuration file, krb5.conf, in your $TNS_ADMIN folder. Adjust the KDC realm, domain, etc., as needed.

# ----------------------------------------------------------------
# OraDBA - Oracle Database Infrastructur and Security, 5630 Muri,
# Switzerland
# ----------------------------------------------------------------
# Name.......: krb5.conf
# Author.....: Stefan Oehrli (oes) stefan.oehrli@oradba.ch
# Editor.....: Stefan Oehrli
# Date.......: 2023.05.04
# Version....: --
# Purpose....: Kerberos Configuration File
# Notes......: --
# Reference..: Oracle Database Security Guide 19c
# ----------------------------------------------------------------
[libdefaults]
forwardable = true
default_realm = TRIVADISLABS.COM
 
[realms]
  TRIVADISLABS.COM = {
    kdc = ad.trivadislabs.com
  }
 
[domain_realm]
.trivadislabs.com = TRIVADISLABS.COM
trivadislabs.com = TRIVADISLABS.COM

It is advisable to restart both the listener and the databases afterward to ensure that the new sqlnet.ora configuration is applied universally. However, this restart does not necessarily need to occur immediately.

Step 4 Create keytab File

Generate a Ticket Granting Ticket (TGT) for the service principal. To confirm the service account and streamline the subsequent steps, obtain a TGT using okinit, an Oracle tool that relies on the previously mentioned sqlnet.ora configuration.

okinit db23@TRIVADISLABS.COM

Example output of the command.

oracle@db23:~/ [CDB23B] okinit db23@TRIVADISLABS.COM

Kerberos Utilities for Linux: Version 23.0.0.0.0 - Beta on 08-NOV-2023 16:17:13

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

Configuration file : /u01/app/oracle/network/admin/krb5.conf.
Password for db23@TRIVADISLABS.COM:
oracle@db23:~/ [CDB23B]

Obtain the Key Version Number (kvno) for the Service Principal. We need the key version number (kvno) for the service principal, which can be retrieved using the kvno utility. You also have the option to explicitly specify the ticket cache using the -c flag. The kvno is crucial for creating the keytab file.

By default, Linux Kerberos tools require a krb5.conf file in /etc. Since we do not intend to configure Kerberos for Linux authentication, we can specify the krb5.conf file from TNS_ADMIN by using the environment variable KRB5_CONFIG.

export KRB5_CONFIG=$TNS_ADMIN/krb5.conf
kvno -c /tmp/krb5cc_1000 db23@TRIVADISLABS.COM

Example output of the command.

oracle@db23:~/ [rdbms] export KRB5_CONFIG=$TNS_ADMIN/krb5.conf
oracle@db23:~/ [rdbms] kvno -c /tmp/krb5cc_1000 db23@TRIVADISLABS.COM
db23@TRIVADISLABS.COM: kvno = 2

We now create a keytab file with ktutil. The tool must be used interactively to read, create and write the keytab file. See the ktutil man page for full usage. In the following example, we use the aes256-cts-hmac-sha1-96 encryption type. Update the addent command accordingly with the correct kvno and encryption type. Optionally, you can add multiple encryption types to a keytab by running addent multiple times. The list of encryption types can be found at Kerberos Parameters. Make sure to use encryption types which are supported by your KDC.

oracle@db23:~/ [rdbms19] export KRB5_CONFIG=$TNS_ADMIN/krb5.conf
oracle@db23:~/ [rdbms19] mv $TNS_ADMIN/krb5.keytab $TNS_ADMIN/krb5.keytab.orig
oracle@db23:~/ [rdbms19] ktutil
ktutil:  addent -password -p oracle/db23.trivadislabs.com@TRIVADISLABS.COM -k 2 -e aes256-cts-hmac-sha1-96
Password for oracle/db23.trivadislabs.com@TRIVADISLABS.COM:
ktutil:  list -e
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2 oracle/db23.trivadislabs.com@TRIVADISLABS.COM (aes256-cts-hmac-sha1-96)
ktutil:  wkt /u01/app/oracle/network/admin/krb5.keytab
ktutil:  q

Verify the new keytab file using oklist.

oracle@db23:~/ [rdbms] oklist -e -k

Kerberos Utilities for Linux: Version 23.0.0.0.0 - Beta on 08-NOV-2023 16:18:31

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

Configuration file : /u01/app/oracle/network/admin/krb5.conf.
Keytab name: FILE:/u01/app/oracle/network/admin/krb5.keytab
KVNO Principal
---- --------------------------------------------------------------------------
   2 oracle/db23.trivadislabs.com@TRIVADISLABS.COM (AES-256 CTS mode with 96-bit SHA-1 HMAC)

Step 5 Database Configuration

To enable Kerberos database authentication, it is necessary to modify the init.ora parameters os_authent_prefix=” and for system older als 23c also remote_os_authent=FALSE in each database. Both parameters require a database restart.

ALTER SYSTEM SET os_authent_prefix='' SCOPE=spfile;

Furthermore, it is essential to create or modify the appropriate database users for Kerberos authentication. In the following example, we will create a user named King and grant them the necessary privileges to establish a connection to the database and select information from V$SESSION.

ALTER SESSION SET CONTAINER=pdb1b;
CREATE USER king IDENTIFIED EXTERNALLY AS 'king@TRIVADISLABS.COM';
GRANT create session TO king;
GRANT SELECT ON v_$session TO king;

Step 6 Test Kerberos Authentication

We initially create a Ticket Granting Ticket (TGT) for a specific user, such as King.

okinit king

Example output of the command.

oracle@db23:~/ [CDB23B] okinit king

Kerberos Utilities for Linux: Version 23.0.0.0.0 - Beta on 03-NOV-2023 15:47:27

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

Configuration file : /u01/app/oracle/network/admin/krb5.conf.
Password for king@TRIVADISLABS.COM:

Now, we can connect directly to the PDB1B database using SQL*Plus without specifying a username and password.

oracle@db23:~/ [CDB23B] sqlplus /@pdb1b

SQL*Plus: Release 23.0.0.0.0 - Beta on Wed Nov 8 16:20:43 2023
Version 23.2.0.0.0

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

Connected to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta
Version 23.2.0.0.0

SQL>

By querying the system context USERENV, you can find relevant information about the user, authentication method and more.

SET linesize 160 pagesize 200
COL db_user FOR A20
COL auth_method FOR A20
COL auth_id FOR A40

SELECT 
   sys_context('userenv','SESSION_USER') db_user,
   sys_context('userenv','AUTHENTICATION_METHOD') auth_method,
   sys_context('userenv','AUTHENTICATED_IDENTITY') auth_id
FROM dual;

Example output of the query.

SQL> SELECT
  2     sys_context('userenv','SESSION_USER') db_user,
  3     sys_context('userenv','AUTHENTICATION_METHOD') auth_method,
  4     sys_context('userenv','AUTHENTICATED_IDENTITY') auth_id
  5  FROM dual;

DB_USER 	     AUTH_METHOD	  AUTH_ID
-------------------- -------------------- ----------------------------------------
KING		     KERBEROS		  king@TRIVADISLABS.COM

Tips and Best Practices

Once Kerberos is up and running, the experience is generally smooth. However, it’s important to consider these best practices:

  • Start Simple: Begin with an uncomplicated setup. A multi-domain Oracle Maximum Availability Architecture (MAA) environment isn’t necessary for initial implementation.
  • Ensure Basic Infrastructure: Verify that the foundational configurations are in place, such as network access, open ports, proper name resolution, and synchronized time settings.
  • Avoid Ad-hoc Tweaks: If issues arise, resist the urge to make random changes. Certain details may be cached, complicating the troubleshooting process. Instead, systematically revert to the last working configuration and proceed cautiously.
  • Regenerate Keytab Files When Needed: Many issues can be resolved with a fresh and correctly configured keytab file. Don’t hesitate to recreate it.
  • Adopt Secure Practices Early: Begin with robust security measures, such as employing strong encryption algorithms and setting secure passwords. The mindset of ‘I’ll secure it later‘ often leads to vulnerabilities. 🤪
  • Document Troubleshooting Steps: Keeping a record of the steps and solutions can be invaluable for future reference.
  • Distinguish Between OS and DB Authentication: It’s critical to understand the differences between OS-level Kerberos authentication and Oracle database-specific Kerberos authentication. Do not confuse the two.

Common Errors and Troubleshooting

The Oracle Support Document 185897.1 Kerberos Troubleshooting Guide provides a comprehensive overview of potential issues you might encounter with database Kerberos authentication. Additionally, my blog post Kerberos Troubleshooting – A few approaches outlines practical troubleshooting examples. When addressing Kerberos authentication problems, enabling Oracle SQLNet Tracing is often indispensable; without it, you might find yourself groping in the dark.

Below a couple of common Database Kerberos Authentication issues

  • Incorrect Keytab File: The keytab file may be outdated or incorrectly configured.
  • Service Principal Name (SPN) Mismatches: The SPN registered in Active Directory doesn’t match the one the Oracle server is expecting.
  • Clock Skew: There’s too much time difference between the client and server machines, or between the server and the domain controller.
  • DNS Resolution Problems: The client or server may be unable to resolve the domain names to their IP addresses.
  • Expired Credentials: User credentials or service tickets may have expired.
  • Kerberos Realm Confusion: Incorrect configuration of the Kerberos realm can lead to failed authentication.
  • Version Mismatch: The version of Kerberos on the client does not match with what the Oracle Database expects.
  • Access Denied: Improper permissions set for the Oracle service account within Active Directory.
  • SQLNet Configuration: sqlnet.ora or krb5.conf file may have incorrect entries or lack necessary Kerberos parameters.
  • Kerberos Ticket Issues: Problems obtaining or using a valid Kerberos ticket due to cache issues or misconfigurations.
  • Network Issues: Latency or connectivity problems can prevent proper communication between the client, server, and Kerberos Key Distribution Center (KDC).
  • Case Sensitivity: Kerberos is case-sensitive; mismatches in case between configurations can cause failures.
  • Client Configuration Errors: The Kerberos client may not be configured correctly on the user’s machine, leading to authentication errors.
  • Multi-Domain Environments: Additional complexities when the database and users are in different domains or forests.
  • KVNO Mismatch: Discrepancies between the KVNO in the keytab file and the KVNO for the service principal in the KDC can result in authentication failures. This often happens after a password change for a service account where the keytab file was not simultaneously updated

Unfortunately, I keep running into a new problem every time I try to configure Kerberos. When writing this blog post, it took me a relatively long time to figure out that the User Principal Name (UPN) of my service account was not set. The error was of course an ORA-01017 and ORA-12631, although this can easily be checked with the following LDAP query.

ldapsearch -h ad.trivadislabs.com -p 389 \
-D king@TRIVADISLABS.COM -q \
-b "cn=Users,dc=trivadislabs,dc=com" \
-s sub "(sAMAccountName=db23)" \
userPrincipalName servicePrincipalName

Conclusion

As you can see, it is clear that setting up and configuring Kerberos is a straightforward process. It provides a relatively simple way to increase the security of database accounts and at the same time significantly improve the user-friendliness of single sign-on (SSO). However, the devil is in the detail. In complex Active Directory domains or Key Distribution Centers (KDCs), additional configuration, such as setting up domain trust, can involve a certain amount of complexity. Furthermore, not all tools and clients are Kerberos-capable out of the box. Therefore, it is important to understand the database users and their access methods. Even with the integration of Kerberos, a well thought-out user and role concept remains essential. However, Kerberos integrates seamlessly with Oracle’s Centrally Managed Users (CMU) and can coexist with other authentication methods, such as password-based authentication. Why don’t you start by configuring Kerberos for your DBAs and power users?

Additional Resources

Some links and references related to this topic.