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.

Next are some things you should take in to account, a walkthrough 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. 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 1: 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…

BUG 2: name your Kerberos5_CC_NAME file on the server! When you don’t specify it, an Oracle will create a default file in /tmp with an incorrect name! Oracle should name it: krb5cc_<user_id>, like: ‘krb5cc_1001’ , but it initially creates ‘krb5cc_’ without an id. After an `okinit` it creates another file ‘krb5cc_1001’ which is correct. Usually one uses this command to test Oracle/Kerberos so the ‘krb5cc_1001’ is present ‘forever’.

When you `okdstry` the credential cache (simulating a AD logoff), Oracle will delete both files again.

The bug is, that without any files, when an OS process (rman clone for instance) or `sqlplus ad_user/@tns` is issued, only the ‘krb5cc_’ is created. This is the wrong naming and logging in fails with [ORA-12641: Authentication service failed to initialize], even if you don’t use AD credentials. The thing is, the Kerberos authentication must technically work! (see SQLNET.FALLBACK_AUTHENTICATION remarks). Due to this bug it doesn’t.

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

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

5. 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;

1. create AD account

One per server/host, for example: ‘krboraaccount1@ADDOMAIN.LOCAL’

  • [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.
  • Name the SQLNET.KERBEROS5_CC_NAME file.
  • 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…

Tagged , , . Bookmark the permalink.

Leave a Reply

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