In this blog entry I try to document how to enable Kerberos. This procedure was actually created and followed during a project at one of my customers.
The Infrastructure
AD
– windows 2008R2 server
– domain : milkyway.space.com
– Kerberos installed and enabled
– DES encryption default disabled
Server :
– moon.milkyway.space.com
– database : crater
– version : 11.2.0.3.4
Client
– windows 7 enterprise edition
– 11.2.0.3 client
The Procedure
- On the AD server
- Create a service account in Active Directory for the database server moon to validate the Kerberos tickets with. This user does not need any specific rights but enable “password never expires”. We called this account “ssoval”
- ensure that you deselect Setup option “Use DES Encryption” and select option “Do not require Kerberos PreAuthentication” for this user
- Make sure that the SPN is set to the correct realm
setspn -A oracle/moon.milkyway.space.com@MILKYWAY.SPACE.COM ssoval
(oracle is just the name of the service, we reuse this name in the kerberos config to point here. This has no connection to service_names of the database.) - Extract a keytab file for this user so we don’t need to enter password to create tickets
ktpass -princ oracle/moon.milkyway.space.com@MILKYWAY.SPACE.COM -crypto all -pass ssoval -mapuser ssoval -out v5srvtab
- Put this file on the database server. I’ve put it in /etc/v5srvtab
- On the Database Server “moon”
- Make sure Advanced Security Option is installed, this is a paying option on top of Enterprise Edition.
- Generate a kerberos ticket, this will be used for connection to the kerberos server for ticket validation
$ORACLE_HOME/jdk/bin/kinit -k -t /etc/v5srvtab oracle/moon.milkyway.space.com
( You might want to create a crontab job for this so that you always have a valid ticket ) - Adjust the sqlnet.ora
SQLNET.KERBEROS5_CONF=/etc/krb5.conf
SQLNET.KERBEROS5_KEYTAB=/etc/v5srvtab
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5) - Create the /etc/krb5.conf file
[libdefaults]
default_realm = MILKYWAY.SPACE.COM
[realms]
MILKYWAY.SPACE.COM = {
kdc = DC1.MILKYWAY.SPACE.COM:88
kdc = DC2.MILKYWAY.SPACE.COM:88
}
[domain_realm]
.milkyway.space.com = MILKYWAY.SPACE.COM
milkyway.space.com = MILKYWAY.SPACE.COM
- On the Database “crater”
- Clear OS_AUTHENT_PREFIX
SQL> alter system set OS_AUTHENT_PREFIX=’’ scope=spfile;
- Disable remote_os_authent
SQL> alter system set remote_os_authent=false;
- restart the database
- Clear OS_AUTHENT_PREFIX
- On the Windows Clients
- Make sure ASO is installed.
- Adjust the sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (BEQ,KERBEROS5)
This last line is important for windows clients because this reuses the already generated tickets available on the system as a result of your AD login. Hence it enables the SSO login. Keep in mind that the Oracle tool okinit will fail with OSD error if this cache is set when you try to get manual tickets.
SQLNET.KERBEROS5_CONF =c:\kerberos\krb5.conf
SQLNET.KERBEROS5_CONF_MIT = true
SQLNET.KERBEROS5_CC_NAME=OSMSFT://
- Create the c:\kerberos\krb5.conf file identical as on the server except for the port numbers
[libdefaults]
default_realm = MILKYWAY.SPACE.COM
[realms]
MILKYWAY.SPACE.COM = {
kdc = DC1.MILKYWAY.SPACE.COM
kdc = DC2.MILKYWAY.SPACE.COM
}
[domain_realm]
.milkyway.space.com = MILKYWAY.SPACE.COM
milkyway.space.com = MILKYWAY.SPACE.COM - Make sure the file services in directory c:\windows\system32\drivers\etc has “kerberos5” in the list as first entry
kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos
kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos
Now you are ready to use Kerberos Authentication.
Example for an user “Bjorn”
- Create an user Bjorn on the AD server in domain MILKYWAY.SPACE.COM
Ensure that you :- deselect Setup option “Use DES Encryption”
- select option “Do not require Kerberos PreAuthentication”
The username is case sensitive, so make sure you have the correct case.
- Create an user Bjorn on the database crater
SQL> create user BJORN IDENTIFIED EXTERNALLY as 'Bjorn@MILKYWAY.SPACE.COM';
SQL> grant create session to BJORN; - Login to the windows desktop and connect to the database over TNS for example :
C:\> sqlplus /@crater
CONNECTED
SQL> show user
USER is "BJORN"
SQL> select sys_context('userenv ', 'session_user') from dual;
SYS_CONTEXT('USERENV','SESSION_USER')
-----------------------------------------
BJORN
SQL> select sys_context('userenv','external_name') from dual;
SYS_CONTEXT('USERENV','EXTERNAL_NAME')
-----------------------------------------------------------------
Bjorn@MILKYWAY.SPACE.COM
Troubleshooting
- KDC has no support for encryption type : pre-11gR2 only supports DES encryption. The company where I performed this setup, did not want to enable this legacy protocol ( and rightly so ), so only connections with 11.2 and higher clients to 11.2 and higher databases will work in this setup.
- Cannot find KDC for requested realm : Make sure your services file is correctly formatted and kerberos5 is the first protocol in the list for port 88
Special thanks to antonio mata gomez from Oracle Belgium for support in this project
Hi Bjorn,
Thanks for this blogpost, it helped me configuring the AD for Keberos. I did notice that you mention Keberos is part of ASO a payed option, but this is not true anymore. From http://docs.oracle.com/cd/E11882_01/license.112/e10594/editions.htm :
Enterprise User Security is an EE feature for directory-based management of database users. It does require a corresponding Oracle Identity Management Directory Services Plus to be licensed.
Usage of Enterprise User Security with Oracle Database strong authentication (PKI, Kerberos) no longer requires Oracle Advanced Security to be licensed.
Best,
Jacco.
Thanks for the extra information.
Great article. Does this only work for sqlplus or will it work for other sql tools such as sql developer?
Should work for any tool that uses sqlnet to connect to the database.
Thanks for a great article. It is very thorough and helped me a lot.
I see this as a first step, and the second step as actual account management.
Among other things, I would like that adding a new user to an AD-group automagically adds the user to the Oracle database and removing the same user disables the account. Do you know any ways to achive this, besides of using Oracle Internet Directory (OID)?
Glad it helped you. About syncing users, I’m not sure how to do that without OID, Niels.
I’m sure something can be done with scripting though. Perl for example has full support for AD and Oracle connections, you could try it that way?
Thank you for a very usefully article. In this article, ktpass -princ oracle/…. is oracle anything to do with the database instance (like database connection string, database instance, ORACLE_SID, or Oracle service in Windows services)? Can I use any string to replace oracle here?
As mentioned in the article, it is a general name we give this connection to reuse later. Not related to anything database related :
(oracle is just the name of the service, we reuse this name in the kerberos config to point here. This has no connection to service_names of the database.)