I’ve recently been investigating Kerberos authentication as a means for providing Windows Single Sign On from Windows clients into an Oracle database servers. Maybe you are wondering one of the following questions.
What on earth does Kerberos authentication have to do with Microsoft Windows?
Don’t you have to pay extra money to Oracle for such functionality?
How to I configure this, are any additional components required?
To answer those questions in order, firstly Kerberos is the default authentication protocol since Windows 2000 (type klist at your Windows command prompt if you’re logged into a Windows domain to see evidence of this).
Secondly, while initially part of the Advanced Security Option, Kerberos Authentication no longer requires this extra cost option.
To answer the final question, read on; the following are the steps I took to enable Kerberos Authentication on my lab environment running on AWS.
First of all the initial setup. The following machines all belong to a domain called patrick.domain.
- Host MSWDC runs Windows Server 2016 is the Domain Controller
- Host WINCLIENT runs Windows Server 2016, and holds Oracle (instant) client binaries
- Host linoradb runs Oracle Linux Server release 7.4, holding an Oracle 11.2.0.4 Enterprise Edition Database. Kerberos client packages are installed
I have created the two domain user accounts on patrick.domain:
- patrickjolliffe – my personal account with which will try to SSO to the database.
- orasvc – a service account that will be used to generate the keytab file used by the database server
First I add the following entries to sqlnet.ora on the database server:
SQLNET.KERBEROS5_CONF=/etc/krb5.conf SQLNET.KERBEROS5_CONF_MIT=true
And update /etc/krb5.conf so that it contains the following:
[libdefaults]
default_realm = PATRICK.DOMAIN
[realms]
PATRICK.DOMAIN = {
kdc = mswdc.patrick.domain
admin_server = mswdc.patrick.domain
}
At this point it’s worth doing some initial checks to validate Kerberos communication between the Oracle Database Server and the Domain Controller.
First let’s obtain, view and then destroy a Kerberos ticket through Linux
Kerberos Stack:
[oracle@linoradb ~]$ kinit patrickjolliffe Password for patrickjolliffe@PATRICK.DOMAIN: [oracle@linoradb admin]$ klist Ticket cache: FILE:/tmp/krb5cc_54321 Default principal: patrickjolliffe@PATRICK.DOMAIN Valid starting Expires Service principal 02/24/2018 06:58:44 02/24/2018 16:58:44 krbtgt/PATRICK.DOMAIN@PATRICK.DOMAIN renew until 02/25/2018 06:58:41 [oracle@linoradb admin]$ kdestroy
Next let’s perform the same task via the Oracle Kerberos stack:
[oracle@linoradb admin]$ okinit patrickjolliffe Kerberos Utilities for Linux: Version 11.2.0.4.0 - Production on 24-FEB-2018 07:01:34 Copyright (c) 1996, 2013 Oracle. All rights reserved. Password for patrickjolliffe@PATRICK.DOMAIN: [oracle@linoradb admin]$ oklist Kerberos Utilities for Linux: Version 11.2.0.4.0 - Production on 24-FEB-2018 07:01:45 Copyright (c) 1996, 2013 Oracle. All rights reserved. Ticket cache: /tmp/krb5cc_54321 Default principal: patrickjolliffe@PATRICK.DOMAIN Valid Starting Expires Principal 24-Feb-2018 07:01:38 24-Feb-2018 15:01:34 krbtgt/PATRICK.DOMAIN@PATRICK.DOMAIN [oracle@linoradb admin]$ okdstry Kerberos Utilities for Linux: Version 11.2.0.4.0 - Production on 24-FEB-2018 07:01:52 Copyright (c) 1996, 2013 Oracle. All rights reserved. [oracle@linoradb admin]$
Next we generate a keytab file on the domain controller for deployment to the database server.
ktpass -princ oracle/linoradb.patrick.domain@PATRICK.DOMAIN -ptype KRB5_NT_PRINCIPAL -crypto RC4-HMAC-NT -mapuser orasvc@patrick.domain -out krb5.keytab -pass
File krb5.keytab is created and I copy to /etc directory on the database server linoradb.
I’ll add a couple of entries to sqlnet.ora on the database server so the Oracle Kerberos Stack can find this keytab, and also provide the service name “oracle” which I specified when generating the keytab.
SQLNET.KERBEROS5_KEYTAB=/etc/krb5.keytab SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
Let’s validate the keytab through the Linux stack:
[oracle@linoradb admin]$ klist -k -e Keytab name: FILE:/etc/krb5.keytab KVNO Principal ---- -------------------------------------------------------------------------- 4 oracle/linoradb.patrick.domain@PATRICK.DOMAIN (arcfour-hmac) [oracle@linoradb admin]$
All good, so let’s do the same check using the Oracle stack:
[oracle@linoradb admin]$ oklist -k Kerberos Utilities for Linux: Version 11.2.0.4.0 - Production on 24-FEB-2018 07:16:14 Copyright (c) 1996, 2013 Oracle. All rights reserved. Service Key Table: /etc/krb5.keytab Ver Timestamp Principal 4 01-Jan-1970 00:00:00 oracle/linoradb.patrick.domain@PATRICK.DOMAIN [oracle@linoradb admin]$
Next I’ll add the following entry to sqlnet.ora on the database server to allow Kerberos in addition to BEQ (local) connection
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
On the database I create a new user that will be authenticated using Kerberos. Note the double quotes and the capitalization.
SQL> create user "PATRICKJOLLIFFE@PATRICK.DOMAIN" identified externally; User created. SQL> GRANT CREATE SESSION TO "PATRICKJOLLIFFE@PATRICK.DOMAIN"; Grant succeeded.
Before we complicate matters by getting the Windows client involved, lets first check Kerberos authentication for a connection locally on the database server (note I create and use a tnsnames entry to make sure we don’t use local BEQ authentication):
[oracle@linoradb ~]$ kinit patrickjolliffe Password for patrickjolliffe@PATRICK.DOMAIN: [oracle@linoradb admin]$ sqlplus /@SSOTEST SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 24 07:49:19 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SHOW USER; USER is "PATRICKJOLLIFFE@PATRICK.DOMAIN" SQL>
That’s working fine so finally lets try the Windows client. I have installed instant client 11.1, 11.2, 12.1 and 12.2 versions and ensured the following settings work with all of them.
For 11.x clients authentication service KERBEROS5 is used, with Credential Cache (CC_NAME) OSMSFT:
For 12.x client 12.x in theory, KERBROS5 service should be used with MSLSA: for the CC_NAME, however due to bug 18895651, KERBEROS5PRE is required with CC_NAME OSMSFT:
Sources on the internet specify “OSMSFT:” or “OSMSFT://”, testing shows that any characters after the “:” are ignored so I provide the simplest possible configuration.
SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5PRE,KERBEROS5) SQLNET.KERBEROS5_CONF_MIT=true SQLNET.KERBEROS5_CONF=C:\Users\patrickjolliffe\krb5.conf SQLNET.KERBEROS5_CC_NAME=OSMSFT:
Testing with instant clients show that KERBEROS5_CONF just needs to point to a valid kerberos configuration file, it can even be empty, however for consistency, I just configure exactly the same as the krb5.conf on the database server.
Below I show connecting via 11.2 instant client. For demonstration purposes I purge Kerberos tickets before connecting, and after successful connection show that a ticket has been obtained for the access to the database:
C:\Users\patrickjolliffe\instantclient_11_1>klist purge Current LogonId is 0:0xa69ed Deleting all tickets: Ticket(s) purged! C:\Users\patrickjolliffe\instantclient_11_1>sqlplus /@ssotest SQL*Plus: Release 11.1.0.7.0 - Production on Sat Feb 24 08:19:15 2018 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "PATRICKJOLLIFFE@PATRICK.DOMAIN" SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\patrickjolliffe\instantclient_11_1>klist Current LogonId is 0:0xa69ed Cached Tickets: (2) #0> Client: patrickjolliffe @ PATRICK.DOMAIN Server: krbtgt/PATRICK.DOMAIN @ PATRICK.DOMAIN KerbTicket Encryption Type: AES-256-CTS-HMAC-SHA1-96 Ticket Flags 0x40e10000 -> forwardable renewable initial pre_authent name_canonicalize Start Time: 2/24/2018 8:19:15 (local) End Time: 2/24/2018 18:19:15 (local) Renew Time: 3/3/2018 8:19:15 (local) Session Key Type: AES-256-CTS-HMAC-SHA1-96 Cache Flags: 0x1 -> PRIMARY Kdc Called: MSWDC.patrick.domain #1> Client: patrickjolliffe @ PATRICK.DOMAIN Server: oracle/linoradb.patrick.domain @ PATRICK.DOMAIN KerbTicket Encryption Type: RSADSI RC4-HMAC(NT) Ticket Flags 0x40a10000 -> forwardable renewable pre_authent name_canonicalize Start Time: 2/24/2018 8:19:15 (local) End Time: 2/24/2018 18:19:15 (local) Renew Time: 3/3/2018 8:19:15 (local) Session Key Type: RSADSI RC4-HMAC(NT) Cache Flags: 0 Kdc Called: MSWDC.patrick.domain C:\Users\patrickjolliffe\instantclient_11_1>
Of course it’s never so easy in practice, there are always complications, debugging and investigation needed to get this working, but when you consider the benefits to a large organization of providing Single Sign-On to Oracle databases, I think it’s time well spent.
The fact that these two conferences run back-to-back seemed a perfect opportunity to experience both, at the expense of a single long-haul flight. Their taking place at amazing locations on the Istrian coast which I had never visited before sealed the deal. I submitted abstracts and was delighted when they both accepted me to speak.
The Slovenian conference was held on the town of Portoroz, and I had heard about some cave systems, Postojna and Skocjan on the way. I had planned to visit both, but on the way to the Postojna, I saw a sign to a cave at Planina so I decided to check that out. The public accessible area only extends a couple of hundred meters inside (much more is accessible with a guide) but it was really cool just being alone in the quiet and pitch darkness.
Postojna Cave is a complete contrast; you take a 20 minute train journey with hundreds of other sightseers deep into an amazing cave system, followed by a 40 minute guided tour, before taking the train back out again. After these two experiences I’d had more then enough of caves, so I decided too skip Skocjan and head direct to the conference venue.
The speakers dinner was held at an fantastic restaurant, Na Burji, and the meal was accompanied by bread baked by the multi-talented Jože Senegačnik. I was thrilled to finally catch up in person with Jure Bratina, the first subscriber to my blog (!).
