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.
