Tag Archives: Trivadis smarp

Kerberos Troubleshooting – A few approaches

It is way too long ago since my last blog post. These were or are busy weeks for me. Any way, I finally found some time to start writing a blog post about a special setup for kerberos authentication of Oracle databases. It is about configuring kerberos authentication for multiple database servers with only one active directory account and corresponding Service Priciple Names (SPN). Additionally there is an challenge, that the keytab file should only be created with ktutil directly on the DB server. Access to a Windows server and use of ktpass.exe is not possible. I did setup a nice test case on a couple of compute instances on Oracle cloud infrastructure. During the verification of the test setup I had to realise that the kerberos authentication does not work as planned. Until now it is not possible to create a keytab file with ktutil that I can use successfully with Active Directory. The same kerberos configuration with a keytab create with ktpass.exe on the AD server does work. But that’s on other story…

The aim of this blog post is to sum up a couple of troubleshooting actions I came across. Kerberos itself is around since a couple of decades. Therefore you will find various documentation, RFC, etc. But it is not always easy to recognise what is still relevant and what not. Mainly because the implementation of Kerberos at both Oracle and Microsoft is not necessarily the same or 100% MIT Kerberos compliant. The fact that there are different versions of Oracle, MS AD and Kerberos makes it even more exciting 🙂

Basics

A basic requirement for Kerberos is the network and time configuration.

  • Problem: okinit does fail with clock skew too great
  • Cause: The systems involved must be synchronous in terms of system time e.g. using a NTP service to configure date / time. If the system times differ to much you will receive this error when using okinit.
  • Solution: Configure proper system times using NTP service. Small time drifts can be covered by setting SQLNET.KERBEROS5_CLOCKSKEW=300 in sqlnet.ora
  • Problem: Miscellaneous errors due to wrong / missing network configuration.
  • Cause: Using CNAME rather A records, no DNS configuration, no revers lookkup etc
  • Solution: Configure proper DNS name resolution for database service as well MS active directory service. Each system must be able to be resolved by name or IP address. Kerberos will look for service principle names based on A records.
oracle@db:/u00/app/oracle/network/admin/ [TDB190S] cd
oracle@db:~/ [TDB190S] nslookup win2016ad.trivadislabs.com
Server:		10.0.1.4
Address:	10.0.1.4#53

Name:	win2016ad.trivadislabs.com
Address: 10.0.1.4

oracle@db:~/ [TDB190S] nslookup 10.0.1.4
4.1.0.10.in-addr.arpa	name = win2016ad.trivadislabs.com.

oracle@db:~/ [TDB190S] nslookup db
Server:		10.0.1.4
Address:	10.0.1.4#53

db.trivadislabs.com	canonical name = ol7db19.trivadislabs.com.
Name:	ol7db19.trivadislabs.com
Address: 10.0.1.6

oracle@db:~/ [TDB190S] nslookup 10.0.1.6
6.1.0.10.in-addr.arpa	name = ol7db19.trivadislabs.com.

Trace and Log Files

Kerberos Trace

As of Oracle 12c release 2 it is possible to enable kerberos tracing by setting KRB5_TRACE to a trace file. This logs the Kerberos calls in the current session.

export KRB5_TRACE=/u00/app/oracle/network/admin/kerberos.trc
oracle@db:~/ [TDB190S] okinit king

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 08-JUN-2020 20:54:16

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

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

A sample output of a kerberos trace file:

oracle@db:~/ [TDB190S] head -10 /u00/app/oracle/network/admin/kerberos.trc
[5645] 1591649656.590082: Getting initial credentials for king@TRIVADISLABS.COM
[5645] 1591649656.590084: Sending unauthenticated request
[5645] 1591649656.590085: Sending request (199 bytes) to TRIVADISLABS.COM
[5645] 1591649656.590086: Resolving hostname ad.trivadislabs.com
[5645] 1591649656.590087: Sending initial UDP request to dgram 10.0.1.4:88
[5645] 1591649656.590088: Received answer (196 bytes) from dgram 10.0.1.4:88
[5645] 1591649656.590089: Sending DNS URI query for _kerberos.TRIVADISLABS.COM.
[5645] 1591649656.590090: No URI records found
[5645] 1591649656.590091: Sending DNS SRV query for _kerberos-master._udp.TRIVADISLABS.COM.
[5645] 1591649656.590092: Sending DNS SRV query for _kerberos-master._tcp.TRIVADISLABS.COM.

Oracle SQLNet tracing

For Kerberos troubleshooting with Oracle SQLNet it is helpful to disable ADR tracing. Not mandatory, but makes life a bit easier. Set DIAG_ADR_ENABLED in sqlnet.ora to OFF.

DIAG_ADR_ENABLED=OFF

Before KRB5_TRACE was available, okinit calls could only be traced with sqlnet.ora and TRACE_LEVEL_OKINIT. See also MOS note 162668.1. The parameter does not make sense when you already use KRB5_TRACE.

TRACE_LEVEL_OKINIT=SUPPORT
TRACE_DIRECTORY_OKINIT=/u00/app/oracle/network/
TRACE_FILE_OKINIT=okinit.trc

For further analysis you usually have to switch on SQLNet Tracing. Don’t even thing about setting an other level than SUPPORT (16). Kerberos calls are only available with the highest level.

TRACE_LEVEL_OKINIT=SUPPORT
TRACE_DIRECTORY_OKINIT=/u00/app/oracle/network/
TRACE_FILE_OKINIT=okinit.trc

Enable tracing for SQLNet clients:

TRACE_LEVEL_CLIENT=SUPPORT
TRACE_DIRECTORY_CLIENT= /u00/app/oracle/network/trc
TRACE_FILE_CLIENT=sqlnet_client.trc

Enable tracing for SQLNet Server:

TRACE_LEVEL_SERVER=SUPPORT
TRACE_DIRECTORY_SERVER= /u00/app/oracle/network/trc
TRACE_FILE_SERVER=sqlnet_server.trc

The errors in the trace files are not always obvious. You can find a few infos and hint in MOS note 185897.1. But most of the time there is no way around searching for the corresponding error or function call in Oracle Support or the search engine of choice.

Network Tracing

The next level is to trace the network calls. Depending on the environment you can directly use Wireshark. But it is much easier to first create a network dump via command line and to analyse it later using Wireshark. I use tcpdump on my OCI environment and download the trace file to my MacBook, where I then use Wireshark.

Get the available interfaces:

sudo tcpdump -D
1.nflog (Linux netfilter log (NFLOG) interface)
2.nfqueue (Linux netfilter queue (NFQUEUE) interface)
3.usbmon1 (USB bus number 1)
4.ens3
5.any (Pseudo-device that captures on all interfaces)
6.lo [Loopback]

Start tracing for interface ens3:

sudo tcpdump -i ens3 -s 65535 -w /tmp/network_okcreate.trc

Keep it running until while testing the kerberos authentication. As soon as done copy the trace file to the client an open it using Wireshark. The following picture does show a trace dump where the kerberos protocol has been selected.

Wireshark sample output

A part of the kerberos packet is encrypted and not visible as you can see in following picture.

Wireshark enc

Kerberos does use the service’s secret key to encrypt these messages. You can import the keytab file into Wireshark to decrypt the messages. For this purpose the keytab file must be specified in Wireshark in the preferences. Click Edit > Preferences > Protocols > KRB5.

Wireshark Preferences

You now see the message content of the packet. This is in particular useful when you have to analyse issues related to ticket size, missing groups etc.

Wireshark decrypted

Conclusion

Unfortunately my Kerberos problem is still not solved. Nevertheless I did get the opportunity to practice a couple of Kerberos tracing methods. The introduction of KRB5_TRACE did simplify tracing a bit, but in most case you still have to use SQLNet or network tracing to find the root cause of you Kerberos problem. A direct solution is unfortunately not always found with tracing. At least you have all the relevant information to search My Oracle Support, open a service request or try your luck at googling for a solution.

Good luck with your Kerberos setup. 😉

References

Some links related to this blog post:

  • Kerberos Troubleshooting Guide [185897.1]
  • Master Note For Kerberos Authentication [1375853.1]
  • How to Trace Unix System Calls [110888.1]
  • Tracing Okinit [162668.1]
  • How to Enable Oracle SQL*Net Client, Server, Listener, Kerberos and External procedure Tracing from Net Manager [395525.1]
  • Requesting kerberos TGT with OKINT errors with okinit: Clock skew too great in 12.1.0.2 [2312008.1]

Oracle Enterprise User Security with multiple ldap.ora

Recently I came across the situation where I have to configure Enterpriser User Security for a database server with multiple databases for different directories. This is quite tricky when using a shared Oracle Home and a central TNS_ADMIN directory for SQLNet configuration. A common TNS_ADMIN also implies the use of only one ldap.ora file. Several ldap servers can be registered in one ldap.ora, but this is primarily used for failover configuration in a high-availability LDAP server architecture. The use of multiple EUS contexts in different LDAP servers is not supported. At least not in one single file. But there are some workarounds.

Oracle does look for the ldap.ora file in a few different places. The following sequence is maintained:

  1. $LDAP_ADMIN environment variable setting
  2. $ORACLE_HOME/ldap/admin directory
  3. $TNS_ADMIN environment variable setting
  4. $ORACLE_HOME/network/admin directory

Notes on this order can be found at different places in the Oracle documentation e.g Oracle Database Database Net Services Reference 19c Overview of Directory Server Usage File, Oracle Database Security Guide 19c About Using a dsi.ora File or in the Oracle Support Note 363283.1 What Is The Search Order For The LDAP.ORA File?

If you don’t trust the documentation, you can also verify the search order with strace. First define a few values for TNS_ADMIN and LDAP_ADMIN to be sure we do not use the default values.

export LDAP_ADMIN="/u01/config"
export TNS_ADMIN="/u00/app/oracle/network/admin"

If we make sure, that LDAP is the first names resolution in sqlnet.ora we could use strace with tsnping.

oracle@eusdb:~/ [TEUS01] grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, EZCONNECT )

oracle@eusdb:~/ [TEUS01] strace -o /u01/config/tnsping.txt tnsping TEUS01

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-NOV-2019 20:47:33

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

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eusdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEUS01)))
OK (30 msec)

Checking the output does show the different directories

oracle@eusdb:~/ [TEUS01] grep -i ldap.ora /u01/config/tnsping.txt
stat("/u01/config/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/ldap/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6be0) = -1 ENOENT (No such file or directory)

As you can see the search order does match the documented search order. But how does that help us? It’s actually relatively simple. To use different LDAP server configuration per database, we do have to make sure, that each database has an individual ldap.ora file. This can be ensured by one the following points:

  • Have a dedicated Oracle Home for each database with an individual ldap.ora file in each Oracle Home
  • Define an environment variable for LDAP_ADMIN for each database.

Both methods have their advantages and disadvantages, but aren’t optimal. As often, several paths lead to the goal. The third option uses a static listener configuration for the database with an ENVS parameter. Unfortunately, the information about ENVS has disappeared in the latest version of the Oracle documentation. At least in the Oracle Database Database Net Services Reference 19c. But you can search for ENVS in the Oracle Database Bookshelf. The parameter ENVS can be used to specify environment variables for the listener to set prior to executing (as a child process) a dedicated server program or an executable specified with the PROGRAM parameter. This allows a static listener entry to be defined for each database, where LDAP_ADMIN or TNS_ADMIN is explicitly set. Below you see an excerpt of listener.ora for ORACLE_SID TEUS01.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = TEUS01 )
       (ORACLE_HOME = /u00/app/oracle/product/19.0.0.0)
       (SID_NAME = TEUS01)
       (ENVS="LDAP_ADMIN=/u00/app/oracle/network/TEUS01")
   )))

The database TEUS01 does use a dedicated ldap.ora. If you add the listener entry just for this DB, you can keep the default ldap.ora in the regular TNS_ADMIN directory. Thus one can apply the following principle:

  • Use a generic ldap.ora configuration in the TNS_ADMIN directory. e.g which is valid for most of the database on this server
  • Add a static listener configuration for each database which does have to use a dedicate Oracle Enterprise User Security configuration and therefor a dedicated ldap.ora.

This method is not only helpful when configuring LDAP server or Oracle Enterprise User Security, but also in other SQLNet use cases. In particular the following:

  • Define TNS_ADMIN and use a dedicate sqlnet.ora configuration for Kerberos authentication. e.g. when you want to use or engineer Kerberos authentication for just one database in an shared environment.
  • Define TNS_ADMIN and use dedicate sqlnet.ora configuration for network encryption.
  • Define LDAP_ADMIN for dedicated ldap.ora or dsi.ora files for engineering centrally managed users.
  • And a couple more…

References

A few links and references related to this blog post

  • Oracle Support Document 728043.1 Use of DIRECTORY_SERVERS in LDAP.ORA & Known issues
  • Oracle Support Document 363283.1 What Is The Search Order For The LDAP.ORA File?
  • Search for ENVS in the Oracle Database Bookshelf
  • Oracle® Database Net Services Reference 11g Release 1 (11.1) ENVS

DOAG Red Stack Magazin – Oracle Unified Directory in Docker

Mid June I wrote an article for the DOAG Red Stack magazin about my work on Oracle Unified Directory in Docker. Just about the same time I did my DOAG SIG Security presentation on the same topic. In the meantime the article has been published in the latest release of the DOAG Red Stack magazin. For this reason I use the opportunity to make the PDF version of the article available on oradba.ch. The article is written in German and available as Trivadis version as well Red Stack version. Although the articles versions differ only in the number of typos and layout.

None of the articles are currently available in English. On request I will write also articles about Oracle Unified Directory in English in the future. However, currently I still have a lot of ideas for more blog posts about database security, Oracle Enterprise User Security and Oracle Unified Directory on my to-do list. And blog posts I do usually write in English… 🙂

Articles in DOAG Red Stack Magazin

A while ago I wrote two articles for the DOAG Red Stack Magazin. In the meantime both articles have been published. For this reason I use the opportunity to make the PDF versions of the articles available on oradba.ch. The articles are written in German and available as Trivadis version as well Red Stack version. Although the articles versions differ only in the number of typos and layout.

None of the articles are currently available in english. On request I will write also articles about Oracle Unified Directory in English in the future. However, currently I still have a lot of ideas for blog posts about database security, enterprise user security and unified directory on my to-do list. And blog posts I usually write in english… 🙂

Oracle Unified Directory 12 Released

Finally end of working day. But while reading some newsletter and mails on my way home, I realised that there will be some work at home. After a long wait, Oracle has finally released Oracle Unified Directory 12c 🙂

A overview of the new features:

  • Improved performance and scalability
  • Support for TNS aliases for Oracle Unified Directory deployments with Oracle Enterprise User Security (EUS) configured
  • Support for TLS 1.2 Protocols and Cipher Suites
  • Password-Based Key Derivation Function 2 Password Storage Schemes
  • ODSM Rebranding
  • Support for new log publishers that are configurable via OUDSM
  • Support for the Upgrade OUD Instance script
  • Support for WebLogic Scripting Tool provisioning commands
  • Support for new log publishers that are configurable via OUDSM
  • Support for Oracle Fusion Middleware configuration tools
  • Support for Oracle WebLogic Server 12.2.1.3
  • Support for Oracle JDK 1.8

See Fusion Middleware Release Notes What’s New in Oracle Identity Management 12c (12.2.1.3.0) for a full list of new features.

Links related to Oracle Unified Directory 12c:

Stay tuned, I’ll definitely write more blog posts on Oracle Unified Directory 12 soon.

GDPR and Database Security Speeches

The new EU GDPR and Database Security in general keeps me busy. I’ve updated the list of speeches and events for the next couple of month. It’s an interesting mix between GDPR, Oracle Database Security and MS SQL Server 2016 security. Depending on the feedback of the Call For Papers for the DOAG Conference and the Oracle OpenWorld there will probably be more. But for now I’ll definitely give a full day training on Oracle Database 12c Security at the Education day on DOAG Conference.

Upcoming events

No planned public appearances

Have you missed an event? In this case check out the download page or blog post categorized with speaking. If possible, I’ll provide all information online?