Authenticate Oracle 12c database users against Active Directory

Following is tested with Oracle 12.1 on Linux 6 (on Exadata) and a Windows 10 client.

“Yet another blog on how to authenticate database users against Active Director using Kerberos…”

I have read and tried a view blogs on how to get this done, but somehow I have found them a bit limited because they talk about a simple configuration with one database on one host. When you have to deal with multiple hosts and multiple databases per host, you need to take some things into account.

Lets start with some explanations, a walk through is below that.

1. Create a different AD account per database host

Per database host, you need to create a different account in AD which will be used for the Kerberos service principle to authenticate. Because the last keytab file generated with ktpass (on the AD server) for an AD account is valid, that account can not be reused for other Kerberos services even for different hosts!

This was hinted in a blog-post by ‘ateam-oracle‘, but when I read it, it looked liked this was only for a host’s AD account and ‘Kerberos service’ combination, but it’s not. I have tried to combine or add more ‘Kerberos-service/host@domain’ principles in a single keytab file for a single AD account, which is technically possible (you can specify an -in keytab and ‘stack’ them). Unfortunately, it will only work for the last combination, because of ‘versioning’ of the keytab file.

If you have three servers and you want to add them to a single AD account, the first combination will be v1, the second v2, the third v3. Your keytab will be v3 and it will fail for the v1 and v2 ‘AD registered combination’. When you reuse this AD account for different hosts, the last combination is the only valid one.

If anyone has a solution for using only one AD account, maybe generating the keytab with Linux tools (ktutil), feel free to comment.

2. Client: set the correct SQLNET.KERBEROS5_CC_NAME value

• The value ‘MSLSA:’ (with colon) is the Oracle 12c setting, if you want to read the native (in memory) credential cache from Windows. In Oracle documentation you will find the setting without colon, which is not correct.
• The ‘OSMSFT://’ (with colon AND slashes), is the pre-12c setting.
• ‘OSMEMORY’ is not a valid setting, it’s a ‘document placeholder’ for ‘add your memory setting here’, like ‘MSLSA:’.
• Anything else in here will result in a file, so ‘OSMEMORY’ will result in the following file: ‘%USERPROFILE%\OSMEMORY’.

BUG: the tool `oklist` still needs the ‘OSMSFT://’ setting(!), even if you use 12c: [Doc ID 1678877.1]. If you want to test and show your Kerberos cache from memory with the `oklist` command, change the parameter…

3. Server: to set or not set the SQLNET.KERBEROS5_CC_NAME value…

(long story… I’ll try to explain the bug here…)

When you don’t set this, Oracle initially creates a ‘/tmp/krb5cc_’ file instead of one with without an appended oracle_uid: ‘/tmp/krb5cc_<oracle_uid>’. After an `okinit` it creates another file ‘krb5cc_54321′ which is correct. If there is only a file without oracle_uid, the fallback of authentication does not work (see 4.)

One solution is naming the credential cache file with SQLNET.KERBEROS5_CC_NAME, but then this will only work for situations where the Oracle database is the only process using Kerberos on the server. If you also have separate Linux users who also use SSO for the Oracle software on this machine, Oracle is not able to separate their tickets in different caches. It does not append the credential cache file with their corresponding uid.

Another solution, which works better in multiple user configurations, is renaming the initial ’empty’ ‘/tmp/krb5cc_’ file to ‘/tmp/krb5cc_<oracle_uid>’. The default principle will be the ‘Oracle AD user’ defined in AD (see walk through, step 1). One can also initiate an `okinit` and use the Oracle AD user as credential or use `kinit` with the keytab without password: `kinit krboraaccount1 -kt /u01/app/oracle/krb/keytab_hostname.serverdomain.local` (see 0. of walk through). If you use the last one in a cron-job, you refresh Oracle’s credential cache and avoid this bug.

We found that creating this file with other AD users works as well, but then the default credential in the file will be of the other AD user. What this means is that anyone who now logs on with the Linux Oracle OS user and then into Oracle via SSO, he or she will be ‘impersonated’ as the last user who successfully authenticated with the `okinit` command… something you don’t want!

The Oracle uid can be found by issuing `id oracle` on the command line. If this returns something like ‘uid=54321(oracle)’, the ‘/tmp/krb5cc_<oracle_uid>’ should be renamed to ‘/tmp/krb5cc_54321‘.

Unfortunately, the catch here is: don’t issue an`okdstry`, because the credential cache will be cleared by deleting these files. You’ll end up in a erroneous situation again!

Bug summary

Without an credential file with an oracle_uid, when an OS process (rman clone for instance) or `sqlplus ad_user/@tns` is issued, only the ‘/tmp/krb5cc_’ is exists, not the ‘/tmp/krb5cc_<oracle_uid>’. This is the error and logging in fails with [ORA-12641: Authentication service failed to initialize]. The thing is, the Kerberos authentication must technically work! (see SQLNET.FALLBACK_AUTHENTICATION remarks). Due to this bug it doesn’t. I’m not sure if this is a bug in Oracle or in the Linux Kerberos libraries.

4. Do use the SQLNET.FALLBACK_AUTHENTICATION

Your Kerberos configuration MUST work! The SQLNET.FALLBACK_AUTHENTICATION only works when you ‘successfully’ did not authenticate against AD. If technically connecting to AD for whatever reason does not work, the fallback setting will not fallback to ‘database authentication’. Make sure your Kerberos configuration is correct.

The fallback parameter must be present on both the client and server side. When absent on the server side, database links are also checked against AD, which will fail! So a fallback to database authentication is necessary. When absent on the client side, we have seen the fallback is necessary when your connection login is handed over from a Data Guard standby database to a primary database.

5. Update your C:\Windows\System32\drivers\etc\services file

Missing from quite some blogs, but one needs to update the ‘C:\Windows\System32\drivers\etc\services’ file to include an extra alias for the Kerberos service port:

# <service name>  <port number>/<protocol>  [aliases...]               [#<comment>]
kerberos          88/tcp                    kerberos5 krb5 kerberos-sec #Kerberos
kerberos          88/udp                    kerberos5 krb5 kerberos-sec #kerberos

Oracle probably references to the ‘Kerberos5’ alias. It has to be the first entry in the alias list as well. Maybe it’s new to 12c or it had disappeared in Windows 10.

6. Try to use the following order

This way you can only break the existing databases in the updating sqlora.net step.

Server:

1. create AD account (one per server/host)
2. define a Kerberos service name for the linux Kerberos host (input for 3.)
3. update AD and create keytab file (input for 4.)
4. update the server’s Kerberos configuration file (/etc/krb5.ini)
5. update the server’s sqlnet.ora

Client:

1. update the clients’s Windows services configuration file (x:\xxx\krb5.conf)
2. update the clients’s Kerberos configuration file (x:\xxx\krb5.conf)
3. update the clients’s sqlnet.ora

---

Walkthrough

0. Used in examples

• using AES256-SHA1 (=AES256-CTS-HMAC-SHA1-96) as only encryption;
• ‘hostname.serverdomain.local‘ as Oracle database host;
• ‘orakrbsrv‘ as Linux Kerberos service name;
• ‘krboraaccount@ADDOMAIN.LOCAL‘ as AD account Oracle will use to check authentication;
• keytab file name can be anything, it doesn’t have to contain the hostname. As long as you reference it as SQLNET.KERBEROS5_KEYTAB value. I have used: /u01/app/oracle/krb/keytab_hostname.serverdomain.local;

1. create AD account

One per server/host, for example: ‘krboraaccount1@ADDOMAIN.LOCAL’. This will be the Oracle AD mapped user.

• [User cannot change password]
• [Password never expires]
• [This account supports Kerberos AES 256 bit encryption]
• [Do not require Kerberis preauthentication]

2. define a Kerberos service name for the linux Kerberos host (input for 3.)

One can use any name here, as long as you reference to it in the principle name of the ktpass command. This can be the same for every host, because in combination of the hostname it will be unique.

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orakrbsrv

3. update AD and create keytab file (input for 5.)

ktpass -princ orakrbsrv/hostname.serverdomain.local@ADDOMAIN.LOCAL -ptype KRB5_NT_PRINCIPAL /
 -crypto AES256-SHA1 -pass somepassword -mapuser krboraaccount1@ADDOMAIN.LOCAL /
 -out c:\windows\temp\keytab_hostname.serverdomain.local

• -ptype KRB5_NT_PRINCIPAL has to be used.
• Run this command on the AD server (createing keytab on Linux is possible, but I have not explored this).
• setting the SPN afterwards with ‘setspn -A ‘ does not seems to be necessary.

4. update the server’s Kerberos configuration file (input for 5.)

Located in ‘/etc/krb5.conf’.

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = ADDOMAIN.LOCAL
 dns_lookup_realm = true
 dns_lookup_kdc = true
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = yes

[realms]
 ADDOMAIN.LOCAL = {
 kdc = addomain.local:88
 admin_server = addomain.local:749
 default_domain = ADDOMAIN.LOCAL
 }

[domain_realm]
 .serverdomain.local = ADDOMAIN.LOCAL
 serverdomain.local = ADDOMAIN.LOCAL

• The [domain_realm] is a ‘mapping’ between the domain of the server host and the AD domain. This can be different, most of the time it’s the same.
• I have seen configurations with only the [domain_realm], but I haven’t tested this.
• Java 8 might need ‘allow_weak_crypto = true’ in the [libdefaults] section when you get an ‘Unable to obtain Principal Name for authentication‘ error.

5. update the server’s sqlnet.ora

# Allow Kerberos5 as authentication method
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)

# Fallback to user/pass authentication
SQLNET.FALLBACK_AUTHENTICATION=TRUE

# Name the Credential Cache file
SQLNET.KERBEROS5_CC_NAME=/tmp/krb5.cc

# Location of the OS level kerberos configuration file
SQLNET.KERBEROS5_CONF=/etc/krb5.conf

# Parse the krb5.conf file based on the MIT Kerberos configuration format
SQLNET.KERBEROS5_CONF_MIT=TRUE

# A unique service name. It does not need to correspond to the DB Service name
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=orakrbsrv

# The absolute location of the keytab file
SQLNET.KERBEROS5_KEYTAB=/u01/app/oracle/krb/keytab_hostname.serverdomain.local

• Use SQLNET.FALLBACK_AUTHENTICATION=TRUE or database links will fail.
• Choice: Name the SQLNET.KERBEROS5_CC_NAME file, see explanation 3 above.
  
• Confirm to the SQLNET.KERBEROS5_CONF_MIT (can not be left out).

6. update the clients’s Windows services configuration file

Located in [C:\Windows\System32\drivers\etc\services]

# <service name>  <port number>/<protocol>  [aliases...]                  [#<comment>]
kerberos          88/tcp                    kerberos5 krb5 kerberos-sec    #Kerberos
kerberos          88/udp                    kerberos5 krb5 kerberos-sec    #kerberos

7. update the clients’s Kerberos configuration file

Choose your location [Y:\xxx\krb5.ini], default is %USERPROFILE%\krb5.ini (if I’m not mistaken).

[libdefaults]
 default_realm = ADDOMAIN.LOCAL

[realms]
 ADDOMAIN.LOCAL = {
 kdc = ADDOMAIN.LOCAL
 }

[domain_realm]
 clientdomain.local = ADDOMAIN.LOCAL
 .clientdomain.local = ADDOMAIN.LOCAL

• The [domain_realm] is a ‘mapping’ between the domain of the client host and the AD domain. This can be different, most of the time it’s the same.

8. update the clients’s sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.KERBEROS5_CONF=Y:\xxx\krb5.ini
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.KERBEROS5_CC_NAME=MSLSA:

• Use SQLNET.FALLBACK_AUTHENTICATION=TRUE or data guard handover can fail and it’s needed if you don’t specify ‘all’ as SQLNET.AUTHENTICATION_SERVICES.
• Use SQLNET.KERBEROS5_CC_NAME=MSLSA: for 12c, use ‘OSMSFT://’ 11c and earlier (and issuing the ‘oklist’ command, even it is 12c).
• Confirm to the SQLNET.KERBEROS5_CONF_MIT (can not be left out).

9. create an Oracle user in the database to be identified externally

create user me_in_oracle identified externally as 'me_in_ad@ADDOMAIN.LOCAL';
grant create session to me_in_oracle;
-- or
create user "ME_IN_AD@ADDOMAIN.LOCAL" identified externally;
grant create session to "ME_IN_AD@ADDOMAIN.LOCAL";

It’s not necessary that both user have same name in the first option. In the second option, all must be in capital letters!

10. test on server, get ticket manually and connect…

$ okinit me_in_ad
Kerberos Utilities for Linux: Version 12.1.0.2.0 - Production (...)
Password for me_in_ad@ADDOMAIN.LOCAL:

$ oklist
Kerberos Utilities for Linux: Version 12.1.0.2.0 - Production (...)

Ticket cache: /tmp/krb5.cc
Default principal: me_in_ad@ADDOMAIN.LOCAL
(...)
$ sqlplus /@TNS_ALIAS
SQL*Plus: Release 12.1.0.2.0 Production (...)

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
(...)

This is testing on the server side with manually getting a ticket from AD. This is not necessary when connecting from a client. It shows Oracle is able to communicate successful with AD.

11. test and connect on the client

– SQL*Plus

C:\Users\me_in_ad>echo %USERNAME%
me_in_ad

C:\Users\me_in_ad>sqlplus /@TNS_ALIAS
SQL> SHOW USER
USER is "ME_IN_ORACLE"

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
ME_IN_ORACLE

SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;
me_in_ad@ADDOMAIN.LOCAL

– SQL Developer

Use an updated version (latest one now: 17.3.1, October 17, 2017). In the ‘Preferences…’ navigate to ‘Database’ > ‘Advanced’ and the point to your Oracle Client Home where the sqlnet.ora.

SSO only works with the OCI/thick driver: Leave Kerberos Thin Config all blank. Don’t fill in a Username and Password, but check Kerberos Authentication on the connection.

AD credentials work with Thin, but its not SSO: Leave Kerberos Thin Config all blank. Fill in your AD Username, Password and check Kerberos Authentication on the connection.

When you want to use SSO with the thin driver, one must fill in the Kerberos Thin Config and manually create a credential cache file with `okinit` from the client. This defeats the idea of SSO. There is no intention yet on making SQL Developer using SSO with the thin client: [Doc ID 1609359.1].

– Toad for Oracle

Use the username ‘EXTERNAL’ and Toad will read your Windows Native Credential Cache.

Happy connecting…