Configure Oracle 11g database to connect with AD(Domain) user using Kerberos5

Problem:

Application config files contain plain text passwords. Which is insecure and should be changed to encrypted passwords or not indicate them at all 🙂

The last solution can be achieved by KERBEROS authorization. So users will connect to the database using Active Directory user and will not indicate username/password. Like it happens in SQL Server with Windows Authorization.

So at the end of this post domain user connected to the windows computer will be able to connect to the database without indicating username and password.

========Details==============

AD Server:

Ip: 192.168.11.10

Hostname: ADSERVER.DOMAIN.GE

Realm: DOMAIN.GE

===========================

Database Server:

Ip: 192.168.11.15

Hostname: DBSERVER.DOMAIN.GE

Database Name/SID: ORCL

===========================

Client:

Ip: 192.168.11.20

Domain User: MARIAMI.DOMAIN.GE

Client Home: D:\app\oracle\product\11.2.0\client_1

===========================

Let’s start..

========AD configuration

1. Under DOMAIN.GE /Users  create a new user with the same name as the hostname of the database (with lowercase letters).

First name             : dbserver.domain.ge
Fill name               : dbserver.domain.ge
User logon name : dbserver.domain.ge
User logon name (pre-Windows 2000): DOMAIN \ dbserver.domain.ge

Next >

I prefer to check “Password never expires”  and uncheck “User must change password at next logon”

Next >

Finish

Right click on the newly created user , choose Properties -> choose tab Account ->in Account options section -> check “Do not require Kerberos preauthentication”

2. Generate keytab file using ktpass.exe

ktpass.exe -princ oracle/dbserver.domain.ge@DOMAIN.GE -ptype KRB5_NT_PRINCIPAL -mapuser dbserver.domain.ge -crypto ALL -pass qwert123QWERT  -out d:\keytab

========Database Server Configuration

export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/db/network/admin

mkdir -p $TNS_ADMIN/kerberos

Copy keytab file generated on AD server and place it in $TNS_ADMIN/kerberos/  location.

Create krb5.conf file.

vim $TNS_ADMIN/kerberos/krb5.conf

[libdefaults]
default_realm = DOMAIN.GE

[realms]
DOMAIN.GE = {
kdc = adserver.domain.ge:88
}

[domain_realm]
.domain.ge = DOMAIN.GE
domain.ge = DOMAIN.GE

File  is case sensitive 🙂 try to not make case sensitive errors.  AD server hostname is in lowercase letters.

So $TNS_ADMIN/kerberos/ contains two files:

ls  $TNS_ADMIN/kerberos/

keytab    krb5.conf

Configure sqlnet.ora file.

Note that at this time if you mistakenly configure sqlnet.ora file , connections to the database will cause error. Be very careful when configuring on production server. But you can solve this error in 3 seconds , by replacing new sqlnet.ora with old sqlnet.ora or just change parameter

SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)

to

SQLNET.AUTHENTICATION_SERVICES= (BEQ)

vim $TNS_ADMIN/sqlnet.ora
SQLNET.KERBEROS5_KEYTAB = /u01/app/oracle/product/11.2.0/db/network/admin/kerberos/keytab

SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)

SQLNET.KERBEROS5_CONF = /u01/app/oracle/product/11.2.0/db/network/admin/kerberos/krb5.conf

SQLNET.KERBEROS5_CONF_MIT = TRUE

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

ADR_BASE = /u01/app/oracle

SQLNET.KERBEROS5_CC_NAME=/u01/app/oracle/product/11.2.0/db/network/admin/kerberos/cache_file

#TRACE_LEVEL_SERVER = SUPPORT

#TRACE_UNIQUE_SERVER = on

#TRACE_DIRECTORY_SERVER = /u01/app/oracle/traces

#TRACE_FILE_SERVER = server

DIAG_ADR_ENABLED = OFF

#TRACE_TIMESTAMP_SERVER = ON

Note: I have commented TRACE_* parameters , because it is not necessary if everything is OK , but if connection fails then you will need these parameters to be uncommented to generate more informative trace file to solve the problem.

Connect to the database via sqlplus and create database user corresponding to the domain user, using uppercase letters.

sqlplus / as sysdba

create user “MARIAMI.DOMAIN.GE” identified externally.

grant connect, resource to “MARIAMI.DOMAIN.GE”;

ALTER SYSTEM SET os_authent_prefix=” SCOPE=SPFILE;

ALTER SYSTEM SET remote_os_authent=FALSE SCOPE=SPFILE;

shutdown immediate;

startup;

Generate ticket

$ okinit -e 23 -f dbserver.domain.ge

Enter the password of dbserver.domain.ge username , in our case qwert123QWERT.

Note, it will generate a cache file called cache_file(SQLNET.KERBEROS5_CC_NAME parameter in sqlnet). It has expire time (8 hours) and after it is passed , connection to the database without password will not work. So you will need a cronjob to run the above command every 7 or less hours. But for now you have 8 hours to configure kerberos and test the connection.. )

========Client Configuration

1. Configure sqlnet.ora

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

SQLNET.KERBEROS5_CONF_MIT = true

SQLNET.KERBEROS5_CC_NAME=OSMSFT://

DIAG_ADR_ENABLED = OFF

SQLNET.AUTHENTICATION_SERVICES= (beq, kerberos5)

SQLNET.KERBEROS5_CONF = d:\app\oracle\krb5.ini

#TRACE_LEVEL_CLIENT = SUPPORT

#TRACE_UNIQUE_CLIENT = on

#TRACE_DIRECTORY_CLIENT = D:\app\oracle

#TRACE_FILE_CLIENT = client

#TRACE_TIMESTAMP_CLIENT = ON

#SQLNET.KERBEROS5_KEYTAB = C:\krb5\v5srvtab

2. Copy krb5.conf file content from db server and name it as .ini instead of .conf

–krb5.ini

[libdefaults]
default_realm = DOMAIN.GE

[realms]
DOMAIN.GE = {
kdc = adserver.domain.ge:88
}

[domain_realm]
.domain.ge = DOMAIN.GE
domain.ge = DOMAIN.GE

3. tnsnames.ora , nothing special.

ORCL =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

4.  Add kerberos5 in C:\windows\system32\drivers\etc\services:

Before:

kerberos 88/tcp krb5 kerberos-sec #Kerberos

kerberos 88/udp krb5 kerberos-sec #Kerberos

After:

kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos

kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos

Try to connect without password. /@ORCL  together.

sqlplus /@ORCL

With PL/SQL Developer , do not indicate username and password just sid and try to connect. I have noticed that PL/SQL Devloper version 9 doesn’t worked for me version 10 worked.

Note: If you are using shared servers and during connecting to the database returns ORA-01017: invalid username/password; logon denied. The problem may be the following, that  you changed sqlnet.ora after instance startup. The problem is that shared servers read sqlnet.ora just before instance startup, so if you change any parameters in sqlnet.ora file after instance startup then shared servers will not recognize it. The only solution is to restart database, to re-read sqlnet.ora file.