Looking for something? Try here..

Monday, January 30, 2023

Oracle CMU - Service Principal Name with multiple servers

 I have written regarding setting up Oracle Centrally Managed Users (CMU) and the various troubleshooting steps in my previous posts. You can take a look at those by clicking below links.

Once we complete the setup, one of the most anticipated questions one will have is how many Service Principal Accounts (oserv - in our lab) do we need to create if we have databases on multiple servers. For example, if we have 10 servers, do we need 1 Service Principal Account (SPN) or 10 SPNs individual to each server. 

I have raised this question to Oracle support on multiple SRs and the answers differ from each SR. One engineer says all we need is 1 SPN and another engineer says we need 10 SPNs if we have 10 servers. This is confusing. So, now we will test it ourselves. I'll be configuring Kerberos Authentication on another server (linux75.selvapc.com) running Oracle database 12.2.0.1 

For this test, we are not going to change any of the steps from the PART A and PART B of the CMU configuration since that will complete the Kerberos authentication. My sqlnet.ora and krb5.conf will not have any changes from the other server. 

[oracle@linux75 admin]$ more sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/db/122/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#Kerberos Parameters
SQLNET.AUTHENTICATION_SERVICES=(beq,kerberos5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.KERBEROS5_CONF=/u01/db/122/network/admin/krb5.conf
SQLNET.KERBEROS5_CLOCKSKEW=6000
SQLNET.KERBEROS5_CONF_MIT=TRUE
#Following parameter is server-side only
SQLNET.KERBEROS5_KEYTAB=/u01/db/122/network/admin/linux75-database.keytab
[oracle@linux75 admin]$
[oracle@linux75 admin]$ more krb5.conf
[libdefaults]
    default_realm = ORABLISS.COM
    clockskew = 6000
    passwd_check_s_address = false
    noaddresses = true
    forwardable = yes
[realms]
    ORABLISS.COM = {
        kdc = 192.168.56.99:88
    }
[domain_realm]
    ORABLISS.COM = ORABLISS.COM
    .ORABLISS.COM = ORABLISS.COM
    orabliss.com = ORABLISS.COM
    .orabliss.com = ORABLISS.COM
[oracle@linux75 admin]$
You can notice, the only difference will be the keytab file specific to my linux75 server. 

The keytab file is generated as below 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\Users\Administrator> ktpass -princ oracle/linux75.selvapc.com@ORABLISS.com -pass Qwerty123# -mapuser oserv@orabliss.com -crypto ALL -ptype KRB5_NT_PRINCIPAL -out Z:\linux75-database.keytab
Targeting domain controller: DS2019.orabliss.com
Successfully mapped oracle/linux75.selvapc.com to oserv.
Password successfully set!
Key created.
Key created.
Key created.
Key created.
Key created.
Output keytab to Z:\linux75-database.keytab:
Keytab version: 0x502
keysize 66 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x1 (DES-CBC-CRC) keylength 8 (0xfb5e5b384c467904)
keysize 66 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x3 (DES-CBC-MD5) keylength 8 (0xfb5e5b384c467904)
keysize 74 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x17 (RC4-HMAC) keylength 16 (0xc5c6bc2b1d924244af78c13df85a5eee)
keysize 90 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x12 (AES256-SHA1) keylength 32 (0xc9e1bf8b841bd336c3ab4fca0c67f0037b21d5719b4c661606a0745cd823e7f7)
keysize 74 oracle/linux75.selvapc.com@ORABLISS.com ptype 1 (KRB5_NT_PRINCIPAL) vno 11 etype 0x11 (AES128-SHA1) keylength 16 (0x4e84b8fae0598b793cd76a09f5658f29)
PS C:\Users\Administrator>
You can now notice in the above command, I'm still using the -mapuser as oserv@orabliss.com which is our SPN. Now the account will look like below.



Notice the User logon name has changed from ORACLE/linux-8.selvapc.com to oracle/linux75.selvapc.com 

Note: Please see the reason for small case usage for "oracle" in the troubleshooting post. 

Now, we are ready for testing. Transfer the keytab file generated to database server linux75.selvapc.com and create the externally authenticated user as usual. Once all is done, we are ready to connect to the database. 
SQL> create user cmuuser identified externally as 'snagulan@ORABLISS.COM';
grant create session to cmuuser;
grant select on v_$database to cmuuser;
User created.

SQL>
Grant succeeded.

SQL>

Grant succeeded.

SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@linux75 admin]$ okinit snagulan

Kerberos Utilities for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2023 12:53:43

Copyright (c) 1996, 2016 Oracle.  All rights reserved.

Configuration file : /u01/db/122/network/admin/krb5.conf.
Password for snagulan@ORABLISS.COM:
[oracle@linux75 admin]$
[oracle@linux75 admin]$ oklist

Kerberos Utilities for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2023 12:53:50

Copyright (c) 1996, 2016 Oracle.  All rights reserved.

Configuration file : /u01/db/122/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: snagulan@ORABLISS.COM

Valid starting     Expires            Service principal
01/29/23 12:53:48  01/29/23 22:53:48  krbtgt/ORABLISS.COM@ORABLISS.COM
        renew until 01/30/23 12:53:44
[oracle@linux75 admin]$ sqlplus /@orclpdb

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 29 12:53:58 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set heading off
select 'DB_NAME (from v$database) : '||name,
       'CONTAINER_NAME            : '||sys_context('USERENV','CON_NAME'),
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
SQL>        'IDENTIFICATION_TYPE       : '||sys_context('USERENV','IDENTIFICATION_TYPE'),
  2    3    4    5    6    7         --'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
from v$database;  8    9

DB_NAME (from v$database) : ORCL
CONTAINER_NAME            : ORCLPDB
SESSION_USER              : CMUUSER
AUTHENTICATED_IDENTITY    : snagulan@ORABLISS.COM
AUTHENTICATION_METHOD     : KERBEROS
IDENTIFICATION_TYPE       : EXTERNAL
ENTERPRISE_IDENTITY       : snagulan@ORABLISS.COM


SQL> !hostname
linux75.selvapc.com

SQL>
Ok, we have successfully tested the kerberos connection from the newly configured database server. What happens to the previously configured database server since we have now change the SPN User Logon name? Does the CMU connection to the server linux-8.selvapc.com still work or does it break after new server configuration? 

Let's test it. 
[oracle@linux-8 ~]$ okinit snagulan

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2023 23:31:46

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /oracle/db/db19/network/admin/krb5.conf.
Password for snagulan@ORABLISS.COM:
[oracle@linux-8 ~]$ oklist

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2023 23:31:53

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /oracle/db/db19/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: snagulan@ORABLISS.COM

Valid starting     Expires            Service principal
01/29/23 23:31:50  01/30/23 09:31:50  krbtgt/ORABLISS.COM@ORABLISS.COM
        renew until 01/30/23 23:31:47
[oracle@linux-8 ~]$ sqlplus /@odb1

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 29 23:32:12 2023
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Jan 28 2023 23:10:46 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> set heading off
select 'DB_NAME (from v$database) : '||name,
           'CONTAINER_NAME                        : '||sys_context('USERENV','CON_NAME'),
       'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
       'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
       'IDENTIFICATION_TYPE       : '||sys_context('USERENV','IDENTIFICATION_TYPE'),
       'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
       'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
from v$database;SQL>   2    3    4    5    6    7    8    9

DB_NAME (from v$database) : CDB19
CONTAINER_NAME                    : ODB1
SESSION_USER              : SELVA
AUTHENTICATED_IDENTITY    : snagulan@ORABLISS.COM
AUTHENTICATION_METHOD     : KERBEROS_GLOBAL
IDENTIFICATION_TYPE       : GLOBAL EXCLUSIVE
LDAP_SERVER_TYPE          : AD
ENTERPRISE_IDENTITY       : cn=Selvakumar Nagulan,cn=Users,dc=orabliss,dc=com


SQL>
SQL> !hostname
linux-8.selvapc.com

SQL>
We can see the CMU connection still works without any issues. This concludes we just need 1 SPN (in our case, oserv) to configure kerberos or CMU connection for multiple oracle database servers. 

Happy CMUing...!!!
Posted by at
Labels: , , , , ,

2 comments:

  1. HiJanuary 30, 2023 at 6:57 PM

    Woww

    ReplyDelete
  2. AnonymousNovember 7, 2024 at 9:42 AM

    so i hate to be the bearer of bad news on this post but unfortunately that is not CMU at work. yes your connection worked but you created the user identified as externally instead of identified globally and hence the reason your connection was succesful.

    ReplyDelete

Subscribe to: Post Comments (Atom)