Oracle Database and 2FA Using Cisco Duo

Simon Pane

December 10, 2020

Tags: Oracle, Schema, Technical Track, Database, Authentication, 18C, User

Background

Oracle Database connectivity using multi-factor (MFA) or two-factor (2FA) authentication is rather simple to set up, is supported for all Oracle Database versions and editions (including SE2) without requiring any additional licenses, and is easy to use. And Cisco Duo works very well as the 2FA provider. The authentication is on a per-user basis, meaning human users of the database (such as DBAs and developers) can be authenticated using 2FA while application and service accounts are not. The key to Oracle Database 2FA is RADIUS. Oracle Database authenticates using the RADIUS protocol, and 2FA authentication services (including Cisco Duo) provide a software appliance that emulates the RADIUS protocol. Incidentally, Oracle Database has supported RADIUS authentication since Oracle8i! A previous article covered how to implement Oracle Database authentication including two-factor authentication (2FA). Two-factor authentication is usually based on a hardware device or software application that generates a "Time-based One-time Password" (TOTP) code — usually a six-digit numeric code. Reference: https://blog.pythian.com/oracle-database-and-two-factor-authentication-2fa/. The basics of how Oracle Database can leverage 2FA, and how RADIUS primary and secondary authentication works, are covered in that article. To reiterate at a high level:

The database user password authentication (including password complexity and management) is offloaded from the database to another directory service such as Microsoft Active Directory or an LDAP service.
Oracle Database is capable of authenticating against RADIUS servers as an external authentication service.
A RADIUS server is used to bridge the various authentication technologies, including the directory service and the 2FA service.

While the previous article covered implementing using FreeRADIUS as the RADIUS server, a more common commercial application is Cisco Duo Security, integrated with primary authentication against Microsoft Active Directory. This article covers how to configure Oracle Database authentication and 2FA using Cisco Duo Security. Thanks to the " Duo Authentication Proxy" this configuration is quite simple to implement. The end result is:

Database users can access the Oracle Database using their Microsoft Activity Directory username and password for primary authentication.
Duo Security provides secondary authentication via a TOTP generated from their mobile app or text message (used for "concatenated" 2FA) or a push notification (for "out-of-bound" / OOB 2FA).
Oracle Database "password-authenticated" accounts (i.e. for applications and / or database service users) can continue to be used concurrently.

Most likely you already have one, or many, Duo Authentication Proxies in your environment. In this case you can skip ahead to the Oracle Database and user configuration sections. For those setting up from scratch (for example, in your own DBA lab environment), the next sections cover the Duo Authentication Proxy setup to allow DBAs to implement from start to finish in their own "lab environment."

Architecture Overview

Integrating the Oracle Database with Duo and 2FA is quite simple thanks to the Duo Authentication Proxy. The Proxy is a locally installed software service that emulates a RADIUS server for communications with RADIUS clients. The Proxy service then communicates with Duo Security (a cloud service) via HTTPS (port 443). In this implementation, the Oracle database becomes the RADIUS client. The Duo Authentication Proxy software accepts the request, and performs a primary authentication against the directory service (such as Microsoft Active Directory), then a secondary authentication via Duo Security's cloud service. This can include 2FA options including a code generated from a user's mobile device app or cellular push notifications. Consequently, the architecture becomes:

Duo Security Service Configuration

The complete Cisco Duo Security service setup and configuration is beyond the scope of this article. Most organizations implementing the service will already have this in place. Cisco Duo Security has various service tiers including a limited free tier which is perfectly fine for DBA testing. To set up your free tier, first sign up with Cisco Duo. From the Duo Admin Panel add a new "Application" via the Protect an Application button of type "RADIUS":

Basic setup is well documented in the Duo documentation: https://duo.com/docs/radius. Provide (scroll-down to "Settings") the protected application with a meaningful name such as ORACLE-RADIUS:

Also consider the "Simple" Username normalization option. This will allow database users to be created in any of the formats as shown in the example above. After configuring the protected application attributes as desired, record:

The "Integration key"
The "Secret key"
The "API hostname"

For example:

Installing the Duo Authentication Proxy

The Duo Authentication Proxy is simple to set up in a local server or virtual machine. Again, the requirements are not demanding; a CentOS 7 or Oracle Linux 7 VM with 1 vCPU, 200 MB of disk space, and 2GB RAM will likely suffice. The Duo documentation covers the requirements and setup well: https://duo.com/docs/radius#install-the-duo-authentication-proxy Summarizing the installation commands (run a " root"): Install necessary prerequisite packages:

yum -y install wget gcc make libffi-devel perl zlib-devel
 yum -y update

Download and install the latest version of the Authentication Proxy software, following the prompts or customizing as required:

wget https://dl.duosecurity.com/duoauthproxy-latest-src.tgz
 tar xzf duoauthproxy-latest-src.tgz
 cd duoauthproxy-*/
 make
 cd duoauthproxy-build
 ./install

Note, the installer creates an OS user and group for running the service. Adjust the Linux firewall (if being used):

firewall-cmd --permanent --zone=public --add-port=1812/udp
 firewall-cmd --permanent --zone=public --add-port=1813/udp
 firewall-cmd --reload

Configuring the Duo Authentication Proxy

First, create an Active Directory service account that Duo will use to search AD. (Or ask your Active Directory / Windows Server Administrator to do this for you). This user needs no additional AD privileges. NOTE: In this article STAGECOACH.NET is a sample / lab Active Directory domain. It has nothing to do with Oracle or Cisco Duo - substitute with your own domain as applicable. The user can be created through the "Active Directory Users and Computers" GUI or via command line from a PowerShell window on a Domain Controller. Example:

New-ADUser `
  -Name "duo-proxy-service" `
  -UserPrincipalName "duo-proxy-service@stagecoach.net" `
  -DisplayName "Duo Proxy Service" `
  -Description "Allow Duo RADIUS Proxy to search the domain." `
  -Path "CN=Managed Service Accounts,DC=stagecoach,DC=net" `
  -ChangePasswordAtLogon $false `
  -PasswordNeverExpires $true `
  -CannotChangePassword $true `
  -Enabled $true `
  -AccountPassword(Read-Host -AsSecureString "Initial Password:")
 
 dsquery user -name duo-proxy-service

Example output:

The Duo Authentication Proxy settings are managed in a configuration file. For Linux, the file location is: /opt/duoauthproxy/conf/authproxy.cfg:

# ls -l /opt/duoauthproxy/conf/authproxy.cfg
 -r--------. 1 duo_authproxy_svc root 1045 May 22 21:26 /opt/duoauthproxy/conf/authproxy.cfg

The configuration is broken into sections identified with a header in square brackets. The configuration file needs to be adjusted as follows ( Note: redaction with stars is in article only — actual configuration files do not have any redaction of sensitive fields but the configuration file can be secured as per the Cisco Duo documentation). Add an " [ad_client]" section with specifics on how Duo will communicate with Microsoft Active Directory. Specify the AD host server by FQDN or IP address and specify the AD query account previously created (or provided to you by your AD Administrator). The seach_dn is the starting point to query in AD (almost like a query predicate). Example:

[ad_client]
 host=10.0.1.10
 service_account_username=duo-proxy-service
 service_account_password=****************
 search_dn=DC=stagecoach,DC=net

For multiple Active Directory Domain Controllers, list the hostnames or IP addresses using host_2, host_3, etc. Then specify the RADIUS service specific values, comprised of three sections:

The Duo Security application Integration key, Secret key, and API hostname previously obtained.
RADIUS client details, such as the IP address or CIDR block of the connecting databases, and the RADIUS secret that will be used for secure communication from the database (acting as the RADIUS client) to the database.
A reference (or pointer) to the previous "[ad_client]" section that provides the AD primary authentication details.

Adding all of the above into a " [radius_server_auto]" section:

[radius_server_auto] ikey=DIY**************36O
 skey=acsM7******************************wIlnn
 api_host=api-68ef9d32.duosecurity.com
 radius_ip_1=10.0.1.0/16
 port=1812
 radius_secret_1=secretoracle
 failmode=secure client=ad_client

Hence, the full file content becomes:

# cat /opt/duoauthproxy/conf/authproxy.cfg
 ; Complete documentation about the Duo Auth Proxy can be found here:
 ; https://duo.com/docs/authproxy_reference
 
 ; MAIN: Include this section to specify global configuration options.
 ; Reference: https://duo.com/docs/authproxy_reference#main-section
 ;[main]
 
 ; CLIENTS: Include one or more of the following configuration sections.
 ; To configure more than one client configuration of the same type, append a
 ; number to the section name (e.g. [ad_client2])
 
 [ad_client]
 host=10.0.1.10
 service_account_username=duo-proxy-service
 service_account_password=****************
 search_dn=DC=stagecoach,DC=net
 
 ; SERVERS: Include one or more of the following configuration sections.
 ; To configure more than one server configuration of the same type, append a
 ; number to the section name (e.g. radius_server_auto1, radius_server_auto2)
 
 [radius_server_auto]
 ikey=DIY**************36O
 skey=acsM7******************************wIlnn
 api_host=api-68ef9d32.duosecurity.com
 radius_ip_1=10.0.1.0/16
 radius_secret_1=secretoracle
 failmode=secure
 client=ad_client
 port=1812

Once configured properly, secure and protect the configuration file as a good measure:

chmod 400 /opt/duoauthproxy/conf/authproxy.cfg
 ls -l /opt/duoauthproxy/conf/authproxy.cfg

And lastly, start the proxy service:

/opt/duoauthproxy/bin/authproxyctl start

Optionally check the status of the service. Example:

[root@duoproxy1 ~]# /opt/duoauthproxy/bin/authproxyctl status
 duoauthproxy (pid 2131) is running
 [root@duoproxy1 ~]#

Example output showing startup validations:

# /opt/duoauthproxy/bin/authproxyctl start
 Running a validation of your configuration...
 [info] Testing section 'ad_client' with configuration:
 [info] {'host': '10.0.1.10',
  'search_dn': 'DC=stagecoach,DC=net',
  'service_account_password': '*****',
  'service_account_username': 'duo-proxy-service'}
 [info] There are no configuration problems
 [info] -----------------------------
 [info] Testing section 'radius_server_auto' with configuration:
 [info] {'api_host': 'api-68ef9d32.duosecurity.com',
  'client': 'ad_client',
  'failmode': 'safe',
  'ikey': 'DIY**************36O',
  'port': '1812',
  'radius_ip_1': '10.0.1.0/16',
  'radius_secret_1': '*****',
  'skey': '*****[40]'}
 [info] There are no configuration problems
 [info] -----------------------------
 [info] SUMMARY
 [info] No issues detected
 
 The results have also been logged in /opt/duoauthproxy/log/connectivity_tool.log
 #

Oracle Database Server Configuration

The Oracle Database configuration is rather straightforward and is a one-time setup. Similar to other "strong authentication" configurations, the key settings are in the SQLNET.ORA file. First, on the Oracle Database server as the " oracle" (or Oracle software owner) user, save the "radius_secret" value to a file:

mkdir -p ${ORACLE_HOME}/network/security
 echo "secretoracle" > ${ORACLE_HOME}/network/security/radius.key
 chmod 600 ${ORACLE_HOME}/network/security/radius.key

Then add the required entries to the SQLNET.ORA file and verify the file (add the appropriate FQDN or IP address for the Duo Authentication proxy server):

cat << EOF >> ${ORACLE_HOME}/network/admin/sqlnet.ora
 SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,RADIUS)
 SQLNET.RADIUS_AUTHENTICATION=10.0.1.16
 SQLNET.RADIUS_AUTHENTICATION_PORT=1812
 SQLNET.RADIUS_SECRET=${ORACLE_HOME}/network/security/radius.key
 EOF

Note: It's critical that SQLNET.AUTHENTICATION_SERVICES includes " RADIUS." You can include other parameters as desired, such as the RADIUS timeout and retries. Or for higher availability and redundancy, the SQLNET.RADIUS_ALTERNATE parameter to specify a backup Duo Authentication Proxy Server should the primary become unavailable. Ensure that the database initialization parameters are properly set:

sqlplus -s / as sysdba << EOF
 alter system set os_authent_prefix='' scope=spfile sid='*';
 alter system set remote_os_authent=false scope = spfile sid='*';
 shutdown immediate
 startup
 --alter pluggable database all open;
 EOF

Add finally, create a test user in the database (presuming the user is already set up in the Duo Admin panel including initialization for a TOTP or with the Duo mobile app) with EXTERNAL authentication. Example:

SQL> create user "simon@stagecoach.net" identified externally;
 
 User created.
 
 SQL> grant connect to "simon@stagecoach.net";
 
 Grant succeeded.
 
 SQL>

SQL> select username, password, account_status, external_name, password_versions from dba_users where oracle_maintained = 'N';
 
 USERNAME PASSWORD ACCOUNT_STATUS EXTERNAL_NAME PASSWORD_VERSIONS
 ------------------------ -------------- -------------- ---------------- -----------------
 simon@stagecoach.net EXTERNAL OPEN
 SCOTT OPEN 11G 12C
 
 SQL>

Whether the username is a short name such as SIMONP or a full Active Directory "User Principal Name" (UPN) such as " simonp@stagecoach.net" is based on the Duo setup. If the "Simple" Username normalization option was chosen, you can use either.

Connection Testing

One of the nice features of Duo Security is that you can use either the TOTP (aka "code") or a push notification to the Duo mobile app. First though, the client side SQLNET.ORA must be properly configured. Essentially, only one parameter needs validation or possible adjustment: SQLNET.AUTHENTICATION_SERVICES. It must include " RADIUS" or be set to " ALL":

[oracle@applinux3 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
 #SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,RADIUS)
 SQLNET.AUTHENTICATION_SERVICES=(ALL)
 
 [oracle@applinux3 ~]$

The SQLNET.ORA setup is the same regardless of whether you use an Oracle Full Client or Instant Client. If using a TOTP, the Duo format is to append the passcode to the password separated with a comma. This is usually known as "concatenated 2FA." For example: <username>/<password>,<passcode> Here's an example using the TOTP passcode appended to (concatenated to) the password (and simply demonstrating using SQL*Plus as " the application" although it should work just as well with most applications):

SQL> connect "simon@stagecoach.net"/MyPassw0rd,417840@ORCL
 Connected.
 SQL>
 SQL> set heading off
 SQL> select 'DB_NAME : ' || name,
  2 'SESSION_USER : ' || sys_context('USERENV','SESSION_USER'),
  3 'AUTHENTICATION_METHOD : ' || sys_context('USERENV','AUTHENTICATION_METHOD'),
  4 'ENTERPRISE_IDENTITY : ' || sys_context('USERENV','ENTERPRISE_IDENTITY')
  5 from v$database;
 
 DB_NAME : ORCL
 SESSION_USER : simon@stagecoach.net
 AUTHENTICATION_METHOD : RADIUS
 ENTERPRISE_IDENTITY : simon@stagecoach.net
 
 
 SQL>

Note that the password, comma, and TOTP code might have all been provided when prompted — they're explicitly shown in the SQL*Plus "connect" command for illustrative purposes only. If, instead of providing the TOTP code appended to the password, you want to use a push notification to the Duo app on a mobile device (known as "out-of-bound 2FA"), you don't need to make any adjustments to the Oracle credential information. You enter the username and password exactly as you normally would. The difference is, the session will pause during authentication until the push message is accepted, rejected, or the connection times out. (Recall the timeout parameters can be specified in the SQLNET.ORA file.) Consequently, the SQL*Plus session looks like it normally would:

SQL> connect "simonp@stagecoach.net"@ORCL
 Enter password:
 Connected.
 SQL>
 SQL>
 SQL> select 'DB_NAME : ' || name,
  2 'SESSION_USER : ' || sys_context('USERENV','SESSION_USER'),
  3 'AUTHENTICATION_METHOD : ' || sys_context('USERENV','AUTHENTICATION_METHOD'),
  4 'ENTERPRISE_IDENTITY : ' || sys_context('USERENV','ENTERPRISE_IDENTITY')
  5 from v$database;
 
 DB_NAME : ORCL
 SESSION_USER : simon@stagecoach.net
 AUTHENTICATION_METHOD : RADIUS
 ENTERPRISE_IDENTITY : simon@stagecoach.net
 
 
 SQL>

In the above example, what you don't see is that after entering the password when prompted, a Duo push was sent to, and accepted on, a mobile device:

Depending on the exact nature of your application, if using a full UPN username (the format that looks like an email address, although it isn't one), you may need to address how to best enquote the username string. For example, in SQL Developer you need to enclose the username in double quotes:

Troubleshooting

The most common error received with Oracle RADIUS/2FA is ORA-01017:

SQL> connect "simon@stagecoach.net"@ORCL
 ERROR:
 ORA-01017: invalid username/password; logon denied
 
 
 Warning: You are no longer connected to ORACLE.
 SQL>

The source of this error can be:

Case sensitivity of the username if created in double quotes (not related to the RADIUS / Duo setup).
Password is actually incorrect.
TOTP code provided is incorrect or has aged and is no longer valid.
A 2FA push was denied or not accepted in time.
The SQLNET.AUTHENTICATION_SERVICES parameter is incorrectly set in the client's SQLNET.ORA file.
The SQLNET.AUTHENTICATION_SERVICES or SQLNET.RADIUS_* parameters are not properly set in the server's SQLNET.ORA file.
The secret in the key file defined by the server's SQLNET.ORA SQLNET.RADIUS_SECRET parameter is incorrect.

If none of those apply try:

Checking the log files on the Duo Proxy Authentication server (example: /opt/duoauthproxy/log/authproxy.log).
Performing SQLNET tracing on the client and server.

Conclusion

Oracle 2FA (or MFA, if you prefer) authentication using Cisco Duo is actually quite reliable, easy to use and easy to set up. The Oracle Database leverages the RADIUS protocol, and Cisco Duo provides a simple-to-install / manage software appliance that emulates a RADIUS server. Since RADIUS support was added in Oracle8i, the only real questions are: Why isn't this configuration more common and Why haven't Oracle users implemented it sooner?

Insight and analysis of technology and business strategy