Authentication of Oracle database users in Microsoft Active Directory - outlook

The assignment - to exclude maintenance of two equal user entities (in Microsoft AD and Oracle DB) and provide password-less entrance into database provided successful authorization in AD. User starts its session on Windows, and run 2-level applications which require authorization in the databases as well.

All the info below is a conspectus. Please look through official Oracle Security documents and blogs for the information in detail. Good starting point is :

https://blogs.oracle.com/database/post/make-someone-else-do-the-work-managing-oracle-database-19c-users-in-active-directory-part-1-kerberos 

1. Important database parameter is os_authent_prefix. Default value of it is "OPS$". It's a prefix to the operating system (OS) username. In order to have equal database and OS usernames, leave the null value for this parameter. It's not online and PDB modifiable - you need to restart the DB/CDB. 

2. Authentication goes through Kerberos5 protocol, port 88 (default) should be accessible from database server(s).

3. Edition of database - Enterprise (EE) 

4. You need to create so called proxy technological AD user on behalf of whum the kerberos5 ticket will be received on the database side. Toggle "Password never expires", deselect "DES encryption" and "Kerberos Pre-authentication".

On AD side enter the command :

c:> ktpass -princ oracle/db_server_dns_name.dns_domain_name@DOMAIN_NAME -pass <password> -mapuser technological_user@DOMAIN_NAME -crypto all -ptype KRB5_NT_PRINCIPAL -out c:\krb5.keytab. 

DOMAIN_NAME (i.e. AD domain name) must always be in upper-case, including in database sql statements containing oracle usernames.

Put resulted file to $ORACLE_HOME/network/admin server directory.

!Important! Objects in AD have so called knum attribute (analog of SCN 😀 in Oracle DB), which goes forward after any alterations of the user. So keep the keytab file in sync with this AD user. After any alteration of technological user recreate keytab file, because database checks the equality of technological user's knum and knum inside keytab. Also, because of this, create dedicated AD user for every other (standby or else) database servers. 

5. Example of Oracle server configuration files :

5.1 Add these lines into sqlnet.ora

# kerberos parameters
sqlnet.kerberos5_conf                  = /u01/app/oracle/product/1910/network/admin/krb5.conf
sqlnet.kerberos5_conf_mit              = true
sqlnet.fallback_authentication         = true
sqlnet.authentication_kerberos5_service = oracle
sqlnet.authentication_services         = (beq, kerberos5pre, kerberos5)
names.directory_path                   = (ldap, tnsnames, ezconnect, hostname)
# server-side only
sqlnet.kerberos5_keytab                = /u01/app/oracle/product/1910/network/admin/krb5.keytab

5.2 krb5.conf

[libdefaults]
 default_realm = DOMAIN_NAME
 clockskew = 6000
 passwd_check_s_address = false
 noaddresses = true
 forwardable = yes
[realms]
  DOMAIN_NAME = {
   kdc = domain_controller_1.DOMAIN_NAME:88
   kdc = domain_controller_2.DOMAIN_NAME:88
 }
[domain_realm]
  .domain_name = DOMAIN_NAME 
   domain_name = DOMAIN_NAME
  .DOMAIN_NAME = DOMAIN_NAME
   DOMAIN_NAME = DOMAIN_NAME

5.3 Also it's important to enable tracing on the server in case of  troubleshooting (there gonna be lots of trace files with huge amount of space; so keep an eye on the free space in trace file directory). The example of enabled tracing in sqlnet.ora :

# trace
DIAG_ADR_ENABLED = off
TRACE_LEVEL_SERVER = on
TRACE_DIRECTORY_SERVER = /u01/app/oracle/product/1910/krb5/trace
TRACE_FILE_SERVER=oracle

Some of the settings are enabled on the fly, but very likely you should restart database to enable most of them.

6. On the client machine add authentication settings to sqlnet.ora :

sqlnet.kerberos5_conf                  = %ORACLE_HOME%\network\admin\krb5.conf
sqlnet.kerberos5_conf_mit              = true
sqlnet.fallback_authentication         = true
sqlnet.authentication_kerberos5_service = oracle
sqlnet.authentication_services         = (kerberos5pre, kerberos5)
sqlnet.kerberos5_cc_name                = OSMSFT://

krb5.conf may be replicated from the database server. 

!Important! If the task or program doesn't support AD authentication and there's a need to logon to database user authenticated by the database, use another dedicated sqlnet.ora with sqlnet.authentication_services = none.

Also make sure that file %windir%\system32\drivers\etc\services contains lines:

kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos

kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos

7. Alter database user to authenticate using Microsoft AD :

SQL> alter user username identified externally as '<ad_user>.DOMAIN_NAME' ;

8. Check username and corresponding AD usernames (with domain) in rhw :

SQL> select username, external_name from dba_users where external_name is not null ;

9. Connect to the database without entering username/password, for example :

c:> sqlplus /@tns_name_for_connect

SQL> select user, sys_context('userenv','authentication_method'), sys_context('userenv','authenticated_identity') from dual ;

Basically, that's all. Hope it'll help ! Enjoy ;-)