If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost
Landscape for my setup
- One PC with Windows (PC01.EXAMPLE.COM)
- One DB Server with Unix (DBSRV01.EXAMPLE.COM)
- One Microsoft Active Directory Server (MSAD01.EXAMPLE.COM)
Username
- user01
Tools for troubleshooting
- PC : Microsoft Network Monitor
- Unix : on AIX, tcpdump
- On AD : ???
My Software
- PC : Oracle Client 11.2.0.3
- Unix : Oracle Server 11.2.0.4
- On AD : MSAD 2008
There are a lot of buggy releases (it makes me think Oracle does not test Kerberos properly)
Some hits :
11.2.0.2 : Bug 12635212 : TCP/88 is not working.
12.1.0.1 : Bug 17890382 : ZTK return value: 6
Also your PC must be using Kerberos (which is the case if you login to your Active Directory). The DB server needs some client libraries (krb5.client.rte on AIX).
System changes:
- PC : edit etc\services
- Unix : edit /etc/services
- On AD : disable pre-authentication
this option has to be set for every user, under user -> user01 -> Properties -> Account -> Account options -> Select “Do not require Kerberos preauthentication”
C:\> find " 88" %SystemRoot%\system32\drivers\etc\services
---------- C:\WINDOWS\SYSTEM32\DRIVERS\ETC\SERVICES
kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos
kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos
$ grep -w 88 /etc/services
kerberos 88/tcp kerberos5 krb5 # Kerberos
kerberos 88/udp kerberos5 krb5 # Kerberos
Those are quite painful. There is a bug 2458563 fixed in 8.1.7.4.99 (whatever it means) that should have addressed pre-authentication. still required on 11.2.0.4 apparently no longer needed with a 11.2.0.4 client
Editing etc/services to add the “kerberos5” string means you need admin rights on Windows and root on Unix.
Okay, now you need to create the config files. You probably should use Kerberos v5 MIT.
Kerberos5 was released in 1993, not sure why you want to use something older than this… Okay, for kerberos4, released in the 80’s, you would need on the PC and on the DB Server something like
EXAMPLE.COM
EXAMPLE.COM MSAD01.EXAMPLE.COM admin server
Otherwise you need to specify : sqlnet.kerberos5_conf_mit=true
I have an open SR to support regarding : 12c upgrade guide
The SQLNET.KERBEROS5_CONF_MIT networking parameter is no longer supported in sqlnet.ora
Okay, here the configuration files
krb5.conf on the database server and on the PC
[libdefaults]
default_realm = EXAMPLE.COM
[realms]
EXAMPLE.COM = {
kdc = MSAD01.EXAMPLE.COM
}
[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM
The config file location (kerberos4 or 5) is specified by sqlnet.kerberos5_conf.
There should be a technical account for your db server created on the MSAD that matched your db server.
On Active Directory, you create a user (e.g. : oracle_DBSRV01) who must not change password on first login. Then you extract the keytab with ktpass
ktpass.exe -princ oracle/[email protected] -mapuser oracle_DBSRV01 -crypto all -pass password -out c:\dbsrv01.keytab
As an Oracle DBA, you will probably ask this to another team who is used to Kerberos.
To verify it, you can list the content of the keytab
$ $ORACLE_HOME/bin/oklist -k dbsrv01.keytab
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 12:45:08
Copyright (c) 1996, 2013 Oracle. All rights reserved.
Service Key Table: dbsrv01.keytab
Ver Timestamp Principal
4 01-Jan-1970 01:00:00 oracle/[email protected]
The principal name must match your full qualified host name. You cannot use a DNS alias.
On your PC check for the login name :
PS> $o = New-Object DirectoryServices.DirectorySearcher;
$o.Filter = 'servicePrincipalName=oracle/dbsrv01.example.com';
$o.FindOne().properties.samaccountname
oracle_DBSRV01
And you can verify the principal of that user
C:\> setspn -L oracle_DBSRV01
Registered ServicePrincipalNames for CN=oracle_DBSRV01,OU=MiscUsers,DC=example,DC=com
:
oracle/dbsrv01.example.com
Now you’ve got your keytab, this must be on the DB Server only (and must be readable for oracle). The location is specified by SQLNET.KERBEROS5_KEYTAB.
Next step is the credential cache (CC) parameter. On your PC with the Oracle 11g client, you must set sqlnet.kerberos5_cc_name to OSMSFT://
On the server it is not neeeded. On Oracle 12c client, you must set it MSLSA:, but due to bug 17890382, it is not working yet (metalink comment : We will have to wait […] bugs are under investigation).
But before you start, you may want to test the ticket.
On Unix, you can get the ticket with kinit and check it with klist. You need to have your configuration in /etc/krb5/krb5.conf (OS Dependent). Do not forget to destroy your credential cache with kdestroy / okdstry while testing
For the DB Server
$ /usr/krb5/bin/kinit -k -t dbsrv01.keytab oracle/[email protected]
$ /usr/krb5/bin/klist
Ticket cache: FILE:/var/krb5/security/creds/krb5cc_99
Default principal: oracle/[email protected]
Valid starting Expires Service principal
01/16/14 17:41:26 01/17/14 03:41:26 krbtgt/[email protected]
Renew until 01/17/14 17:41:26
$ /usr/krb5/bin/kinit [email protected]
Password for [email protected]:
Ticket cache: FILE:/var/krb5/security/creds/krb5cc_99
Default principal: [email protected]
Valid starting Expires Service principal
01/16/14 17:35:57 01/17/14 03:35:57 krbtgt/[email protected]
Renew until 01/17/14 17:35:57
Now we can test the okinit (oracle kinit) tool to do the same. There are some specific trace options that could be set
Here the complete sqlnet.ora on the server
DIAG_ADR_ENABLED = OFF
TRACE_DIRECTORY_OKINIT = /var/opt/oracle/krb/cc
TRACE_FILE_OKINIT = okinit
TRACE_LEVEL_OKINIT = SUPPORT
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.AUTHENTICATION_SERVICES= (BEQ,KERBEROS5)
SQLNET.KERBEROS5_CC_NAME = /var/opt/oracle/krb/cc/krb5cc_99
SQLNET.KERBEROS5_CONF = /var/opt/oracle/krb/krb5.conf
SQLNET.KERBEROS5_KEYTAB = /var/opt/oracle/krb/dbsrv01.keytab
sqlnet.kerberos5_conf_mit=true
Note the authentication service. If kerberos is not working, you may no longer be able to log / as sysdba and also some db links may no longer work.
Also note SQLNET.AUTHENTICATION_KERBEROS5_SERVICE, which is the prefix of your principal, oracle/[email protected]
Then we use okinit as we did for kinit
$ $ORACLE_HOME/bin/okinit -k -t dbsrv01.keytab oracle/[email protected]
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:52:21
Copyright (c) 1996, 2013 Oracle. All rights reserved.
$ $ORACLE_HOME/bin/oklist
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:55:27
Copyright (c) 1996, 2013 Oracle. All rights reserved.
Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: oracle/[email protected]
Valid Starting Expires Principal
16-Jan-2014 17:54:30 17-Jan-2014 01:54:30 krbtgt/[email protected]
$ $ORACLE_HOME/bin/okinit [email protected]
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:02
Copyright (c) 1996, 2013 Oracle. All rights reserved.
Password for [email protected]:
$ $ORACLE_HOME/bin/oklist
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:12
Copyright (c) 1996, 2013 Oracle. All rights reserved.
Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: [email protected]
Valid Starting Expires Principal
16-Jan-2014 18:15:06 17-Jan-2014 02:15:02 krbtgt/[email protected]
In case of error, grep for “k5” in the trace file.
Do the same on the PC01. To test okinit, temporary change the CC cache to a file
sqlnet.authentication_services=(kerberos5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=C:\oracle\krb5
TRACE_UNIQUE_CLIENT=on
TRACE_FILE_CLIENT=kerb_client
sqlnet.kerberos5_conf_mit=true
sqlnet.kerberos5_conf=C:\oracle\krb5\krb5.conf
#sqlnet.kerberos5_cc_name=OSMSFT://
sqlnet.kerberos5_cc_name=C:\oracle\krb5\krbcc
and test as in Unix.
Some errors will be easier to find out with a network sniffer on port 88
With AIX
tcpdump -v -v port 88
On Windows
Start -> Microsoft Network Monitor -> Microsoft Network Monitor -> New capture -> Display filter
Frame.Ethernet.Ipv4.TCP.Port == 88 or Frame.Ethernet.Ipv4.UDP.Port == 88
-> Apply -> Start
If you for instance only see UDP packets but no TCP packets, you probably hit bug 12635212.
I still have some KDC_ERR_S_PRINCIPAL_UNKNOWN errors with my working setup, don’t worry about those.
Ok, now that okinit works, the next step is to log in the database.
Check the adapters on the db server
$ adapters
Installed Oracle Net transport protocols are:
IPC
BEQ
TCP/IP
SSL
RAW
Installed Oracle Net naming methods are:
Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
Oracle Names Server Naming
NIS Naming
Installed Oracle Advanced Security options are:
RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication
Create the user on the database db01 on the server dbsrv01. You need to have OS_AUTHENT_PREFIX=”” and do not set REMOTE_OS_AUTHENT (if you have it set, why would you need Kerberos?)
SQL> create user user01 identified externally as '[email protected]';
User created.
SQL> grant create session to user01;
Grant succeeded.
Connect from the PC
$ sqlplus -L /@db01
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 16 18:40:43 2014
Copyright (c) 1982, 2011, 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>
This also works in Toad, SQL-Developer and other tools using the OCI thick client. Just let “User” and “Password” blank.
In SQLDeveloper, make sure you do not check Kerberos but you use OCI Thick and no username and password
There is probably a way to do it with the jdbc thin client as document in Note 1523651.1, I have not gone that far yet
Update:
Note 303436.1 : Improper format of configuration file: Remove TAB characters from KRB5.conf file. Replace with spaces.
Thanks for this Laurent. I’m running a couple of months behind you…
Very very very helpful! Thanks
Pingback: poor man ActiveDirectory password checker | Laurent Schneider
i found everytime to run an “okinit” for sso user to get TGT is not practical.
Think about in the enterprise environment you have thousands of users trying to use SSO to connect to db. Do okinit for each of user is tidies.
if okinit has to be used, it must be automated in some scripts
i search the whole google and didn’t find anyone actually taking about this. Also i’ve done test with 2 sso users log in at the same time, running okinit will actually overwrite each other’s ticket cache.
Jeff, you don’t have to run okinit in Windows. Just set the parameter sqlnet.kerberos5_cc_name=OSMSFT:// (and eventually MSLSA in 12c)
Having a different sqlnet.ora for 11g and 12c is a failure imho
great! i found that actually does the trick. Thanks a lot!
Thanks for the post, was really useful.
I was able to configure kerberos with sqldeveloper 4.0, just config the kerberos details in the properties, use the right tns_admin directory with sqlnet.ora correctly configured and in username/password insert your windows username and password. This ticking the kerberos check.
About the MSLSA bug, if I well understand the Oracle support, will be fixed in 12.2. Having different configuration in 11 and 12 is a big fail.
Your blog post beats the Oracle support my company pays tens of thousands of dollars for. Thanks.
Hello and many thanks for this article. I try to configure kerberos for a clusterware 12c active/passive (not RAC). How can I configure kerberos in that case ?
Many thanks for your help.
Thanks for great post. We recently “kerberized” hadoop clusters and most of manual work was done by Cloudera Manager (even created service accounts in AD for each cluster server in a separate Active Directory’s OU). With Oracle it seems still a bit of manual work..
My understanding on “OKLIST does not Display the Windows Native Credential Cache when SQLNET.KERBEROS5_CC_NAME=MSLSA:” (Doc ID 1678877.1) — This only affects oklist – workaround is just to use “klist” from Windows and not oklist from OH?
So 17890382 doesn’t actually affect authentication? If it does, will be setting sqlnet.kerberos5_cc_name to 11g’s OSMSFT:// be a good workaround? As indicated 1678877.1.
Pingback: Database Engineering
Hello, what if the database Server is on Windows ? Do i have to install Kerberos-Software on my windows-db-Server?
What about okinit, sqlnet.ora etc. if db-server is on Windows ?
Trying to use your post for this slightly different szenario does not seem to work.
Looking forward to some enlightening hints. Best regards Michael.
Hi,
In sqldeveloper if set in Preferences-> advanced-> Database cache name as OSMSFT:// not working any suggestions ?