Kereberos Integration with Oracle Database for authentication - 19c

 

Was recently testing integrating kerberos with an oracle database. This is extremely helpful
when there are many databases to manage.  Complying with password reset policy across many
databases is a nearly impossible task. By integrating with kerberos this is achieved each time a
user's domain account password is changed. This also helps to move from
default password authentication to strong authentication. As most of the organizations
are already using Windows AD, it's very easy to integrate with the Oracle database.
Centralized authentication also provides the benefit of single sign-on (SSO) for users.


Below are key areas steps are performed in the integration process.


  1. Settings on the Kerberos Server

  2. Configuration on the Database server

  3. Setting on the Client


It can be taken to next level for CMU




  1. Configurations on the Kerberos Server(Version I used - Windows 2012)



Tools and Utilities used


  1. Active Directory Users and Computers (ADUC)

  2. Ktpass.exe : Utility to generate keytab file


Server Details


New windows server 2012 installation - Virtual box guest



Details

Windows 2012 server - Domain controller


Domain Controller IP :192.168.56.120

Computer name: DC01

Domain name: yootest.local





Account for oracle database server/host

Create an account in the AD for the database host as below


First Name: hol.localdomain(This is fully qualified name of database host)

User Logon name: hol



User account for mapping


Fullname: oracledb

User logon name: oracledb

Password never expires


Create a normal domain account for mapping. During keytab generation this account will be used to map with the host account we created in the earlier step. More than one host can be mapped to the same account. 






Generate keytab file for the database host


Open command prompt as Administrator and run below command to generate keytab file. This will generate keytab file and set spn


ktpass.exe -princ oracle/hol.localdomain@YOOTEST.LOCAL -mapuser oracledb -crypto all -ptype KRB5_NT_PRINCIPAL  -pass <password for the AD mapping account> -out c:\keytab.db.hol





You can verify spn with setspn -L command


Setspn -L oracledb



List service principal name mapped to domain account oracledb. In this case two different hosts are mapped to  oracledb domain account. It is possible to map multiple hosts, There are some limits though.





Move the generated keytab (keytab.db.hol) file to the database server






2. Configuration on the database host


OS: Oracle Linux 7.7 - db server

Host Name: hol.localdomain

IP: 192.168.56.64

Database Version: 19c


Utilities (Available with Database & Client Installation)

oklist

Okinit

okdstry

oknv


These are provided with oracle installation once $ORACLE_HOME/bin is in your path it should be available. 



Copy the keytab generated earlier on this domain server to the database host


/home/oracle/keytab.db.hol



Create krb5.conf file


File name: /home/oracle/krb5.conf


[libdefaults]

default_realm = YOOTEST.LOCAL

clockskew = 6000

forwardable=true  

[realms]

YOOTEST.LOCAL = {

kdc = yootest.local:88

}

[domain_realm]

.YOOTEST.LOCAL = YOOTEST.LOCAL

YOOTEST.LOCAL = YOOTEST.LOCAL

.yootest.local = YOOTEST.LOCAL

yootest.local = YOOTEST.LOCAL




Modify sqlnet.ora to add parameter required for kerberos authentication


NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)


SQLNET.KERBEROS5_KEYTAB=/home/oracle/keytab.db.hol

SQLNET.KERBEROS5_CONF=/home/oracle/krb5.conf

SQLNET.KERBEROS5_CONF_MIT=TRUE

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

#SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)

SQLNET.AUTHENTICATION_SERVICES=(beq,kerberos5pre, kerberos5)


sqlnet.kerberos5_clockskew=6000


#SQLNET.KERBEROS5_CC_NAME=/tmp/cc


sqlnet.fallback_authentication=TRUE




# Incase Tracing required

##TRACE_LEVEL_SERVER = 15

##TRACE_FILE_SERVER = server

##TRACE_DIRECTORY_SERVER = /tmp/trace

##DIAG_ADR_ENABLED= OFF



Connect to the database and create a external authenticated user


Create a database user 


create user TESTUSER identified EXTERNALLY AS  'testuser@YOOTEST.LOCAL';


Grant create session to testuser ;


Or


create user "TESTUSER@YOOTEST.LOCAL" identified EXTERNALLY ;


grant create session to "TESTUSER@YOOTEST.LOCAL" ;


* the user testuser is already created in the windows AD, if not create an ad account



We will be tempted to test right away with this setup but this setup will not allow us to connect to database as a client


Attempting to connect to database this way will throw ORA-12638



ERROR:

ORA-12638: Credential retrieval failed



Connect to database as a client


We need to setup a different TNS_ADMIN to configure client to connect to the database using kerberos 



Create a new dir for connecting to the db from client

New TNS_ADMIN: /home/oracle/tmp_tns





Sqlnet.ora


NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)


SQLNET.KERBEROS5_CONF=/home/oracle/krb5.conf

SQLNET.KERBEROS5_CONF_MIT=TRUE

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

SQLNET.AUTHENTICATION_SERVICES=(beq,KERBEROS5)


sqlnet.kerberos5_clockskew=6000


#add trace related para


Create a tnsnames.ora file with details of database


tnsnames.ora




TEST =

  (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = hol)(PORT = 1521))

(CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = test)

)

  )



Set the tns admin to the above location and test 


export TNS_ADMIN=/home/oracle/tmp_tns




Use tnsping and make sure it's using correct sqlnet.ora,


tnsping test





Generate kerberos ticket


Make sure that $ORACLE_HOME/bin is in your path so that okinit and oklist work 


okinit testuser


Supply the password for getting kerberos ticket


Verify the ticket status


oklist



This shows valid ticket is available


Connect to the database using kerberos 



Sqlplus /@<tnsalias for the database>


Sqlplus /@test



If use is created with the second option 


No password is asked and we are connected to the database as testuser





Windows 10 client 




Windows Client






DNS is properly set 


19c Client


Copy krb5.conf file used earlier on to this pc 


C:\app\administrator\krb5.conf



Sqlnet.ora


#SQLNET.AUTHENTICATION_SERVICES= (NTS)


NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


SQLNET.KERBEROS5_CONF=C:\app\administrator\krb5.conf

SQLNET.KERBEROS5_CONF_MIT=TRUE

SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

SQLNET.AUTHENTICATION_SERVICES=(kerberos5)

#SQLNET.AUTHENTICATION_SERVICES=(kerberos5pre)


sqlnet.kerberos5_clockskew=6000


#sqlnet.fallback_authentication=TRUE

#SQLNET.KERBEROS5_CC_NAME = MSLSA:





TRACE_LEVEL_CLIENT = 15

TRACE_FILE_CLIENT = client

TRACE_DIRECTORY_CLIENT = C:\app\log

TRACE_TIMESTAMP_CLIENT = ON

TRACE_UNIQUE_CLIENT = ON

DIAG_ADR_ENABLED= OFF


Generate a ticket using okinit 



Connection to the database should be established as below


Audit entry 



By default when logging on to windows domain account it gets assigned a default kerberos ticket which can be listed using the klist command. This ticket can be used for connecting to the database without manually generating a ticket with okinit.


To achieve this uncomment below parameter in the sqlnet.ora


SQLNET.KERBEROS5_CC_NAME = MSLSA:




After this is done trying to connect to db with sqlplus might throw ora-12638. I got this on windows 10 19c(19.3 client)



ERROR:

ORA-12638: Credential retrieval failed


Trace was having


(4780) [08-MAY-2023 21:49:31:893] nauztk5aauthent: Error building credentials request "9".

(4780) [08-MAY-2023 21:49:31:893] nauztk5aauthent: failed

(4780) [08-MAY-2023 21:49:31:893] nauztk5aauthent: exit

(4780) [08-MAY-2023 21:49:31:893] nau_ccn: get credentials function failed

(4780) [08-MAY-2023 21:49:31:893] nau_ccn: failed with error 12638



Fix


  1. Change the domain account option, check “account is sensitive and can not be delegated”


Once changed, on the client machine logout and login to get a new ticket reflecting this change


  1. Another fix is to have below reg setting ( Adding this has security implications and not recommended, but it might help in testing)


Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters








Comments

Post a Comment

Popular posts from this blog

APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter (R12 12.1.3)

Image
APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter After upgrading oracle application 11i(11.5.10.2) to R12(12.1.3) running concurrent program request caused this issue When concurrent program is run for a concurrent request after upgrading to R12 12.1.3 the lov, which is attached to a parameter, gives APP-FND-01436: “List of Values cannot find any values for you to choose”. When the same request is run from oracle applications 11i 11.5.10.2 the same value set is working fine. The value set is based on a table. If you go and test the value set query from the value set definition screen it returns complete successful message yet if you go to the parameter of the concurrent request program form where the particular value set is used it gives you the above error. After searching google and oracle support I found metalink note 1400963.1, EBS R12.1: Parameter for Concurrent Request Using Table Based LOV G...
Read more

WEB ADI - issues encountered during initial access

Image
On accessing create document link under Desktop Integrator responsibility browser was showing following error Set BNE UIX Physical Directory profile value to proper directory at site level Location = $COMMON_TOP/html/cabo/  (11i) Location = $COMMON_TOP/webapps/oacore/html/cabo/  (R12) As this was a cloned instance the value of this profile setting was pointing to a wrong location. set as BNE UIX Physical Directory /u03/oracle/prodcomn/html/cabo/ Access to functions are controlled by function security so to access and work with web adi the user should have proper access. You should have access to particular integrator to access and work with it Find out responsibility which is created to work with web adi check the main menu for this responsibility. Modify the responsibility or Menu if default is not fitting to your needs. For instance Desktop Integration Menu has create document, Define Lay out, Define Mapping, Manage Documents, Setup options Note : you...
Read more

Error processing request - Contact your application administrator apex 20.1

Image
After upgrading 11.2.0.4 to 19c with apex 20.1 accessing apex_admin/apex pages was throwing “Error processing request” Contact your application administrator. There were three invalid objects for APEX_200100 WWV_FLOW_SESSION_RAS WWV_FLOW_WIZARD_API WWV_FLOW_DATA_LOADER Tried compiling these objects but compile was completing with PL/SQL: ORA-00942: table or view does not exist To resolve the issue following rant needs to be given then compile (ran both grant and compile as sysdba) grant select on SYS.DBA_XS_DYNAMIC_ROLES to APEX_200100; grant select on SYS.DBA_TAB_IDENTITY_COLS to APEX_200100; alter package APEX_200100.WWV_FLOW_SESSION_RAS compile body; alter package APEX_200100.WWV_FLOW_WIZARD_API compile body; alter package APEX_200100.WWV_FLOW_DATA_LOADER compile body; Returned 0 invalid object for APEX_200100  select * from dba_objects where status = 'INVALID' and owner = 'APEX_200100' ; Now both admin and apex pages ar...
Read more