Wednesday, September 08, 2010

Single Sign On to the Database (or WNA for SQL*Plus)

In this entry, I'll demonstrate how easy it is to do Kerberos authentication to the database, or -rephrasing that- how to do WNA in the database.
The goal of this exercise will be to allow users to log on to a remote database using the following syntax:
sqlplus /@db10g

Scenario

I like to think major sites have serious machines and ditto operating systems. Workstations are uncannily often based on MS Windows, so I'll use MS Windows for a client, and Linux for database server.

Prerequisites

Both client and server need to have the Advanced Security Option installed, for which Oracle wants to get paid. Just read the license conditions: if you want to do Kerberos, you need to pay.
Checks

Now, how do you check whether you have that installed? On *ixes, you can use the command adapters:
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
[ora10@db1 ~]$

According to the documentation, that is proof. I have found that it is not always the case, and you may find yourself without the Oracle Kerberos utilities. Better check if these are available, they sould be:
[ora10@db1 ~]$ ls $ORACLE_HOME/bin/ok*
/oracle/db10/10GRel2/bin/okdstry /oracle/db10/10GRel2/bin/okinit
/oracle/db10/10GRel2/bin/oklist /oracle/db10/10GRel2/bin/okdstry0
/oracle/db10/10GRel2/bin/okinit0 /oracle/db10/10GRel2/bin/oklist0

That concludes checks on the software stack on the server. If you miss anything: install the Advanced Security Option!
Client checks

For MS Windows clients, there is unfortunately not much else to do than to fire up OUI, and list the installed options. Fore mentioned Kerberos utilities should also be available on the client, so you may want to check that out as well.

Hands on


Service Principal

First thing to do, is to allow the database server to get Kerberos tickets. In order to do that, I need a Kerberos service principal. In order to do that, I need to create a domain user on my MS Windows 2000 Domain Controller:

I have chosen the name of the instance, but that does not really matter. You may want to use a more generic user name if you have an environment with many instances per machine.
Make sure you set "Use DES encryption" and "Do not require preauthentication", as shown here:

You may have to patch your MicroSoft Active Directory to support this. Check out Knowledge Base article 833708 on how to do this in the registry, or apply SP 1.
I think everybody has SP1 (or better) - I did not have to change anything on my PDC image, but this may cause problems.
Keytab file

Now that I created a user, I must transfer the credentials to the database server. That involves exporting keys to a file, and it is done using this statement on the PDC (or member domain controller):

ktpass -princ service/username@AD.DOMAIN -mapuser username -pass APassWord -DesOnly -crypto des-cbc-md5 -ptype KRB5_NT_PRINCIPAL -out c:\serv.keytab

This is an actual session:


The beauty is, that you may forget the password: you will never need it again. In fact, you need not know the password, unless things don't work, and you want to troubleshoot.
You should remember the service name: you need to specify that in configuration files. I use "orcl" in this example - you may want to use a more generic name, like krb5srv.
Oracle MOS Document states you can only use -crypto des-cbc-crc for encrytion; this is incomplete; you can also use -crypto des-cbc-md5, as I did. (you need md5 for HTTP principals, but that's an other story).

Prepare server


Binary transfer the keytab file to the server. These files are called keytab files, as they contain a table of keys - the keytab for short.
Logon to the database server an cd to your TNS_ADMIN directory. Which happens to be $ORACLE_HOME/network/admin, if you do not use $TNS_ADMIN.
Alter the sqlnet.ora file to look like:
[ora10@db1 admin]$ cat sqlnet.ora
sqlnet.authentication_services=(beq, kerberos5)
sqlnet.kerberos5_conf=/oracle/db10/admin/krb5.conf
sqlnet.kerberos5_keytab=/oracle/db10/admin/db1.keytab
sqlnet.authentication_kerberos5_service=orcl
sqlnet.kerberos5_conf_mit=true

Make sure the variables point to the correct locations of the keytab file and the (general) Kerberos configuration file. This configuration file contains:
[ora10@db1 admin]$ cat /oracle/db10/admin/krb5.conf
[libdefaults]
default_realm = HOME.LOCAL
[realms]
HOME.LOCAL = {
kdc=192.168.1.250
}
[domain_realm]
.home.local = HOME.LOCAL
home.local = HOME.LOCAL
Mind you, I am completely bypassing the fact, your server should be configured to support Kerberos, i.e. without support for Kerberos on OS level, this is less likely to succeed.

Prepare Client


Client side is about the same as the server, apart from the service principal/keytab file. Change sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (all)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
sqlnet.kerberos5_conf=C:\oracle\clt10g\network\admin\krb5.conf
sqlnet.kerberos5_conf_mit=true
sqlnet.authentication_kerberos5_service=orcl
# sqlnet.kerberos5_cc_name=C:\oracle\clt10g\network\admin\krbcache
sqlnet.kerberos5_cc_name=OSMSFT://

Note the last line: it is MS specific, and allows the use of the MS Windows internal Kerberos cache, instead of a file based one (as specified the line above).
Note however, using the internal cache will disallow the use of Oracle Kerberos utilities; these will err off with "OSD error"

The Kerberos configuration file is similar to the one on the server, and connects realms to domains:
[libdefaults]
default_realm = HOME.LOCAL
[realms]
HOME.LOCAL = {
kdc = w2k-pdc.home.local
}
[domain_realm]
.home.local = HOME.LOCAL
home.local = HOME.LOCAL


You may now test the Kerberos setup. You should be able to get a ticket from the Kerberos Ticket Granting Server (which is MS Active Directory, in this case).
Try to get a ticket for a known MS domain account, I can log on to the domain "home.local" as frank, that would make my account "frank@HOME.LOCAL" - and be careful, Kerberos is strangely case sensitive! The oklist utility reveals:
Ticket cache: win2kcc
Default principal: frank@HOME.LOCAL

Valid Starting Expires Principal
08-Sep-2010 12:56:03 08-Sep-2010 22:56:03 krbtgt/HOME.LOCAL@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:03 08-Sep-2010 22:56:03 krbtgt/HOME.LOCAL@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 13:28:28 08-Sep-2010 22:56:03 orcl/db1.home.local@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:04 08-Sep-2010 22:56:03 W2K-PDC$@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:04 08-Sep-2010 22:56:03 ldap/w2k-pdc.home.local/home.local@HOME.LOCAL renew until 15-Sep-2010 12:56:03

Now, create a database user "FRANK@HOME.LOCAL", in uppercase, and quoted, as Oracle uses the commercial at-sign for special purposes. You should now be able to login:

C:\Documents and Settings\frank>sqlplus /@db10

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 8 13:51:56 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> show user
USER is "FRANK@HOME.LOCAL"
SQL>

Stay tuned

Of course, things start getting interesting when adding OID and EUS. Wall-to-wall SSO!
Later...

6 comments:

Anonymous said...

Hello Frank,

Thanks for posting this. We have Oracle Server on Windows 2003, KDC on another Windows 2003. okinit works and we get the initial TGT. We get the ORA-12641 when try to connect using sqlplus. Most of the documentation talk about oracle server running on Unix/Linux not Windows. We turned on trace but can not find anything more than ORA-12641, TNS-12641

Frank said...

12641, 00000, "Authentication service failed to initialize"
// *Cause: The authentication service failed during initialization.
// *Action: Enable tracing to determine the exact error.
Did you trace the Kerberos communication, using the following entries in sqlnet.ora?
TRACE_LEVEL_OKINIT=16
TRACE_DIRECTORY_OKINIT=
TRACE_FILE_OKINIT=krb_init

Unknown said...

Hi Frank,
You did a great job, as according to my knowledge, there is no document found of this level on the web. I found two documents on metalink but they are much ambiguous. I would like to add a bit into your document that it is possible for the kerberos user to be authenticated without support for Kerberos on OS level.
I'm facing a problem. My oracle 11g server is configured for kerberos on Win2003sp2(DBSVR) and .NET application is on another win2003sp2(APPSVR). When I configure APPSVR in Active Directory to be "Trusted for delegation to any service (Kerberos only)", my application runs perfectly, but when I configure it as "Trusted for delegation to specified services only" and add SPN that I created while creating keytab, my application gives error. Oracle 11g documentation says that 11g supports constrained delegation, but how, it is not clear. Any hint will be greatly appreciated.

Frank said...

Altaf, thanks for the thumbs up. I am not quite sure what you mean by "it is possible for the kerberos user to be authenticated without support for Kerberos on OS level" - of course, somewhere along the line, there should be Kerberos support. If not, you could never have received a Kerberos ticket.
As for your problem: it seems a MicroSoft issue. I recall some settings like "allow to log on as batch process" should explicitly be enabled for some applications. Not sure if this is the case here - you provide not enough details for me to understand your issue(s).
Basically: you database should be able to contact the Kerberos Ticket Granting server to inquire the status of the ticket issued by the incoming connection request. It has nothing to do with what role the machine has within a MicroSoft domain.

Unknown said...

Hi Frank,
Please accept my apologies for being too late as I was on leave from my office for two weeks and was not able to respond timely. I am sorry, I could not understand properly your statement in your document, “your server should be configured to support Kerberos, i.e. without support for Kerberos on OS level, this is less likely to succeed.” If it means “Kerberos libraries should be installed” then it is very right. Perhaps every OS includes Kerberos libraries in its very basic installation e.g. Linux includes Krb5 workstation and libs rpms in its basic installation. In my previous comments I meant to say that these libraries need no further configuration. Give host (oracle server) an appropriate name, add its entry into DNS and that’s all (no further configuration at os level is required). Most of the documents on Oracle Kerberos authentication consider that oracle server is a member of some domain which is not correct. There is no need to make oracle server a member of a domain (by using samba or some other means).
As far as my problem is concerned, I want to implement oracle’s new feature “constrained delegation”. I have implemented it using .Net and SQL Server but unable to do it with Oracle. Would you please guide me to implement the following oracle’s feature: -
“The Oracle Kerberos authentication mechanism now supports the Microsoft Windows Server 2003 constrained delegation feature. The middle tier can use the Kerberos adapter to authenticate to the Oracle Database without providing the user's forwarded Kerberos credentials.” (Database 11g Release 2 (11.2) New Features in Oracle Advanced Security)
Kind Regards

Frank said...

Cannot comment on that - 11.2 is on the backlog list, somewhere :).
However, I have been looking into Proxy users (and this just sounds too similar to be completely off), and was NOT impressed.
I would rather have the remote credentials presented to the database, so I can actually do SSO/WNA to the database as well, and use features like Enterprise user Security.
I will touch that in the wall-to-wall-security write up