Connecting to Oracle Database

Oracle Data Provider for .NET can connect to Oracle Database in a number of ways, such as using a user name and password, Windows Native Authentication, Kerberos, and Transport Layer Security/Secure Sockets Layer. This section describes OracleConnection provider-specific features, including:

• Connecting to Oracle Autonomous Database

• Using Oracle Identity and Access Management

• Using Azure Active Directory

• Connection String Attributes and URLs

• Connection String Builder

• Specifying the Data Source Attribute

• Using WebSocket

• Using Transport Layer Security and Secure Sockets Layer

• Using Secure External Password Store

• Using Kerberos

• Using Windows Native Authentication (NTS)

• Network Data Encryption and Integrity

• Schema Discovery

• Connection Pooling

• Connection Pool Management

• Connection Performance Counters

• Database Resident Connection Pooling and Connection Manager in Traffic Director Mode

• Oracle Multitenant and Pluggable Databases

• Edition-Based Redefinition

• Privileged Connections

• Connection Pooling with OracleCredential

• Password Expiration

• Proxy Authentication

• Dynamic Distributed Transaction Enlistment

• Client Identifier and End-to-End Tracing

Connecting to Oracle Autonomous Database

Oracle Autonomous Database includes several deployment options:

• Oracle Autonomous Database Serverless (ADB-S)

• Oracle Autonomous Database on Dedicated Exadata Infrastructure (ADB-D)

• Oracle Autonomous Database on Exadata Cloud@Customer (ADBC@C)

TLS/SSL must be used to connect to ADB-S. For ADB-D and ADBC@C, use of TLS is optional. Currently, only ADB-S can be used with Oracle Identity and Access Management.

This documentation section focuses on how to connect to ADB-S.

Unmanaged ODP.NET 19.14 and 21.5 and higher supports TLS connections without a wallet.

ODP.NET core and managed drivers 19.13 and 21.4 and higher support TLS connections without a wallet.

ODP.NET Core 19.13.1 or 21.4.1 and higher support token based authentication with Oracle Identity and Access Management (IAM).

Managed ODP.NET 21.6.1 now supports token based authentication with IAM.

Oracle recommends using the latest ODP.NET driver version available on NuGet Gallery or Oracle website when connecting to Oracle ADB-S.

Connecting with a Wallet

The following quick start link has instructions how to download, install, and configure ODP.NET and Oracle Developer Tools for Visual Studio when using a wallet:

Quick Start: Developing .NET Applications for Oracle Autonomous Database

Connecting without a Wallet

When you connect to Oracle ADB-S with ODP.NET, you do not need to deploy the Oracle wallet or the Oracle network configuration files sqlnet.ora or tnsnames.ora with your application. Instead, you supply the data source attribute, a TLS connection string, with the configuration information in the ODP.NET connection.

To use ODP.NET TLS connections, do the following:

1. Obtain managed ODP.NET or ODP.NET Core versions 19.13 or 21.4 or above. For unmanaged ODP.NET, versions 19.14 and 21.5 can be obtained. Lower level versions do not support TLS connections without wallets.

2. Enable TLS connections on your Autonomous Database instance. See Update your Autonomous Database Instance to Allow both TLS and mTLS Authentication for details.

3. After you enable TLS connections, supply a TLS connection string in the ODP.NET data source to connect to your Autonomous Database instance. See View TNS Names and Connection Strings for an Autonomous Database Instance for details on viewing or copying TLS connection strings.

Note:

Older TNS names use a distinguished name and place quotation marks around it. Newer TNS names do not have it. If you store one of these older TNS names in a .NET string, add a backslash escape sequence before each quotation mark (for example, \" ). This allows .NET to recognize the quotation mark as part of the TNS name.

See Also:

• Tips for Connecting .NET Apps to Oracle Autonomous Database

• Autonomous Database Cloud Services Documentation

• Oracle Database Cloud Services Documentation

Using Oracle Identity and Access Management

Oracle identity and access management (IAM) solutions provide secure access to enterprise applications for both cloud and on-premises deployments. Oracle IAM cloud service supports a unified identity across Oracle cloud services, including Oracle ADB-S. This capability allows identity to be propagated to all services Oracle IAM supports. A unified identity makes user management and account management easier for administrators and end users.

IAM users can connect to the Oracle Autonomous Database instance by using either an IAM database password verifier, an IAM token, or a database alternate password for token authentication.

Using the IAM database password verifier is similar to the Oracle Database password authentication process. However, instead of the password verifier (encrypted hash of the password) being stored in the Oracle Autonomous Database, the verifier is instead stored as part of the Oracle Cloud Infrastructure (OCI) IAM user attributes. This option is supported by all ODP.NET clients.

The second connection method, the use of an IAM token for the database, is more modern. The use of token-based access is a better fit for Cloud resources such as Oracle Autonomous Database. The token is based on the strength that the IAM endpoint can enforce. This can be multi-factor authentication, which is stronger than the use of passwords alone. Starting with versions 19.13.1 and 21.4.1, ODP.NET Core supports this new connection method. This functionality was added to managed ODP.NET in version 21.6.1. This will utilize native OCI Authentication primitive, specifically OCI supported tokens for authenticating OCI users when they logon to a database. By integrating with OCI IAM for authentication, Oracle databases in OCI will provide seamless identity integration with OCI services.

The third connection method is the database password for IAM token authentication. This method is different from using the IAM database password verifier since it is using a database alternate password for token authentication. This connection method is also called token-based authentication using password. ODP.NET Core and Managed ODP.NET support this feature starting with version 21.6.1.

The fourth connection method is IAM single sign-on token (SSO) authentication. Starting with ODP.NET 23ai, Oracle IAM single sign-on is supported with the managed and core provider types. ODP.NET will automatically handle IAM token retrieval and management. Oracle Database 19c (19.16), 23ai, and higher versions support IAM SSO.

See Also:

See also Use Identity and Access Management (IAM) Authentication with Autonomous Database in Using Oracle Autonomous Database Serverless.

Configuring a Client Connection Using an IAM Database Password Verifier

After you have configured the authorization needed for the IAM user, this user can log in without additional configuration.

The application provides the IAM user name and IAM database password, not the Oracle Cloud Infrastructure (OCI) console password, using any ODP.NET driver type. The only constraint is that the ODP.NET driver must have support for Oracle Database 12c passwords verifiers. Earlier password verifiers are not supported with IAM. No special client or tool configuration is needed for the IAM user to connect to the Oracle Autonomous Database instance.

Configuring a Client Connection Using Token

ODP.NET supports the IAM token, which is a Signature token represented in standard JSON Web Token (JWT). Signature Token – also called Proof of Possession (PoP) token – is associated with a private key known to the client. The client sends a signature and token to the protected resource. The protected resource verifies the signature along with the token itself.

For passing IAM token to Oracle Autonomous Database, the following options are supported:

• Use of token file: For existing applications, use Oracle Cloud Infrastructure (OCI) command line interface (CLI) to request and store the database token in the file system, and configure ODP.NET to use the token file.

• Use of API: Modify application to request database token from IAM and pass the database token to the database client through ODP.NET API.

ODP.NET presents the database token when connecting to a database. The database requests the public key from IAM and the database validates the database token. If the token is valid, the database requests the IAM groups that the user is a member. The database reviews the local schema mappings to find which global schema the IAM user will have access to, exclusive or shared. If no schema mapping exists, the user will not be allowed to access the database.

Configuring Client Connection to use Token File

The OCI CLI is available to request database tokens from IAM. When the OCI CLI tool is used to request tokens, it copies the token and private key to the default directory on the local machine or a custom directory can be specified. On Windows operating systems, the default directory is based on the USERPROFILE environment variable (i.e. $USERPROFILE/.oci/db-token). On Linux and macOS operating systems, the default directory is based on the HOME environment variable (i.e. $HOME/.oci/db-token).

To use token file for authentication:

1. Set ODP.NET User Id to “/” in the connection string and leave the password empty.

2. Set TOKEN_AUTH parameter to OCI_TOKEN. This parameter can be configured at various levels like TNS descriptor, EZConnect string, sqlnet.ora file or application configuration file. This can also be configured programmatically either at connection level through the OracleConnection.TokenAuthentication property or at process level through the OracleConfiguration.TokenAuthentication property.

3. If using a non-default token location, set the token file directory. More details on setting a specific token file location is discussed in the “Use of ODP.NET API to pass the Token” section.

When database token usage is enabled, all other external authentication methods, such as Kerberos, Windows authentication, and TLS cannot be used. However, TLS to encrypt the client-server connection can be used – and must be used when using IAM tokens.

See Also:

• Authenticating and Authorizing IAM Users for Oracle Autonomous Databases for all the steps needed to get the token file.

• OCI Command Line Interface

Use of ODP.NET API to pass the Token

Applications can use OCI SDK to get database token for IAM user and pass the database token along with private key to ODP.NET through the API.

The database token has attributes within the token, some of which are worth noting, which will be covered in more detail later:

• exp attribute within the token represents the expiry time of the token.

• sub attribute within the token represents the IAM user of the token.

The OracleConnection.AccessToken property can be set to the OracleAccessToken object. The OracleAccessToken object can be constructed by providing both the signature token and private key, which are required to connect via IAM.

If OracleConnection.AccessToken property is not supplied by the application and the TOKEN_AUTH parameter is set to OCI_TOKEN, then ODP.NET will look for the signature token and private key files in the default directory. Applications can override this default directory by setting the TOKEN_LOCATION parameter. This parameter can also be configured at various levels like TNS descriptor, EZConnect string, sqlnet.ora file, or application configuration file. This parameter can also be configured programmatically either at the ODP.NET connection level through the OracleConnection.TokenLocation property or at process level through the OracleConfiguration.TokenLocation property.

The following table shows how ODP.NET will use IAM when a signature token is supplied and when TOKEN_AUTH / TokenAuthentication property is set.

Table 3-2 ODP.NET Authentication using IAM and Signature Token

Application Supplied Oracle Access Token	TOKEN_AUTH / TokenAuthentation Setting	PASSWORD_AUTH / PasswordAuthentation Setting	ODP.NET Authentication
Yes	OCI_TOKEN / OracleTokenAuth.OciToken	NOT SET / OraclePasswordAuth.PasswordVerifier (Default)	IAM via application SUPPLIED TOKEN
Yes	NOT SET / OracleTokenAuth.Disabled (default)	NOT SET / OraclePasswordAuth.PasswordVerifier (default)	IAM via application SUPPLIED TOKEN
No (default)	OCI_TOKEN / OracleTokenAuth.OciToken	NOT SET / OraclePasswordAuth.PasswordVerifier (Default)	IAM via file SUPPLIED TOKEN
No (default)	NOT SET / OracleTokenAuth.Disabled (default)	NOT SET / OraclePasswordAuth.PasswordVerifier (Default)	External Authentication, such as Kerberos, Windows, and TLS/SSL
Yes	OCI_TOKEN / OracleTokenAuth.OciToken	OCI_TOKEN / OraclePasswordAuth.OciToken	IAM via application SUPPLIED TOKEN
Yes	NOT SET / OracleTokenAuth.Disabled (default)	OCI_TOKEN / OraclePasswordAuth.OciToken	IAM via application SUPPLIED TOKEN
No (default)	OCI_TOKEN / OracleTokenAuth.OciToken	OCI_TOKEN / OraclePasswordAuth.OciToken	If userid=/, IAM via file supplied token. Else if TokenAuthentication set programmatically, then error. In all other cases, token from REST service.
No (default)	NOT SET / OracleTokenAuth.Disabled (default)	OCI_TOKEN / OraclePasswordAuth.OciToken	Token From REST Service if userid/password set or userid= / with SEPS enabled. In all other cases, External Authentication.

ODP.NET applications using the API to provide signature token and private key are recommended to register with the RefreshAccessToken event exposed on the OracleAccessToken object to receive a callback when token is about to expire. The .NET event provides a convenient way for applications to “refresh” the signature token and the private key before they expire.

ODP.NET triggers the token refresh callback 60 seconds before it expires. It is the application’s responsibility to ensure that ODP.NET always has a valid token for authentication when creating new connections to the database. Please note that the callback will be called only once for an authenticated token.

After the callback provides the refreshed token and private key, all new connections start using the refreshed token for authentication. However, the token expiry has no impact on existing connections checked out from or within the connection pool. If for some reason, applications fail to provide a valid refreshed token and private key through the callback, it can use OracleConnection object’s OpenWithNewToken() method to open a connection by passing a new signature token and private key.

It's important to note that already established idle connections will be dispensed even when the token has expired. The newly supplied signature token will be used only to create new connections. If the supplied signature token is invalid or expired, applications will receive an error during the Open() method invocation on the OracleConnection object.

IAM Token Functionality Requirements

With ODP.NET Core NuGet packages, applications must target .NET 5.0 or higher to use IAM tokens.

Managed ODP.NET does not have any unique .NET assembly version dependencies to use IAM tokens.

Sample Code

Here is ODP.NET sample code showing how to use OracleAccessToken to establish a connection and how to propagate the refreshed token through the OracleRefreshAccessTokenEventArgs class. This sample code works with managed ODP.NET and ODP.NET Core.

// C#
using System;
using System.IO;
using Oracle.ManagedDataAccess.Client;

class RefreshDbTokenSample
{
	// Update this path based from where token and private key would be read.
	private static string tokenPath = @"C:\token_dir";

	static void Main(string[] args)
	{
		// Create token and private key file path.
		string tokenFile = Path.Combine(tokenPath, "token");
		string privateKeyFile = Path.Combine(tokenPath, "oci_db_key.pem");

		// Read token and private key contents.
		char[] dbToken = File.OpenText(tokenFile).ReadToEnd().ToCharArray();
		char[] privateKey = File.OpenText(privateKeyFile).ReadToEnd().ToCharArray();

		// Create OracleAccessToken
		OracleAccessToken accessToken = new OracleAccessToken(dbToken, privateKey);

		// Set the token refresh call back
		accessToken.RefreshAccessToken += new OracleRefreshAccessTokenEventHandler(OnRefreshAccessToken);

		// establish a connection
		string constr = "user id=/;data source=oracle";

		// Create connection object
		OracleConnection con = new OracleConnection(constr);

		// Set accessToken to the connection before opening connection.
		con.AccessToken = accessToken;

		// Open connection now.
		con.Open();

		Console.WriteLine("Connected using DB Token Authentication");

		con.Dispose();
	}

	public static void OnRefreshAccessToken(
				OracleRefreshAccessTokenEventArgs eventArgs)
	{
		// Application code to get Refreshed DB token and private key 
		RefreshToken(out char[] refreshedToken, out char[] refreshedPrivateKey);

		// Set the refreshed DB token and private key to eventArgs
		eventArgs.DbToken = refreshedToken;
		eventArgs.PrivateKey = refreshedPrivateKey;
	}

	public static void RefreshToken(out char[] refreshedToken, out char[] refreshedPrivateKey)
  {
		refreshedToken = null;
		refreshedPrivateKey = null;

		// TODO: Add code to refresh the token.
	}
}

Configuring a Client Connection Using Database Alternate Password for Token Authentication

The database alternate password connection method uses a username and password specific for IAM token-based authentication. This set of credentials is separate from the more common database username and password.

To use this feature, the PASSWORD_AUTH parameter must be set to OCI_TOKEN.

IAM exposes a REST endpoint for clients to request database bearer tokens. ODP.NET makes calls to request tokens from the endpoint via TLS 1.2 or higher. The driver specifies a username, password, oci iam url, tenant Oracle Cloud Identifier (OCID), a database compartment identifier (optional), and a database identifier (optional) in the request. If a database identifier is specified, then the database compartment identifier becomes mandatory. A bearer token is returned to ODP.NET in the response. The driver then sends the returned bearer token to authenticate with the database.

ODP.NET caches the token for reuse when additional connection requests occur. It holds onto the token until its expiration. The application will need to refresh the token before it expires to be able to make connections.

ODP.NET alternate password credentials can be stored securely in a Secure External Password Store (SEPS) wallet for application use.

Comparing IAM Token Authentication Methods

The following table identifies the differences and similarities among the IAM token authentication options.

App provides Database Token and Private Key	Database Token and Private Key through File	Alternate User Credentials for Token Authentication
Username must be set to “/” in the connection string.	Username must be set to “/” in the connection string.	Username and Password in ODP.NET connection string represent alternate credentials.
-	-	SEPS can store credentials. When SEPS is enabled, the username and password is retrieved first. Then, the parameter, PASSWORD_AUTH, determines whether database access occurs via username/password or token-based authentication.
Setting OracleCredential results in an error when opening a connection.	Setting OracleCredential results in an error when opening a connection.	OracleCredential can be set with alternate username and password. The username/password is provided through the connection string or OracleCredential.
No configuration parameter	TOKEN_AUTH=OCI_TOKEN	PASSWORD_AUTH=OCI_TOKEN
No configuration parameter	TOKEN_LOCATION can also be set to override default directory location of token and private file. You can set both TOKEN_AUTH and PASSWORD_AUTH to OCI_TOKEN on the same connection. In this scenario, if the username is set to “/”, then file-based token authentication is used. Otherwise, alternate password is used.	OCI_IAM_URL and OCI_TENANCY must be set. OCI_COMPARTMENT and OCI_DATABASE can be set to provide specific compartment identifier and database identifier, respectively. If OCI_DATABASE is set, then, OCI_COMPARTMENT is mandatory. You can set both TOKEN_AUTH and PASSWORD_AUTH to OCI_TOKEN on the same connection. In this scenario, if the username is set to “/”, then file-based token authentication is used. Otherwise, alternate password is used.
OracleAccessToken must be set on connection with proper database token and private key before opening.	-	-
Database token provided by the application must be Signature token.	Database token read from the file must be Signature token.	Database token received from IAM is bearer token.
ODP.NET sends the following information to the database upon opening a connection: database token header Signature generated by signing the header using private key	ODP.NET reads the database token and private key from the files and sends the following information to the database for authentication upon opening a connection; database token header Signature generated by signing the header using private key	Upon opening a connection, ODP.NET uses the username/password to request the database token from IAM and then sends that token to the database for authentication.

IAM Single Sign-On

Managed ODP.NET and ODP.NET Core support Oracle IAM single sign-on (SSO), allowing user identity to be propagated across Oracle databases and cloud services, including federated users. SSO is easier for end users as they only need to log on once to access all the services that use an Oracle IAM identity. It is also easier for .NET developers as ODP.NET performs access token management automatically. The application is freed from dealing with the database token and private key.

When ODP.NET is given the Oracle IAM authentication parameters, it acquires the IAM access token to establish the database connection and all subsequent connection requests. The token is cached on a per connection pool basis. When the token is about to expire, ODP.NET automatically retrieves a new access token.

ODP.NET IAM SSO requires Oracle.ManagedDataAccess.Oci package in NuGet Gallery. That package uses the Oracle Cloud Identity Data Plane Service (OCI.DotNetSDK.Identitydataplane NuGet package) and Oracle Cloud Identity (OCI.DotNetSDK.Identity NuGet package), which is automatically downloaded as a dependency.

To configure IAM SSO, ODP.NET requires IAM credential and connection information. It can use the authentication parameters in the table below to connect.

Table 3-3 ODP.NET OCI IAM Parameters

OCI IAM Parameter	Description
Token authentication	Specifies the authentication flow for retrieving an OCI IAM access token
Configuration file	Specifies the configuration file location for retrieving OCI IAM authentication values, such as user id and tenancy OCID
Profile	Specifies the profile ODP.NET uses from the configuration file
Compartment	Specifies the compartment Oracle Cloud ID for the database
Database	Specifies the database Oracle Cloud ID

For all OCI IAM authentication flows, only the token authentication parameter is required. All other parameters are either optional or not needed. The table below shows all the IAM authentication flows ODP.NET supports and the parameters available.

Table 3-4 ODP.NET OCI IAM Authentication Flows

OCI IAM Authentication Flows	Required Parameters
API Key	Token authentication set to OciApiKey Configuration file (optional) Profile (optional) Compartment OCID (optional) Database OCID (optional)
Instance Principal	Token authentication set to OciInstancePrincipal Compartment OCID (optional) Database OCID (optional)
Resource Principal	Token authentication set to OciResourcePrincipal Compartment OCID (optional) Database OCID (optional)
Delegation Token	Token authentication set to OciDelegationToken Compartment OCID (optional) Database OCID (optional)
Interactive	Token authentication set to OciInteractive Configuration File (optional) Profile (optional) Compartment OCID (optional) Database OCID (optional) Username (optional placeholder)
Default	Token authentication set to OciDefault

ODP.NET can retrieve the OCI IAM parameter values from any of the following configuration locations. The parameter values in the locations higher in the table have precedence over values in locations listed lower.

Table 3-5 ODP.NET OCI IAM Configuration Locations

Configuration Location	Available Parameters
OciTokenAuthentication class via OracleConnection	ConfigurationFile Profile Compartment Database OracleConnection.TokenAuthentication
Tnsnames.ora file or Easy Connect Plus	OCI_CONFIG_FILE OCI_PROFILE OCI_COMPARTMENT OCI_DATABASE TOKEN_AUTH
OracleConfiguration class	OciConfigurationFile OciProfile OciCompartment OciDatabase TokenAuthentication
App/Web configuration file	OCI_CONFIG_FILE OCI_PROFILE OCI_COMPARTMENT OCI_DATABASE TOKEN_AUTH
Sqlnet.ora	OCI_CONFIG_FILE OCI_PROFILE OCI_COMPARTMENT OCI_DATABASE TOKEN_AUTH

ODP.NET OCI IAM SSO can be programmatically configured via the OciTokenAuthentication class, OracleConnection UseOciTokenAuthentication method, and OracleConfiguration class. The provider alters the OciTokenAuthentication object to read only after the connection is opened to ensure no OCI token authentication settings are changed for the pool's lifetime. With the settings locked, the token is cached for re-use when new pooled connections are requested.

ODP.NET OCI IAM SSO code sample:

	OracleConnection conn = new OracleConnection(constr);
	conn.TokenAuthentication = OracleTokenAuth.OciApiKey;
	OciTokenAuthentication tokenconfig = new OciTokenAuthentication
	{
		ConfigurationFile = "<CONFIGURATION FILE>",
		Profile = "<PROFILE>",
	       Compartment = "<COMPARTMENT OCID>",
	       Database = "<DATABASE OCID>"
	}
	conn.UseOciTokenAuthentication(tokenConfig);
	conn.Open();
	conn.Close();

Alternatively, ODP.NET OCI IAM SSO can be configured without any ODP.NET code changes using tnsnames.ora, Easy Connect Plus, sqlnet.ora, and/or .NET configuration files.

IAM Interactive Authentication Flows

For interactive authentication flows, when the user and tenancy name values are the same for two different data sources, the user will only need to login once even if the connections belong to different connection pools. The reason why is that the same token is used for all these connections and ODP.NET caches it. The app can direct ODP.NET to clear its cache of all its session tokens so that they aren't used with new connections or new pools. ClearInteractiveTokenCache static method will clear access token cached in existing connection pools, that is, pools which are created with interactive authentication flows. Only existing opened connections will continue to work.

Code sample: ODP.NET OCI IAM Single Sign-on Connection Using Interactive Authentication Flow

string username = "[e-mail]";
string constr = $"user id=<USERNAME>; data source = db-descriptor1";
OracleConnection conn = new OracleConnection(constr);
conn.TnsAdmin = @"C:\DB1\TnsAdmin\";
conn.TokenAuthentication = OracleTokenAuth.OciInteractive;
OciTokenAuthentication obj1 = new OciTokenAuthentication()
{
	ConfigurationFile = @"C:\oci_config\config"
};
conn.UseOciTokenAuthentication(obj1);
conn.Open();
Console.WriteLine("Connected to DB 1.");
conn.Close();
  
string constr2 = $"user id=<USERNAME>; data source = db-descriptor2 ";
OracleConnection conn2 = new OracleConnection(constr2);
conn2.TnsAdmin = @"C:\DB2\TnsAdmin\";

conn2.TokenAuthentication = OracleTokenAuth.OciInteractive;
OciTokenAuthentication obj2 = new OciTokenAuthentication()
 {
	ConfigurationFile = @"C:\oci_config\config"
 };
conn2.UseOciTokenAuthentication(obj2);
conn2.Open();
Console.WriteLine("Connected to DB 2.");
conn2.Close();

Using Azure Active Directory

Starting with ODP.NET 21.7 and 19.15.1, ODP.NET supports Microsoft Entra ID, also known as Azure Active Directory (AAD), authentication when connecting to Oracle Database. ODP.NET will then use an access token to access the database instead of a username and password.

This feature benefits applications and services that use AAD for centralized user authentication with Oracle database. Those services can include Azure and Microsoft 365-based cloud services, such as Microsoft Power BI service, that rely on AAD for user management.

Using token-based authentication is more secure and simpler for the end user. It becomes unnecessary to specify credentials each time the user accesses a resource. Moreover, the resource never needs to handle and manage individual user credentials.

AAD follows the OAuth 2.0 user authorization standard. OAuth 2.0 provides a means of obtaining an access token, then using that access token for accessing resources, such as Oracle database.

AAD is supported in the core, managed, and unmanaged ODP.NET drivers. It requires Oracle Autonomous Database or Oracle Database 19.16 or higher.

Managed and unmanaged ODP.NET require NET Framework 4.5.2 or higher for Azure Active Directory support. ODP.NET Core does not have a runtime requirement more specific than the general component system requirements to support Azure Active Directory.

Starting with ODP.NET version 23 and Oracle Database 23ai, AAD single sign-on is supported with managed ODP.NET and ODP.NET Core. ODP.NET will automatically handle AAD token retrieval and management.

Connecting Using Azure AD Tokens

For an ODP.NET application using the managed or core provider to authenticate with AAD using a file-based access token, the TOKEN_AUTH parameter or OracleConfiguration.TokenAuthentication property must be set to OAUTH. The token’s location is then specified in the TOKEN_LOCATION parameter or TokenLocation property. The token location must be specified explicitly when using OAUTH. Otherwise, an error will occur. For OAUTH, this location can be the directory where the file “token” is, or the full path that includes a file name.

For an ODP.NET application to authenticate with AAD with an access token, none of these configuration steps is necessary. The access token is provided through ODP.NET APIs.

Developers construct an OracleAccessToken object to provide the access token via the OracleConnection.AccessToken property. If the application sets up a RefreshAccessToken event handler, ODP.NET will invoke it 60 seconds before the access token expires. The OracleRefreshAccessTokenEventArgs parameter provides a refreshed access token in the callback method.

The OracleAccessToken token can only be updated by calling OpenWithNewToken method or by setting the OracleRefreshAccessTokenEventArgs.Token property upon a .NET callback invocation.

For ODP.NET connection pooling, the OracleAccessToken object will be used to distinguish connection pools, not the actual token as they can expire and be refreshed. Only new connections need to use a refreshed access token. Existing pooled connections do not depend on the access token and will continue to work. Applications should always specify the same OracleAccessToken object on OracleConnection to ensure the same connection pool is used.

TOKEN_AUTH and TOKEN_LOCATION can also be set in the tnsnames.ora and sqlnet.ora files. To use OAuth with unmanaged ODP.NET, set your token values in these files as there are no equivalent unmanaged ODP.NET APIs.

Sample Code: Using ODP.NET Azure Active Directory Authentication

// This is a simple ODP.NET, Core Driver application that connects to an Oracle Autonomous Database
// using a token obtained from Azure Active Directory (Azure AD).

// Azure.Identity can be obtained through NuGet Gallery.  
// It will include the Azure.Core and Azure.Identity namespaces.
using System;
using System.Threading;
using Azure.Core;
using Azure.Identity;
using Oracle.ManagedDataAccess.Client;

namespace ConnectToOracleUsingAccessToken
{
  class Program
  {
    static void Main()
    {
      try
      {
        // Retrieve an access token from Azure AD.
        string token = GetAccessToken();

        // Create an instance of an OracleAccessToken.  The access token needs to
        // be passed to the OracleAccessToken constructor as array of characters.
        var oracleAccessToken = new OracleAccessToken(token.ToCharArray());

        // Create an instance of an OracleConnection object.
        // The developer must provide the appropriate data source setting.
        var connection = new OracleConnection("User Id=/;Data Source=<oracle>");

        // tnsnames.ora, sqlnet.ora, and cwallet.sso must reside in the same
        // directory as the application executable.  These files can be downloaded
        // from Oracle Cloud for the Oracle Autonomous DB instance.
        connection.TnsAdmin = @".\";

        // Assign the OracleAccessToken to the AccessToken property on the
        // OracleConnection object.
        connection.AccessToken = oracleAccessToken;

        // Open the connection.
        connection.Open();

        // If Open() fails, it will throw an exception.
        Console.WriteLine("Open success.");

        // Dispose the OracleConnection object.
        connection.Dispose();
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex);
      }
    }

    // Retrieves an Azure AD access token through the
    // Service Principal Auth flow using a client secret.
    static string GetAccessToken()
    {
      // The developer must configure the Azure AD parameters below.
      string clientId = "<client Id of app registration in Azure AD>";
      string tenantId = "<tenant Id of Azure AD>";
      string clientSecret = "<secret value of app registration in Azure AD>";
      string scope = "<scope of DB registration in Azure AD>";

      // Create a TokenRequestContext object.
      var tokenRequestContext = new TokenRequestContext(new[] { scope });

      // Create a ClientSecretCredential object.
      var credentials = new ClientSecretCredential(tenantId, clientId, clientSecret);

      // Get the access token from Azure AD.
      AccessToken accessToken = credentials.GetToken(tokenRequestContext, default(CancellationToken));

      // Return the access token.
      return accessToken.Token;
    }
  }
}

See Also:

More Azure Active Directory ODP.NET sample code can be found on Oracle .NET GitHub site:

https://github.com/oracle/dotnet-db-samples/tree/master/samples/azure-active-directory

Connecting Using Azure AD Single Sign-On

ODP.NET supports AAD single sign-on (SSO), allowing user identity to be propagated across Oracle databases and cloud services. SSO is easier for end users as they only need to log on once to access all the services that use an AAD identity. It is also easier for .NET developers as ODP.NET performs access token management automatically.

When ODP.NET is given the AAD authentication parameters, it acquires the AAD access token to establish the database connection and all subsequent connection requests. The token is cached on a per connection pool basis. When the token is about to expire, ODP.NET automatically retrieves a new access token.

ODP.NET AAD SSO requires Oracle.ManagedDataAccess.Azure package in NuGet Gallery. That package uses Azure .NET SDK (Azure.Identity NuGet package), which is automatically download as a dependency.

To configure AAD SSO, ODP.NET requires Azure credential and connection information. It uses the parameters in the following table to connect.

Parameters	Description
Authentication method	This specifies the authentication flow for getting an Azure AD bearer access token.
DB application ID URI	This specifies the protected resource identifier on Azure AD for which client application requests the access token.
Tenant ID	This specifies tenant identifier under which applications (client app or database) are registered. The tenant is an entity or a logical group of cloud resources assigned to an individual or an organization.
Client ID	Client ID is the identifier of an application registered on Azure AD.
Client secret	Client secret is the credential of a resource in Azure cloud. An app registered under Azure AD can have a client secret.
Client certificate	Client certificate is the digital certificate of an Azure cloud resource. This also can be used instead of client secret.
Client certificate password	This specifies the protected client certificate password.
Redirect URI	A redirect URI, or reply URL, is the location where the authorization server sends the user once the app has been successfully authorized and granted an authorization code or access token. It is used in interactive authorization flow.
Azure username	This is the Azure accounts user's ID. For example: [e-mail]
Azure password	This is the Azure account's password.

The parameters required to connect depend on Azure authentication method and cloud account configuration. The following table shows all the AAD authentication methods ODP.NET supports and the parameters each requires or can optionally use.

Authentication Methods	Required Parameters
Service Principal	Client ID Tenant ID Client secret or client digital certificate path Certificate password (only for protected certificates) DB application ID URI
Interactive Authentication	Client ID (optional when both client app and database registrations are under the same Azure tenant as the logged in user) Tenant ID (optional when both client app and database registrations are under the same Azure tenant as the logged in user) Redirect URI DB application ID URI Username (optional placeholder)
Managed Identity	DB application ID URI Client ID (only for user-defined managed identity)
Default	DB application ID URI Client ID [OPTIONAL] Tenant ID [OPTIONAL] Client secret [OPTIONAL] Client digital certificate path [OPTIONAL] Certificate password [OPTIONAL] Azure account username [OPTIONAL] Azure account password [OPTIONAL]
Username and Password	Client ID Tenant ID DB application ID URI Azure account username Azure account password
Device Code	Client ID Tenant ID DB application ID URI

With Azure Default flow, the client driver evaluates five authentication flows in the sequence: Service Principal (using client secret), Service Principal (using client certificate), Username Auth Flow, Managed Identity, and Visual Studio Auth Flow. It performs the most appropriate flow based on the environment where the application is running.

Note:

ODP.NET always uses the .default AAD scope.

ODP.NET can attain these AAD parameter values from any of the following configuration locations. The locations higher in the table have precedence over later locations listed lower.

Configuration Location	Available Parameters
AzureTokenAuthentication class	ClientId TenantId DatabaseApplicationIdUri ClientSecret ClientCertificate ClientCertificatePassword RedirectUri
Tnsnames.ora file or Easy Connect Plus	CLIENT_ID TENANT_ID AZURE_DB_APP_ID_URI CLIENT_CERTIFICATE REDIRECT_URI
OracleCredential class	Azure account username and password can be configured as username and password, respectively.
ODP.NET connection string	Azure account username and password can be configured as username and password, respectively.
SEPS wallet	Client ID and client secret can be configured as username and password, respectively. Azure account username and password can be configured as username and password, respectively.
Sqlnet.ora	CLIENT_ID TENANT_ID AZURE_DB_APP_ID_URI
Azure .NET SDK environment variables	AZURE_CLIENT_ID AZURE_CLIENT_SECRET AZURE_CLIENT_CERTIFICATE_PATH AZURE_TENANT_ID AZURE_USERNAME AZURE_PASSWORD

ODP.NET AAD SSO can be programmatically configured using the OracleConnection UseAzureTokenAuthentication extension method. The provider alters the AzureTokenAuthentication object to read only after the connection is opened to ensure no Azure token authentication settings are changed for the pool's lifetime. With the settings locked, the token is cached for re-use when new pooled connections are requested.

Azure Interactive Authentication Flows

For interactive authentication flows, when the user, application Id uri, client id, tenant id, and redirect uri values are same for two different connections, the user will only need to login once even if the connections belong to different connection pools. The reason why is that the same token is used for all these connections and ODP.NET caches it. The app can direct ODP.NET to clear its cache of all its access tokens so that they aren't used with new connections or new pools. ClearInteractiveTokenCache static method will clear access token cached in existing connection pools, that is, pools which are created with interactive authentication flows. Only existing opened connections will continue to work.

Code Sample: ODP.NET Azure Active Directory Single Sign-on Configuration and Connection

        string dbAppIdUri = "<value>";
        string clientId = "<value>";
        string tenantId = "<value>";
        string redirectUri = "<value>";
        var conn = new OracleConnection("User Id=/;Data Source=<value>;Connection Timeout=900");
        var tokenConfig = new AzureTokenAuthentication
        {
          ClientId = clientId,
          TenantId = tenantId,
          DatabaseApplicationIdUri = dbAppIdUri,
          RedirectUri = redirectUri
        };
        conn.WalletLocation = @".\wallet";
        conn.TnsAdmin = @".\tns";
        conn.TokenAuthentication = OracleTokenAuth.AzureInteractive;
        conn.UseAzureTokenAuthentication(tokenConfig);
        conn.Open();

Alternatively, AAD SSO can be configured without any ODP.NET code changes using tnsnames.ora, Easy Connect Plus, sqlnet.ora, SEPS wallet, and/or Azure .NET SDK environment variables.

Connection String Attributes and URLs

All ODP.NET providers support the connection string attributes in the following table.

Managed ODP.NET and ODP.NET Core can accept connection string URLs in lieu of connection string attributes. These URLs access the Azure, Oracle Cloud Infrastructure, or local file centralized configuration provider in which the ODP.NET connection string values are stored.

Table 3-6 lists the supported connection string attributes.

Table 3-6 Supported Connection String Attributes

Connection String Attribute	Description	Default Value
Application Continuity	Enables database requests to automatically replay transactional or non-transactional operations in a non-disruptive and rapid manner in the event of a severed database session, which results in a recoverable error.	true
Connection Lifetime	Minimum life time (in seconds) of the connection.	0
Connection Timeout	The time to wait (in seconds) for a new connection or an idle connection from the connection pool before a connection time out error can occur.	15
Data Source	Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.	empty string
DBA Privilege	Administrative privileges: SYSDBA, SYSASM, SYSOPER, SYSBACKUP, SYSDG, SYSKM, or SYSRAC.	empty string
Decr Pool Size	Number of connections that are closed when an excessive amount of established connections are unused.	1
Enlist	Controls the enlistment behavior and capabilities of a connection in context of COM+ transactions or System.Transactions.	true
HA Events	Enables ODP.NET connection pool to proactively remove connections from the pool when an Oracle database service, service member, instance, or node goes down. Works with Oracle Global Data Services, including Oracle RAC, Data Guard, GoldenGate, and some single instance deployments.	true
Load Balancing	Enables ODP.NET connection pool to balance work requests across Oracle database instances based on the load balancing advisory and service goal. Works with Oracle Global Data Services, including Oracle RAC, Active Data Guard, and GoldenGate.	true
Incr Pool Size	Number of new connections to be created when all connections in the pool are in use.	5
Max Pool Size	Maximum number of connections in a pool.	100
Metadata Pooling	Caches metadata information.	True
Min Pool Size	Minimum number of connections in a pool.	1
Password	Password for the user specified by User Id.	empty string
Persist Security Info	Retrieval of the password in the connection string.	false
Pooling	Connection pooling.	true
Proxy User Id	User name of the proxy user.	empty string
Proxy Password	Password of the proxy user.	empty string
Self Tuning	Enables or disables self-tuning for a connection.	true
Statement Cache Purge	Statement cache purged when the connection goes back to the pool.	false
Statement Cache Size	Statement cache enabled and cache size, that is, the maximum number of statements that can be cached.	0
Tns_Admin	Directory where ODP.NET can find its sqlnet.ora and tnsnames.ora configuration files. Not available in ODP.NET, Unmanaged Driver.	empty string
Token_Auth	This attribute specifies the access token authentication type. Possible values are OCITOKEN, OAUTH, or DISABLED. Only supported for managed ODP.NET and ODP.NET Core.	DISABLED
Token_Location	This attribute is the file-based token location. The value can be a directory where a file named "token" is or it can be the file's full path specification. Only supported for managed ODP.NET and ODP.NET Core.	Varies depending on token authentication type
User Id	Oracle user name.	empty string
Validate Connection	Validation of connections coming from the pool.	false
Wallet_Location	ODP.NET wallet directory. Not available in ODP.NET, Unmanaged Driver	empty string

The following example uses connection string attributes to connect to Oracle Database:

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //using connection string attributes to connect to Oracle Database
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}
 

See Also:

• OracleConnection Properties for detailed information on connection attributes

• OracleCommand Object for detailed information on statement caching

• Centralized Configuration Providers for Deployments

Connection String Builder

The OracleConnectionStringBuilder class makes creating connection strings less error-prone and easier to manage.

Using this class, developers can employ a configuration file to provide the connection string and/or dynamically set the values though the key/value pairs. One example of a configuration file entry follows:

<configuration>
   <connectionStrings> 
<add name="Publications" providerName="Oracle.DataAccess.Client" 
           connectionString="User Id=scott;Password=tiger;Data Source=inst1" />
   </connectionStrings> 
</configuration>

Connection string information can be retrieved by specifying the connection string name, in this example, Publications. Then, based on the providerName, the appropriate factory for that provider can be obtained. This makes managing and modifying the connection string easier. In addition, this provides better security against string injection into a connection string.

See Also:

OracleConnectionStringBuilder Class

Specifying the Data Source Attribute

This section describes different ways of specifying the data source attribute.

The following example shows a connect descriptor mapped to a TNS alias called sales in the tnsnames.ora file:

sales=
 (DESCRIPTION= 
  (ADDRESS= (PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA= 
     (SERVICE_NAME=sales.us.acme.com)))

The connection pool will maintain the full descriptor of an alias so that subsequent connection requests with the same connection string will not need to resolve the alias again. This applies to tnsnames.ora, .NET config data sources, and LDAP aliases. To flush out the cached full descriptor maintained by the connection pool, invoke OracleDataSourceEnumerator.GetDataSources() followed by OracleConnection.ClearPool() or OracleConnection.ClearAllPools().

If connection pooling is not used, the alias will need to be resolved to the full descriptor for each request. In the case of LDAP, the LDAP server is contacted for each connection request.

Using the TNS Alias

To connect as scott/tiger using the TNS Alias, a valid connection appears as follows:

"user id=scott;password=tiger;data source=sales";

Using the Connect Descriptor

ODP.NET also allows applications to connect without the use of the tnsnames.ora file. To do so, the entire connect descriptor can be used as the "data source".

The connection string appears as follows:

"user id=scott;password=tiger;data source=" + 
     "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)" + 
     "(HOST=sales-server)(PORT=1521))(CONNECT_DATA="+
     "(SERVICE_NAME=sales.us.acme.com)))"

Easy Connect and Easy Connect Plus Naming Methods

The Easy Connect and Easy Connect Plus naming methods enable clients to connect to a database without any configuration.

With this enabled, ODP.NET allows applications to specify the Data Source attribute in the form of:

//host:[port]/[service_name]

Using the same example, some valid connection strings follow:

"user id=scott;password=tiger;data source=//sales-server:1521/sales.us.acme.com"
"user id=scott;password=tiger;data source=//sales-server/sales.us.acme.com" 
"user id=scott;password=tiger;data source=sales-server/sales.us.acme.com"

If the port number is not specified, 1521 is used by default.

Easy Connect has been enhanced in ODP.NET 19c to support a wider application breadth, including clustered or cloud databases, and for ease of use. These improvements are called Easy Connect Plus and its features include:

• TCP/IP with SSL/TLS

• Any SQL*Net description level parameter can be used

• Multiple hosts and ports

• A straightforward name-value pair format

The syntax uses the question mark symbol ? to indicate the name-value pairs start and the ampersand symbol & to delimit each name-value pair. The entire connect string must be specified as a single string. Leading and trailing white spaces are ignored within parameter values. If whitespace is required as part of the value, it should be placed within double-quotes. In ODP.NET 23.5, Easy Connect Plus support for address lists was added.

Easy Connect Plus syntax:

[[protocol:]//]host1{,host2}[:port1]{,host3:port2}{;host4{,host5}[:port3]}
[/[service_name][:server][/instance_name]][?parameter_name=value{&parameter_name=value}]

Easy Connect Plus samples:

• tcps://sales1.us.example.com:1522,sales2.us.example.com;sales3.us.example.com,sales4.
  us.example.com/MyServiceName?WALLET_LOCATION=/wallets

• tcps://salesserver1:1521/sales.us.example.com

• salesserver1:1521,salesserver2,salesserver3:1522/sales.us.example.com

• tcps://salesserver1:1521/sales.us.example.com?wallet_location=D:/oracle

• tcps://salesserver1:1521/sales.us.example.com?ssl_server_cert_dn=cn=sales,cn=OracleContext,dc=us,dc=example,dc=com

• tcps://salesserver1:1521/sales.us.example.com?https_proxy=www-proxy.mycompany.com&https_proxy_port=80

• salesserver1:1521/sales.us.example.com?connect_timeout=60&transport_connect_timeout=30&retry_count=3

See Also:

Oracle Database Net Services Administrator's Guide for details and requirements in the section Using Easy Connect Naming Method

Using LDAP

ODP.NET can connect with connect identifiers mapped to connect descriptors in an LDAP-compliant directory server, such as Oracle Internet Directory and Microsoft Active Directory.

To enable ODP.NET LDAP connectivity, provide configuration information using an ldap.ora file.

To configure LDAP for ODP.NET, Unmanaged Driver, follow these Oracle documentation instructions in Configuring the Directory Naming Method in Oracle Database Net Services Administrator's Guide.

To configure LDAP for ODP.NET, Managed Driver, follow the instructions in "settings section" and "LDAPsettings section."

Beginning with Oracle Database release 18c, version 18.1, organizations can use centrally managed users (CMUs) with Active Directory. This feature is designed for organizations who prefer to use Active Directory as their centralized identity management solution. Organizations can use Kerberos, PKI, or password authentication with CMU and Active Directory.

LDAP channel binding and LDAP signing are ways to enhance the communication security between LDAP clients and Active Directory domain controllers. Beginning with version 19, managed ODP.NET and ODP.NET Core LDAP Naming adapters fully comply with the Microsoft LDAP hardening guidance.

See Also:

• Oracle Database Net Services Administrator's Guide and Oracle Database Security Guide for details and requirements in the section Using LDAP

• Microsoft Guidance for Enabling LDAP Channel Binding and LDAP Signing

LDAP URL Naming

LDAP URL Naming enable clients to resolve a TNS alias stored in a LDAP server without SQLNET.ORA and LDAP.ORA configuration files.

Supported Syntax:

ldap[s]://host[:port]/name[,context][?parameter=value{&parameter=value}]

host is host name or IP address.

port is port used. Default port number is 636 for LDAPS, 363 for LDAP.

name[,context] can be:

• alias, Default_Admin_Context

• alias, CN=OracleContext, Default_Admin_Context

• CN=alias, Default_Admin_Context

• CN=alias, CN=OracleContext, Default_Admin_Context

Default_Admin_Context is the value of the DEFAULT_ADMIN_CONTEXT parameter from LDAP.ORA.

parameters:

• DIRECTORY_SERVER_TYPE can be OID, OUD, AD, with OID as the default.

• AUTHENTICATE_BIND can be true or false, with false as the default.

• AUTHENTICATE_BIND_METHOD can be LDAPS_SIMPLE_AUTH or NONE, to indicate to use user name/password from the wallet.

• WALLET_LOCATCION specifies the location of the wallet. If WALLET_LOCATION is missing in the URL and required, it will be retrieved from the SQLNET.ORA.

• LDAP_CONN_TIMEOUT specifies the LDAP connection timeout in seconds.

When LDAP URL is used to resolve an alias, all LDAP related parameters in SQLNET.ORA and LDAP.ORA are ignored with the exception of WALLET_LOCATION parameter in SQLNET.ORA.

LDAPS Configuration without Wallets

Starting with versions 23.4, LDAP Naming adapter for ODP.NET managed and core drivers supports SSL without wallets. With one-way TLS, the LDAP client certificate is not required. A client wallet for the trusted Certificate Authority (CA) can be provided for ODP.NET LDAP Naming adapter authentication of the server. Without a wallet, the trusted CA will come from the local host's default trustpoints. LDAPS connection configuration is simpler without wallet files.

LDAPS Walletless One-way TLS can be used in one of two ways:

• If the LDAP server uses a certificate/wallet signed by a O/S-trusted certificate authority, as defined by the .NET root/localmachine store, then for LDAP client no changes are required besides un-setting the Oracle wallet location if it is already set.

• If the LDAP server uses a self-signed certificate/wallet, then the self-signed root certificate (public key) for the LDAP server certificate/wallet must be inserted into the root certificate store (root/localmachine) of the client machine.

ODP.NET LDAP Naming adapter will attempt to connect with one-way TLS when TCPS protocol is specified and either:

• no wallet location setting is present, or,

• wallet location is set to system.

Data Source Enumerator

The data source enumerator enables the application to generically obtain a collection of the Oracle data sources that the application can connect to.

See Also:

"OracleDataSourceEnumerator Class"

Using WebSocket

Websocket is a protocol that offers full-duplex communication channels over a single TCP connection. WebSocket with SSL/TLS offers a secure WebSocket connection. WebSocket is an extension to HTTP and is able to work with HTTP proxies and intermediaries.

ODP.NET Core, managed, and unmanaged providers all support Websocket and secure WebSocket protocols in Oracle Database 19c and higher.

ODP.NET WebSocket Configuration

WebSocket and secure WebSocket can be set through the connect descriptor by setting PROTOCOL to WS for WebSocket or WSS for secure WebSocket in tnsnames.ora, .NET configuration file, or OracleDataSourceCollection Class.

The WebSocket uniform resource identifier (URI) can be set in tnsnames.ora, sqlnet.ora, .NET configuration file, and OracleConfiguration class.

ODP.NET Configuration File WebSocket URI Setting Sample:

<oracle.manageddataaccess.client> 
        <version number="*">     
            <settings>              
                <setting name="SQLNET.URI" value="<WebSocket URI>"/>     
            </settings> 
        </version>
</oracle.manageddataaccess.client>

OracleConfiguration WebSocket URI Setting C# Sample:

OracleConfiguration.SqlNetURI = <WebSocket URI>;

When setting the URI in unmanaged ODP.NET, it cannot begin with a forward slash character. For managed ODP.NET, the forward slash at the beginning of the URI is optional.

Using Transport Layer Security and Secure Sockets Layer

Transport Layer Security (TLS) and its predecessor, Secure Sockets Layer (SSL), are industry standard protocols for securing network connections.

ODP.NET core, managed, and unmanaged versions support one-way and two-way TLS/SSL with wallets for database and transport authentication. ODP.NET core, managed, and unmanaged also support one-way TLS without wallets. Not having to provide a wallet can simplify database connectivity, such as with Oracle Autonomous Database.

Secure Sockets Layer and Transport Layer Security Differences

Although SSL was primarily developed by Netscape Communications Corporation, the Internet Engineering Task Force (IETF) took over development of it, and renamed it Transport Layer Security (TLS).

ODP.NET supports TLS 1.2 and 1.3.

The SSL/TLS client can ensure that the distinguished name (DN) is correct for the database server it is trying to connect to. Parameters for DN Matching are SSL_SERVER_DN_MATCH (sqlnet.ora) and SSL_SERVER_CERT_DN (tnsnames.ora), which can be defined in the .NET config file as well.

To turn DN Match on, set SSL_SERVER_DN_MATCH to True (or On or Yes). SSL_SERVER_CERT_DN is optional. It allows the administrator to specify exactly the DN they want to match. If the SSL_SERVER_CERT_DN is not set, then HOSTNAME based DN matching will be done.

SSL_SERVER_DN_MATCH can be set using:

• sqlnet.ora

• connect address: Both full description based and EZConnect

• OracleConfiguration.SSLServerDNMatch

SSL_SERVER_CERT_DN can be set using:

• connect address: Both full description based and EZConnect

See Also:

• The TLS Protocol Version 1.0 [RFC 2246] at the IETF Web site, which can be found at:

  http://www.ietf.org

• SSL_VERSION in the "settings section."

Note:

To simplify the discussion, this section uses the term SSL where either SSL or TLS may be appropriate because SSL is the most widely recognized term. However, where distinctions occur between how you use or configure these protocols, this section specifies what is appropriate for either SSL or TLS.

ODP.NET Secure Sockets Layer Configuration Using Wallets

When you configure Secure Sockets Layer on the client, you must confirm that the wallet is created and use TCP/IP with SSL on the client. Optionally, you can perform additional steps to enhance the configuration.

Note:

ODP.NET supports auto login wallets, but not the local auto login wallet option.

SSL Configuration Topics:

• Step 1: Confirm Client Wallet Creation

• Step 2: Use TCP/IP with SSL on the Client

• Step 3: Specify Required Client SSL Configuration (Wallet Location)

• Step 4: Set the SSL Version on the Client (Optional)

• Step 5: Set SSL as an Authentication Service on the Client (Optional)

Step 1: Confirm Client Wallet Creation

Before proceeding to the next step, you must confirm that a wallet has been created on the client and that the client has a valid certificate.

ODP.NET, managed and core drivers support file and Microsoft Certificate Store (MCS) based wallets.

• For file-based wallets, use Oracle Wallet Manager to check that the wallet has been created. See Step 1A: Confirm Wallet Creation on the Server in Oracle Database Security Guide for information about checking a wallet.

• For MCS, ODP.NET will retrieve the credentials from the MY or Personal certificate store. Use Microsoft tools or the orapki utility to create certificates, then load the certificates into MCS for use.

Step 2: Use TCP/IP with SSL on the Client

The ODP.NET Data Source must be modified to use SSL. Specifically, the transport protocol must be changed to use TCP/IP with SSL or what Oracle calls "tcps". An example ODP.NET Data Source for use with SSL is:

finance = (DESCRIPTION= 
  (ADDRESS = (PROTOCOL=tcps) (HOST=finance_server) (PORT=1575) )
  (CONNECT_DATA = (SERVICE_NAME=Finance.us.example.com) ) )

Step 3: Specify Required Client SSL Configuration (Wallet Location)

Edit the sqlnet.ora or .NET application configuration to specify the wallet location.

• An example of setting the SSL wallet location for file based wallets, where <wallet_location> is the specified location where the client wallet is stored:

  wallet_location = (SOURCE=(METHOD= File)
                            (METHOD_DATA=(DIRECTORY=<wallet_location>)))
  

• An example of setting the SSL wallet location for MCS based wallets is:

  wallet_location = (SOURCE=(METHOD= MCS))
  

Step 4: Set the SSL Version on the Client (Optional)

The SSL_VERSION parameter can be set through the sqlnet.ora or the .NET application.config, web.config, or machine.config file. Normally, it is not necessary to set this parameter. The default setting for this parameter is any, which allows the database server to apply any necessary restrictions to the SSL version accepted. An example setting in the sqlnet.ora is:

SSL_VERSION=1.2

Step 5: Set SSL as an Authentication Service on the Client (Optional)

If TCPS is to be used as both a transport and as an external database authentication, set the SQLNET.AUTHENTICATION_SERVICES parameter in the sqlnet.ora or application.config, web.config, or machine.config file.

Note that SSL can be used as just a transport encryption vehicle. Hence, the "optional" designation for this setting.

If SSL/TLS is to be used as an external database authentication method, then an externally authenticated database user matching the client certificate must be created.

An example setting allowing SSL external authentication in the sqlnet.ora is:

SQLNET.AUTHENTICATION_SERVICES = (TCPS)

Note:

Prior to ODAC 12c Release 4, ODP.NET, Managed Driver SSL connections would be redirected to dynamic (ephemeral) port on the database server machine. With ODAC 12c Release 4 and later, managed ODP.NET SSL connections will now continue to the original socket connection to the Oracle Listener. Hence, firewalls will now only need to allow access to the Oracle Listener's port (e.g., 1521).

See Also:

• Enabling Secure Sockets Layer in Oracle Database Security Guide for more information about TLS/SSL and configuration with Oracle database.

• Creating a User Who Is Authenticated Externally in Oracle Database Security Guide for more information about creating externally identified database users.

ODP.NET Secure Sockets Layer Configuration without Wallets

Starting with versions 19.13 and 21.4, ODP.NET managed and core drivers support SSL without wallets. Unmanaged ODP.NET introduced SSL support without wallets from versions 19.14 and 21.5. With one-way TLS, the client certificate is not required. A client wallet for the trusted Certificate Authority (CA) can be provided for ODP.NET authentication of the server. Without a wallet, the trusted CA will come from the local host's default trustpoints. Connection configuration is simpler without wallet files.

Walletless One-way TLS can be used in one of two ways:

• If the database server uses a certificate/wallet signed by a O/S-trusted certificate authority, as defined by the .NET root/localmachine store, then no client changes are required besides un-setting the Oracle wallet location if it is already set.

• If the database server uses a self-signed certificate/wallet, then the self-signed root certificate (public key) for the server certificate/wallet must be inserted into the root certificate store (root/localmachine) of the client machine.

ODP.NET will attempt to connect with one-way TLS when TCPS protocol is specified and either:

• no wallet location setting is present, or,

• wallet location is set to system.

ODP.NET essentially ignores existing static wallet configuration (file or OracleConfiguration based) when system is specified through the following dynamic configuration methods:

• TNS connect descriptor

  • WALLET_LOCATION or MY_WALLET_DIRECTORY

• Easy Connect Plus

  • WALLET_LOCATION

• ODP.NET connection string

  • Wallet_Location

• ODP.NET OracleConnection property

  • WalletLocation

These options allow administrators to specify at different granularities connections using a client certificate or SEPS and connections not using a wallet.

By default, Oracle Autonomous Database credentials use the WALLET_LOCATION in sqlnet.ora. To disable the wallet requirement, remove the sqlnet.ora WALLET_LOCATION setting completely or set one of the above dynamic wallet location settings to system.

Inserting Public Keys into System Trusted Certificate Authority List

The Certificate Authority (CA) public key is stored in the CA certificate. This certificate is stored in the local trust store on the client system. Here are typical commands to install root certificate in different platform.

Windows:

Base64/PEM format:

	openssl pkcs12 -in ewallet.p12 -clcerts -nokeys -out rootca.pem
	certutil -addstore -enterprise -f -v root rootca.pem

Linux (Oracle and Red Hat):

Create PEM certificate from PKCS #12 file:

openssl pkcs12 -in ewallet.p12 -clcerts -nokeys -out rootca.pem -password pass:<password>
sudo cp rootca.pem /etc/pki/ca-trust/source/anchors
sudo update-ca-trust

On Linux, the administrator can manually edit the root PEM file by adding the PEM file contents resulting from the OPENSSL command shown above. The root PEM file on Linux is located in /etc/pki/tls/cert.pem, which is a link to /etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem.

Linux (Ubuntu):

Change the PEM (.pem) or CER (.cer) file extension to a CRT extension (.crt). Then, execute the following two commands:

sudo cp rootca.crt /usr/local/share/ca-certificates/rootca.crt
sudo update-ca-certificates

macOS:

Base64/PEM format:

sudo security add-trusted-cert -d -r trustRoot -k "/Library/Keychains/System.keychain" "<directory>/rootca.pem"
 

Troubleshooting TLS/SSL Setup

This section discusses commonly encountered issues and their typical resolution steps.

Common TLS/SSL Wallet Errors

Microsoft Windows now restricts wallets from using the MD5 algorithm. Oracle wallets may have been generated with this algorithm as that was the default option in Oracle Public Key Infrastructure (orapki) utility 12.1 and earlier.

orapki can be found in the ORACLE_HOME\bin directory of the database server installation or Oracle client administrator install. It is not included with Oracle Instant Client. The utility is only needed to setup up the wallet; it is not necessary to deploy it with the wallet.

When you setup TLS/SSL and encounter an "ORA-0052: Failure during SSL handshake" error combined with a 0x80004005 error code and first inner exception "A SSPI-call failed" and second inner exception "A token sent to the function is invalid", then it is very likely that Microsoft Security Support Provider Interface (SSPI) rejected your Oracle Wallet, such as when MD5 is used. This is a failure on the handshake. You can resolve this error by using the SHA-2 algorithm instead.

If the second inner exception instead indicates "The credentials supplied to the package were not recognized", it is possible the user certificate was generated without a certificate authority (CA). You can resolve this error by using orapki to generate a CA/root certificate and then regenerating your user wallet/certificate to point to this new CA/root certificate.

The steps below will regenerate your Oracle Wallet using orapki and SHA-2. Any orapki version can be used to generate the wallet with these instructions.

1. Create root wallet, for example, a CA wallet.

   orapki wallet create -wallet ./root -pwd <password>

2. Add a self-signed certificate (CA certificate) to the root wallet.

   orapki wallet add -wallet ./root -dn 'CN=<my root>' -keysize 1024 -self_signed -validity 3650 -pwd <password> -sign_alg sha512

3. Export the self-signed certificate from the wallet.

   orapki wallet export -wallet ./root -dn 'CN=<my root>' -cert ./root/b64certificate.txt -pwd <password>

4. Create a user wallet, for example, a customer wallet.

   orapki wallet create -wallet ./user -pwd <password> -auto_login

5. Add a certificate request.

   orapki wallet add -wallet ./user -dn 'CN=<client's hostname>' -keysize 1024 -pwd <password> -sign_alg sha512

6. Export the certificate request.

   orapki wallet export -wallet ./user -dn 'CN=<client's hostname>' -request ./user/creq.txt -pwd <password>

7. Create a certificate issued by a CA.

   orapki cert create -wallet ./root -request ./user/creq.txt -cert ./user/cert.txt -validity 3650 -pwd <password> -sign_alg sha512

8. Add a trusted certificate (CA certificate) to the wallet. This example assumes the same CA for both the client and server wallets.

   orapki wallet add -wallet ./user -trusted_cert -cert ./root/b64certificate.txt -pwd <password>

9. Add a user certificate.

   orapki wallet add -wallet ./user -user_cert -cert ./user/cert.txt -pwd <password> -sign_alg sha512

10. Display contents of user wallet.

    orapki wallet display -wallet ./user -pwd <password>

11. Create a server wallet.

    orapki wallet create -wallet ./server -pwd <password> -auto_login

12. Add a server certificate request.

    orapki wallet add -wallet ./server -dn 'CN=<server's hostname>' -keysize 1024 -pwd <password> -sign_alg sha512

13. Export the certificate request.

    orapki wallet export -wallet ./server -dn 'CN=<server's hostname>' -request ./server/creq.txt -pwd <password>

14. Create a server certificate issued by a CA.

    orapki cert create -wallet ./root -request ./server/creq.txt -cert ./server/cert.txt -validity 3650 -pwd <password> -sign_alg sha512

15. Add a trusted certificate (CA certificate) to the server wallet. This example assumes the same CA for both the client and server wallets.

    orapki wallet add -wallet ./server -trusted_cert -cert ./root/b64certificate.txt -pwd <password>

16. Add an user_cert certificate for the server wallet.

    orapki wallet add -wallet ./server -user_cert -cert ./server/cert.txt -pwd <password> -sign_alg sha512

17. Display contents of server wallet.

    orapki wallet display -wallet ./server -pwd <password>

Mutual TLS/SSL Certificate Selection

When ODP.NET connections use TCPS(TLS/SSL), the user needs a signed certificate. This client certificate can be stored in an Oracle wallet and Microsoft Certificate Store (MCS). If there is more than one certificate that can be used, the user or application settings must specify the specific one to connect with. This choice can be made manually by the user via graphical user interface (GUI) or automatically by the application using the thumbprint.

For the GUI, the end user selects the correct client certificate from the list available in a store, such as MCS. The key store can filter the visible certificates to simplify selecting the correct certificate. For example, the GUI should be able to filter certificates by private certificate and expiration state. By default, public key only trusted Certificate Authorities (CA) certificates should not be visible in the certificate store GUI as the selection process is for mutual TLS.

GUI certificate selection is disabled by default. To enable, set the OracleConnection AllowCertificateSelectionUI property to true. When connecting from Windows, the end user gets a pop-up window to select a TLS/SSL certificate in MCS.

Alternatively to GUI selection, a thumbprint can uniquely identify the client certificate.

Developers can set the thumbprint in the SSLCertificateThumbprint property of OracleConfiguration or OracleConnection, TNS connect descriptor, Easy Connect Plus, or sqlnet.ora entries.

orapki (orapki.exe) can manage certificates and wallets for Oracle database. It can create, alter, view certificates in the Oracle wallet to include the SHA1 thumbprints. The thumbprint of a wallet can be displayed via the following orapki command:

orapki wallet display -wallet <wallet directory> -pwd <wallet password>
    -complete

Using Secure External Password Store

The Secure External Password Store (SEPS) is the use of a client-side wallet for securely storing the password credentials. All ODP.NET driver types can be configured to use the external password store.

An Oracle wallet is a container that securely stores authentication and signing credentials. Wallets can simplify large-scale deployments that rely on password credentials for database connections. Applications no longer need embedded user names and passwords, which reduces security risk.

Using SEPS with ODP.NET Core requires the NuGet package, System.Security.Cryptography.Pkcs version 4.7 or higher, as a dependency for your application project.

ODP.NET can use SEPS in conjunction with TCPS in two scenarios:

• SEPS wallet from sqlnet.ora WALLET_LOCATION and TCPS wallet from connect address WALLET_LOCATION

• Only SEPS wallet from sqlnet.ora WALLET_LOCATION is specified and WALLET_LOCATION is not specified in connect address

In the second scenario, if the wallet has a client certificate, mTLS is used. If there is no certificate, then walletless one-way TLS is used.

Configuring Secure External Password Store (SEPS)

Steps for configuring SEPS:

• Step 1. Create the wallet file

• Step 2. Point the configuration to the client wallet

• Step 3. Turn on SEPS

Step 1. Create the wallet file

Use the mkstore utility to create the wallet file and insert the credentials.

Step 1a. Create a wallet on the client by using the following syntax at the command line:

mkstore -wrl wallet_location -create

For example:

mkstore -wrl c:\oracle\product\12.1.0\db_1\wallets -create
Enter password: password

Step 1b. Create database connection credentials in the wallet by using the following syntax at the command line:

mkstore -wrl wallet_location -createCredential db_connect_string username
Enter password: password

For example:

mkstore -wrl c:\oracle\product\12.1.0\db_1\wallets -createCredential orcl system
Enter password: password

Step 2. Point the configuration to the client wallet

In the client sqlnet.ora file, enter the WALLET_LOCATION parameter and set it to the directory location of the wallet you created in Step 1.

For example, if you created the wallet in $ORACLE_HOME/network/admin and your Oracle home is set to C:\app\client\<user>\product\<version>\client_1\, then you need to enter the following into your client sqlnet.ora file:

WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)
           (METHOD_DATA =
              (DIRECTORY = C:\app\client\<user>\product\<version>\client_1\Network\Admin) ) )

Step 3. Turn on SEPS

Enable SEPS in your application by setting the wallet override setting to true in the OracleConfiguration.SqlNetWalletOverride property or SQLNET.WALLET_OVERRIDE setting in sqlnet.ora or .NET configuration file.

SQLNET.WALLET_OVERRIDE = TRUE

This setting causes all CONNECT /@db_connect_string statements to use the information in the wallet at the specified location to authenticate to databases.

When external authentication is in use, an authenticated user with such a wallet can use the CONNECT /@db_connect_string syntax to access the previously specified databases without providing a user name and password. Note however, that the wallet file needs to be kept up to date with the database credentials. If the database credentials change, but the wallet file is not changed appropriately, then the connections will fail.

See Also:

• Oracle Data Provider for .NET, Managed Driver Configuration for more details about the wallet settings.

• Managing the Secure External Password Store for Password Credentials in Oracle Database Security Guide for more information about secure external password store and configuration with Oracle database.

Using Kerberos

Kerberos is a network authentication service for security in distributed environments. ODP.NET applications can use Kerberos for single sign-on and centralized user authentication.

All ODP.NET provider types support Kerberos for external authentication to the database server.

Managed ODP.NET Kerberos has a dependency on MIT Kerberos 4.0.1.

ODP.NET Core Kerberos requires the Oracle.ManagedDataAccess.Kerberos NuGet package, which uses Kerberos.NET. When using ODP.NET Core Kerberos, ensure that the local hostname is IP resolvable via DNS or a local hosts file.

Note:

• Managed ODP.NET and ODP.NET Core do not support Kerberos constrained delegation.

• Managed ODP.NET and ODP.NET Core do not support Kerberos5Pre authentication adapter. Use Kerberos5 instead.

File Based Credential Cache and MSLSA

ODP.NET supports both a file-based Kerberos client credential cache (CC) and the ability to use Windows logon credentials as Kerberos client credentials. The latter is called MSLSA-based Kerberos authentication.

In order to utilize a file based Kerberos client credential cache (CC), the following executables associated with the full Oracle Database Client install are needed:

• okinit.exe

• oklist.exe

• okdstry.exe

The executables are required in order to acquire the Kerberos5 credentials and store them in the file based credential cache (CC). However, after credential cache creation, as long as the credentials remain valid, the above executables are then unneeded by the ODP.NET application at run-time.

ODP.NET, Managed Driver Dependency on MIT Kerberos

To use Kerberos5 database authentication in conjunction with ODP.NET, Managed Driver, download and install MIT Kerberos for Windows 4.0.1 on the same machine as ODP.NET, Managed Driver from the following location:

http://web.mit.edu/kerberos/dist/

See Also:

Configuring Kerberos Authentication in Oracle Database Security Guide for more information about Kerberos and configuration with Oracle database.

Configuring Kerberos Authentication with ODP.NET

Please reference the following "key" when viewing the below Kerberos configuration examples:

• oracleclient = Kerberos/Windows Domain user ID used by the Oracle database client program to represent the Oracle Client user on the domain

• oracleserver = Kerberos/Windows Domain user ID used by the Oracle database server

• DOMAIN.COMPANY.COM = Kerberos/Windows domain

• dbhost.company.com = Oracle database server machine hostname

• kerberos_service_name = Kerberos service name

• dc.company.com = hostname for Kerberos Key Distribution Center (KDC) and Windows Domain Controller

Configuring Kerberos Authentication Topics:

• Step 1. Update Windows services file to include a "kerberos5" entry

• Step 2. Create client and server Kerberos users (Windows domain users for MSLSA)

• Step 3. Associate the DB server's Kerberos principal name with the DB server's Kerberos Service (SPN mapping) and generate the server keytab file

• Step 4. Confirm the mapping of server user to service principal

• Step 5. Setup server sqlnet.ora to point to the keytab file generated in step 2

• Step 6. Create a kerberos configuration file that points to the Kerberos KDC (Windows Domain Controller for MSLSA)

• Step 7. Configure the Oracle database client and server sqlnet.ora or .NET config to point to the above Kerberos configuration file

• Step 8. Point the client sqlnet.ora or .NET config to a credential cache file or to MSLSA

• Step 9. Set the client and server authentication services in the sqlnet.ora or .NET config to Kerberos5

• Step 10. Setup an externally authenticated database user that matches the Kerberos client user setup in step 1 (note the case)

• Step 11. Login to the client machine via the Windows Domain client user (for MSLSA) or perform an okinit to authenticate the client Kerberos user (for file based CC):

• Step 12. Run the ODP.NET application

Step 1. Update Windows services file to include a "kerberos5" entry

Change the Kerberos entry in the Windows service file (C:\windows\system32\drivers\etc\services) from:

kerberos   88/tcp           krb5 kerberos-sec      #Kerberos

to:

kerberos   88/tcp kerberos5 krb5 kerberos-sec      #Kerberos

Step 2. Create client and server Kerberos users (Windows domain users for MSLSA)

As noted in the above "key", we will use oracleclient and oracleserver as our client and server Kerberos user IDs, respectively.

ODP.NET supports MSLSA using Windows domain users which have the following attributes:

• "Kerberos DES" unchecked

• "Kerberos AES 128 bit" checked

• "Kerberos AES 256 bit" checked

• "Kerberos preauthentication not required" checked

Step 3. Associate the DB server's Kerberos principal name with the DB server's Kerberos Service (SPN mapping) and generate the server keytab file

Run the following commands on the Kerberos KDC (Windows Domain Controller for MSLSA) as an administrator:

> ktpass -princ kerberos_service_name/[e-mail] /crypto all /mapuser [e-mail] /pass <oracleserver password> /out v5srvtab 
 
> setspn -A kerberos_service_name/[e-mail] oracleserver

Step 4. Confirm the mapping of server user to service principal

Also on the Kerberos KDC, run the following command, noting the output:

> setspn -L oracleserver 
 
Registered ServicePrincipalNames for CN=oracleserver,CN=Users,DC=domain,DC=company,DC=com: 
        kerberos_service_name/dbhost.company.com 
kerberos_service_name/[e-mail]  

Step 5. Setup server sqlnet.ora to point to the keytab file generated in step 2

Add the following line to the server sqlnet.ora:

sqlnet.kerberos5_keytab = c:\krb\v5srvtab

Step 6. Create a kerberos configuration file that points to the Kerberos KDC (Windows Domain Controller for MSLSA)

An example kerberos configuration file (krb.conf):

[libdefaults] 
default_realm = DOMAIN.COMPANY.COM 
 
[realms] 
DOMAIN.COMPANY.COM = { 
  kdc = dc.company.com 
 } 
 
[domain_realm] 
.domain.company.com = DOMAIN.COMPANY.COM 
domain.company.com = DOMAIN.COMPANY.COM 
.DOMAIN.COMPANY.COM = DOMAIN.COMPANY.COM 
DOMAIN.COMPANY.COM = DOMAIN.COMPANY.COM 

Step 7. Configure the Oracle database client and server sqlnet.ora or .NET config to point to the above Kerberos configuration file

Edit the client or server sqlnet.ora to include:

sqlnet.kerberos5_conf = C:\krb\krb.conf

Or edit the client application config to include (in the settings section):

<setting name="sqlnet.kerberos5_conf" value="C:\krb\krb.conf" />

Step 8. Point the client sqlnet.ora or .NET config to a credential cache file or to MSLSA

Example pointing to Credential Cache file:

sqlnet.kerberos5_cc_name = c:\krb\krb.cc

Example pointing to MSLSA:

sqlnet.kerberos5_cc_name = MSLSA: 

Step 9. Set the client and server authentication services in the sqlnet.ora or .NET config to Kerberos5

sqlnet.authentication_services=(Kerberos5)

Step 10. Setup an externally authenticated database user that matches the Kerberos client user setup in step 1 (note the case)

create user "[e-mail]" identified externally; 
grant connect, create session to "[e-mail]";

Step 11. Login to the client machine via the Windows Domain client user (for MSLSA) or perform an okinit to authenticate the client Kerberos user (for file based CC):

okinit oracleclient 

In the credential cache case, use oklist to verify there are valid credentials in the cache. If those credentials are expired, then use okdstry to clean the cache and then execute a new okinit command.

Step 12. Run the ODP.NET application

Set the ODP.NET connection string to User Id=/ and leave the Password blank.

Note:

• After configuring the client and server, the last 2 steps are the only steps required on an ongoing basis to run the ODP.NET application.

• A Microsoft Visual C Run-Time Library (MSVCRT.DLL) bug can cause ODP.NET, Managed Driver's setting of the Kerberos5 configuration to be ignored by the Microsoft run-time. In such a case, you will encounter the error message:

  OracleInternal.Network.NetworkException (0x80004005): NA Kerberos5: Authentication handshake failure at stage: krb5_sname_to_principal: default realm not found. Please set SQLNET.Kerberos5_conf.
  

  To workaround this error, manually set KRB5_CONFIG in the ODP.NET application's run-time environment to point to the Kerberos5 configuration file pointed to by SQLNET.Kerberos5_conf. For example,

  set KRB5_CONFIG=c:\oracle\network\admin\krb5.ini 
  

See Also:

Configuring Kerberos Authentication in Oracle Database Security Guide for more information about Kerberos and configuration with Oracle database.

Using Windows Native Authentication (NTS)

With the Windows native authentication adapter, Oracle users can authenticate to the database using just their Windows user login credentials. It provides a way to enable single sign-on and to simplify user and role credential management. Windows native authentication is also known as Windows Native authentication (NTS).

Note:

• ODP.NET Core supports Windows Native Authentication on Windows only

• Due to a limitation in the Microsoft .NET APIs, ODP.NET, Managed Driver only supports Windows Native authentication (NTS) via Microsoft NT LAN Manager (NTLM) instead of Kerberos-based credentials. Normally, this limitation would be invisible to the ODP.NET, Managed Driver application, since the Windows domain and the Oracle database server will transparently support both NTLM and Kerberos domain credentials by default.

Configuring Windows Native Authentication (NTS) for the ODP.NET Client

Steps in configuring the NTS for the ODP.NET Client:

• Step 1. Ensure OSAUTH_PREFIX_DOMAIN is set correctly

• Step 2. Setup the externally identified database user

• Step 3. Setup the client configuration to utilize NTS as the authentication methodology

Step 1. Ensure OSAUTH_PREFIX_DOMAIN is set correctly

Make sure OSAUTH_PREFIX_DOMAIN is set appropriately. If you desire the externally identified user ID to include the domain, set it to true, otherwise false. The parameter is a registry setting that can be found at HKLM/software/oracle/HOME<ORACLE_SID>. For example, if your ORACLE_SID is r1, it is located at HKLM/software/oracle/HOMEr1.

Step 2. Setup the externally identified database user

If you set the parameter to true in Step 1, use the following commands to setup the externally identified database user associated with the desired Windows domain user:

create user "MYDOMAIN\MYUSER" identified externally; 
grant connect, create session to "MYDOMAIN\MYUSER";

Step 3. Setup the client configuration to utilize NTS as the authentication methodology

Edit the client sqlnet.ora or app config to add NTS to the sqlnet.authentication_services. For example.

sqlnet.authentication_services = (NTS)

Note:

After configuring the client and server, the last 2 steps are the only steps required on an ongoing basis to run the ODP.NET application.

See Also:

Authenticating Database Users with Windows in Oracle Database Platform Guide for Microsoft Windows for Windows for more information about Windows native authentication.

Operating System Authentication Credentials

Oracle Database can use Windows user login credentials to authenticate database users. To open a connection using Windows user login credentials, the User Id connection string attribute must be set to a slash (/). If the Password attribute is provided, it is ignored.

Note:

ODP.NET Core supports operating system authentication for Windows only.

All ODP.NET, Unmanaged Driver connections, including those using operating system authentication, can be pooled. ODP.NET, Managed Driver supports operating system authentication, except when the Windows domain is constrained to only support Kerberos-based domain authentication. Connections are pooled by default, and no configuration is required, as long as pooling is enabled.

The following example shows the use of operating system authentication:

/* Create an OS-authenticated user in the database
   Assume init.ora has OS_AUTHENT_PREFIX set to "" and <OS_USER>
   is any valid OS or DOMAIN user.
 
     create user <OS_USER> identified externally;
     grant connect, resource to <OS_USER>;
 
   Login through OS Authentication and execute the sample.  See Oracle 
   documentation for details on how to configure an OS-Authenticated user
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class OSAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Establish connection using OS Authentication   
    con.ConnectionString = "User Id=/;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

See Also:

Oracle Database Administrator’s Reference for Microsoft Windows for information on how to set up Oracle Database to authenticate database users using Windows user login credentials

Network Data Encryption and Integrity

ODP.NET enables data encryption and integrity over a network for both intranet and cloud deployments. This ensures that data is disguised to all, except authorized users, and guarantees the original message contents are not altered. In earlier releases, these features were known as Oracle Advanced Security Option (ASO) encryption. Starting with Oracle Database 12c, Oracle ASO is not required to use network data encryption and data integrity.

The SQLNET.ALLOW_WEAK_CRYPTO, OracleConfiguration SqlNetAllowWeakCrypto, or equivalent settings must be set to true (default) to use weaker encryption and checksum algorithms.

Using Data Encryption

All ODP.NET provider types support the following encryption standards and algorithms.

• Advanced Encryption Standard (AES)

  • AES 128-bit

  • AES 192-bit

  • AES 256-bit

• Triple-DES (3DES)

  • 112-bit^*

  • 168-bit^*

  ^* = Encryption algorithms that are considered weak

ODP.NET, Managed Driver and ODP.NET Core use the following settings to configure network encryption:

• SQLNET.ENCRYPTION_CLIENT

• SQLNET.ENCRYPTION_TYPES_CLIENT

See Also:

settings section for definition and information on usage.

Using Data Integrity

All ODP.NET provider types support the following data integrity algorithms:

• SHA-1

• SHA-2

  • SHA-256

  • SHA-384

  • SHA-512

See Also:

• For more information on network encryption and integrity or configuring them for ODP.NET, Unmanaged Driver, refer to the Oracle Database Security Guide.

• To configure network encryption or data integrity in ODP.NET, Managed Driver and ODP.NET Core, refer to the SQLNET.CRYPTO_CHECKSUM_CLIENT and SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT settings in Oracle Data Provider for .NET, Managed Driver Configuration or sqlnet.ora. On the database server machine, you will likely have to configure the SQLNET.CRYPTO_CHECKSUM_SERVER and SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER parameters in the sqlnet.ora file.

Schema Discovery

ADO.NET exposes five different types of metadata collections through the OracleConnection.GetSchema API. This permits application developers to customize metadata retrieval on an individual-application basis, for any Oracle data source. Thus, developers can build a generic set of code to manage metadata from multiple data sources.

The following types of metadata are exposed:

• MetaDataCollections

  A list of metadata collections that is available from the data source, such as tables, columns, and indexes.

• Restrictions

  The restrictions that apply to each metadata collection, restricting the scope of the requested schema information.

• DataSourceInformation

  Information about the instance of the database that is currently being used, such as product name and version.

• DataTypes

  A set of information about each data type that the database supports.

• ReservedWords

  Reserved words for the Oracle query language.

  See Also:

  Oracle Schema Collections

User Customization of Metadata

ODP.NET provides a comprehensive set of database schema information. Developers can extend or customize the metadata that is returned by the GetSchema method on an individual application basis.

To do this, developers must create a customized metadata file and provide the file name to the application as follows:

1. Create a customized metadata file and put it in the CONFIG subdirectory where the .NET framework is installed. This is the directory that contains machine.config and the security configuration settings.

   This file must contain the entire set of schema configuration information, not just the changes. Developers provide changes that modify the behavior of the schema retrieval to user-specific requirements. For instance, a developer can filter out internal database tables and just retrieve user-specific tables

2. Add an entry in the app.config file of the application, similar to the following, to provide the name of the metadata file, in name-value pair format.

   <oracle.dataaccess.client>
     <settings>
       <add name="MetaDataXml" value="CustomMetaData.xml" />
     </settings>
   </oracle.dataaccess.client>
   

When the GetSchema method is called, ODP.NET checks the app.config file for the name of the customized metadata XML file. First, the GetSchema method searches for an entry in the file with a element named after the provider, in this example, oracle.dataaccess.client. In this XML element, the value that corresponds to the name MetaDataXml is the name of the customized XML file, in this example, CustomMetaData.xml.

If the metadata file is not in the correct directory, then the application loads the default metadata XML file, which is part of ODP.NET.

See Also:

"GetSchema"

Connection Pooling

ODP.NET connection pooling is enabled and disabled using the Pooling connection string attribute. By default, connection pooling is enabled. The following are ConnectionString attributes that control the behavior of the connection pooling service:

• Connection Lifetime

• Connection Timeout

• Decr Pool Size

• HA Events

• Incr Pool Size

• Load Balancing

• Max Pool Size

• Min Pool Size

• Pooling

• Validate Connection

Connection Pooling Example

The following example opens a connection using ConnectionString attributes related to connection pooling.

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
 
class ConnectionPoolingSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Open a connection using ConnectionString attributes
    //related to connection pooling.
    con.ConnectionString = 
      "User Id=scott;Password=tiger;Data Source=oracle;" + 
      "Min Pool Size=10;Connection Lifetime=100000;Connection Timeout=60;" + 
      "Incr Pool Size=5; Decr Pool Size=2";
    con.Open();
    Console.WriteLine("Connection pool successfully created");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Connection is placed back into the pool.");
  }
}

Using Connection Pooling

When connection pooling is enabled (the default), the Open and Close methods of the OracleConnection object implicitly use the connection pooling service, which is responsible for pooling and returning connections to the application.

The connection pooling service creates connection pools by using the ConnectionString property as a signature, to uniquely identify a pool.

In managed and Core versions of ODP.NET, connection strings themselves must be an exact match in their entirety. If keywords are supplied in a different order or a space is added to the connection string, a new pool is created. If a pool already exists with the requested signature, a connection is returned to the application from that pool.

In unmanaged ODP.NET, a new pool is created only when connection string attribute values change. Extra spaces or changing keyword order do not create a new pool.

When a connection pool is created, the connection pooling service initially creates the number of connections defined by the Min Pool Size attribute of the ConnectionString property. This number of connections is always maintained by the connection pooling service for the connection pool, except when Fast Connection Failover removes invalid connections is exceeded. In this case, the connection number could drop below the Min Pool Size. ODP.NET would then attempt to restore the minimum pool size level upon the next connection request.

At any given time, these connections are in use by the application or are available in the pool.

The Incr Pool Size attribute of the ConnectionString property defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool.

When the application closes a connection, the connection pooling service determines whether or not the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection pooling service destroys the connection; otherwise, the connection goes back to the connection pool. The connection pooling service enforces the Connection Lifetime only when Close() or Dispose() is invoked.

The Max Pool Size attribute of the ConnectionString property sets the maximum number of connections for a connection pool. If a new connection is requested, but no connections are available and the limit for Max Pool Size has been reached, then the connection pooling service waits for the time defined by the Connection Timeout attribute. If the Connection Timeout time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the connection pool request has timed-out. Upon a connection timeout, ODP.NET distinguishes whether the timeout occurred due to the database server failing to deliver a connection in the allotted time or no connection being available in the pool due to the maximum pool size having been reached. The exception text returned will either be "Connection request timed out" in the case of the former or "Pooled connection request timed out" in the case of the latter.

The Validate Connection attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own exception/error handler to retrieve and validate a new connection, rather than using the Validate Connection attribute. This generally provides better performance.

The connection pooling service closes connections when they are not used; connections are closed every 3 minutes. The Decr Pool Size attribute of the ConnectionString property provides connection pooling service for the maximum number of connections that can be closed every 3 minutes.

Enabling connection pooling by setting "pooling=true" in the connection string (which is the case by default) will also pool operating system authenticated connections.

Connection Pool Management

ODP.NET connection pool management provides explicit connection pool control to ODP.NET applications. Applications can explicitly clear connections in a connection pool.

Using connection pool management, applications can do the following:

Note:

• Clear connections from connection pools using the ClearPool method.

• Clear connections in all the connection pools in an application domain, using the ClearAllPools method.

See Also:

• "ClearPool"

• "ClearAllPools"

Connection Performance Counters

Installing Oracle Data Provider for .NET creates a set of performance counters on the target system. All ODP.NET provider types (Core, managed, and unmanaged) publish these performance counters for each of their client applications. These performance counters can be viewed using Windows Performance Monitor (Perfmon) or the PerformanceCounter class in the System.Diagnostics namespace version 4.5.0 or higher.

Note:

ODP.NET performance counters are available on Windows platforms only.

As ODP.NET performance counters are not enabled nor registered after installation, administrators must register the counters, then enable the specific counters of interest before being able to monitor them.

Registering Performance Counters

After installation, ODP.NET performance counters must be registered so that tools, such as Perfmon, can consume them. Registration requires running a PowerShell script for the respective provider.

ODP.NET installs six scripts for counters that must be run from PowerShell to take effect

• register_odpc_perfmon_counters.ps1 – Registers ODP.NET Core counters

• register_odpm_perfmon_counters.ps1 – Registers managed ODP.NET counters

• register_odpu_perfmon_counters.ps1 – Registers unmanaged ODP.NET counters

• unregister_odpc_perfmon_counters.ps1 – Unregisters ODP.NET Core counters

• unregister_odpm_perfmon_counters.ps1 – Unregisters managed ODP.NET counters

• unregister_odpu_perfmon_counters.ps1 – Unregisters unmanaged ODP.NET counters

These scripts are located in the ORACLE_HOME\odp.net\PerfCounters directory for Oracle Universal Installer and NuGet deployments, in the <INSTALLATION_DIRECTORY>\odp.net\PerfCounters directory for xcopy deployments, and <VISUAL_STUDIO_SOLUTION_DIRECTORY>\packages\Oracle.ManagedDataAccess.<VERSION>\PerfCounters directory for MSI deployments.

Windows Administrator privileges are required when running these PowerShell scripts.

To unregister, run the unregister PowerShell script that matches the ODP.NET provider type you wish to unregister.

Previously, the OraProvCfg.exe utility was included with ODP.NET for registering and unregistering counters. These PowerShell scripts replace the utility. If you registered ODP.NET counters with this utility, then unregister with the same OraProvCfg.exe version.

Enabling Performance Counters

ODP.NET enables monitoring many different connection counters, including pooled and non-pooled connections. These counters can be monitored individually or together. Developers can set which counters to monitor prior to application startup using the PerformanceCounters setting in the <settings> section of the .NET config file, which is available in managed and unmanaged ODP.NET, or Windows Registry, which is available in unmanaged ODP.NET only. For ODP.NET Core, developers can set the OracleConfiguration.PerformanceCounters property.

Table 3-7 lists the connection performance counters with their setting values.

Table 3-7 Performance Counters for Connection Pooling

Performance Counter	Valid Values	Description
None	0	Not enabled (Default)
HardConnectsPerSecond	1	Number of sessions being established with the Oracle Database every second.
HardDisconnectsPerSecond	2	Number of sessions being severed from the Oracle Database every second.
SoftConnectsPerSecond	4	Number of active connections originating from connection pools every second.
SoftDisconnectsPerSecond	8	Number of active connections going back to the connection pool every second.
NumberOfActiveConnectionPools	16	Total number of active connection pools.
NumberOfInactiveConnectionPools	32	Number of inactive connection pools.
NumberOfActiveConnections	64	Total number of connections in use.
NumberOfFreeConnections	128	Total number of connections available for use in all the connection pools.
NumberOfPooledConnections	256	Total number of pooled active and free connections.
NumberOfNonPooledConnections	512	Number of non-pooled active connections.
NumberOfReclaimedConnections	1024	Number of connections which were garbage-collected implicitly.
NumberOfStasisConnections	2048	No operation. This counter is no longer supported.

These settings are bitwise ORed in order to monitor more than one counter. For example, to collect counters for SoftConnectsPerSecond (4), Soft DisconnectsPerSecond (8), and NumberOfActiveConnectionPools (16), set PerformanceCounters to 28 (i.e. 4 + 8 + 12).

Setting Performance Counters in .NET Configuration Files

Performance counters can be set using an .NET configuration file, such as web.config or app.config, in managed and unmanaged ODP.NET only. Since .NET configuration entries take precedence over Windows Registry settings, they can be used for a specific application.

As earlier described, populating the PerformanceCounters setting in the <settings> section enables individual performance counters to be monitored. Windows Perfmon uses very long and not easily human readable application instance names. To make identifying each application easier administrators can assign an identifier name in the <connectionPools> section of the .NET configuration file. This section supports the following settings:

• connectionString: This setting identifies the connections or pool to monitor using the connection string as the unique identifier. The connection string entered here must match the target connection string to be monitored, but without the password attribute.

• poolName (optional): Connection strings can be very long and hard to read with many that look similar. poolName allows using an arbitrary string to easily identify which pool is being monitored.

The following examples show how to use these settings.

ODP.NET, Managed Driver

<oracle.manageddataaccess.client>
 <version number="*">
  <connectionPools>
           .
           .
     <connectionPool connectionString="[connection string without password]" poolName="[Pool Name]">   </connectionPool>
           .
           .
  </connectionPools>
 </version>
</oracle.manageddataaccess.client>

ODP.NET, Unmanaged Driver can use the same pool name setting and format as listed above by replacing the <oracle.manageddataaccess.client> tags with <oracle.unmanageddataaccess.client> tags. Alternatively, the legacy <oracle.dataaccess.client> format is available for unmanaged ODP.NET.

ODP.NET, Unmanaged Driver

<configuration>
 <oracle.dataaccess.client>
  <settings>
           .
           .
      <add name="[connection string without password]" value="connectionPool name='[Pool Name]'"/>   
          .
          .
    </settings>
  </oracle.dataaccess.client>
</configuration>

Setting Performance Counters in Windows Registry

Publication of individual unmanaged ODP.NET performance counters is enabled or disabled using the Windows Registry value PerformanceCounters of type REG_SZ. This registry value is under:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version

where Assembly_Version is the full assembly version number of Oracle.DataAccess.dll.

Similarly to the .NET config file, multiple performance counters can be obtained by adding the valid values. For example, if PerformanceCounters is set to 3, then both HardConnectsPerSecond and HardDisconnectsPerSecond are enabled.

Publishing Performance Counters

Commonly, ODP.NET performance counters are monitored using Perfmon. The counters are published under the following Category Names:

• ODP.NET, Core Driver

• ODP.NET, Managed Driver

• ODP.NET, Unmanaged Driver

Administrators can choose the individual ODP.NET counters to monitor after selecting one or more of these categories. PerfMon shows all ODP.NET counters, but only the explicitly enabled counters generate statistics.

After choosing the counters to monitor, administrators then select the running instance(s) to monitor. ODP.NET instances must be actively running for them to appear in PerfMon. Otherwise, no instances will appear available to monitor. After instance selection occurs, they are added to PerfMon as counters to monitor.

Performance counters can monitor at the application domain, pool, or database instance level. Database instance level monitoring only applies if load balancing or Fast Connection Failover features are enabled.

The instance name format is as follows:

<Application Domain Name> [<Process Id>, <Application Domain Id>][<Connection String/Pool Name>][<Instance Name>]. The entry is limited to 127 characters. There is a restriction length on every field in the instance name. The following table shows the maximum number of characters allocated for each field:

Table 3-8 Field Names of Performance Counters and Maximum Number of Characters

Field Name	Maximum Number of Characters
Application Domain	40
Pool Name/Connection String	70
Database Instance Name	16

When the length of a field value exceeds the length limit, the string is truncated and appended with "..." to fit within the length limit and indicate the continuation. For example, for a given application called Program.exe with a connection string user id=hr;Password=<password>;data source=inst1;max pool size=125;min pool size=50, one may see the following similar to the following for a process that has two application domains:

• Program.exe [123, 1]

• Program.exe [123, 1][ user id=hr;data source=inst1;max pool siz...]

• Program.exe [123, 1][ user id=hr;data source=inst1;max pool siz...] [instA]

• Domain 2[123, 2]

• Domain 2[123, 2][ user id=hr;data source=inst1;max pool siz...]

• Domain 2[123, 2][ user id=hr;data source=inst1;max pool siz...] [instB]

• Domain 2[123, 2][ user id=hr;data source=inst1;max pool siz...] [instC]

Since connection pool attributes can be similar in their first 70 characters, applications can set a Pool Name to uniquely identify each one in the monitoring tool. For example, when using Pool Name, the process will show up as follows:

Domain 2[123, 2][Pool Name][instC]

Database Resident Connection Pooling and Connection Manager in Traffic Director Mode

Client side connection pooling can be very efficient for middle tier machines. However, it can consume a great deal of database server resources if there are numerous middle tier servers with idle connections. While the intent is to keep the number of idle connections to a minimum, it becomes difficult as the number of middle tier servers increase, each possibly having idle connections that cannot be shared across applications.

Database Resident Connection Pooling (DRCP) is intended to optimize resource usage by pooling connections at the database server level, which can then be shared across many applications. The benefit is better scalability and lower resource usage at the database server level.

Oracle's database proxy solution, Connection Manager in Traffic Director Mode (CMAN-TDM) has its own pooling feature, Proxy Resident Connection Pooling (PRCP). PRCP works similarly to DRCP. If an application works well with DRCP, it will work just as well with PRCP. The only change necessary on the application side is the TNS alias or Easy Connect string should point to the PRCP server instead of the database/DRCP server.

ODP.NET supports DRCP and PRCP, which allows dispensing and releasing these connections to better utilize database server resources.

About DRCP

DRCP pools server processes, each of which is the equivalent to a dedicated server process and database session combined. These are called pooled servers. Pooled servers can be shared by multiple applications running on the same or multiple hosts.

When DRCP is configured for Oracle Database Real Application Clusters (Oracle RAC), the pool configuration is applied to each database instance. Starting or stopping the pool on one instance starts or stops the pool on all instances. 

DRCP and ODP.NET

DRCP is a server side pool that complements ODP.NET client side pooling. These two pools can be used together.

In a typical dedicated server mode, client side connection pooling saves both on server round trips and socket/session creation. In a connection open/close sequence, a server round trip is involved only upon the first Open(). For subsequent open/close sequences on the same connection, no client to server interaction is required because the connection is pooled locally in the client address space.

With DRCP enabled, when the client creates a connection, the connection is assigned a pooled server when needed. When the connection is closed, the database server releases the DRCP server session back to the server side pool, effectively making the DRCP server session available for reuse. This server session can be reused by the same pool or a different pool on the same or different middle tier hosts.

Configuring DRCP and ODP.NET

The following section describe how DRCP can be configured on the client side and the server side.

• Configuring DRCP on the Client Side

  ODP.NET developers can enable DRCP on the client side by using (SERVER=POOLED) in the connect descriptor.

  Sample Descriptor:

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales.example.com)(SERVER=POOLED)))

• Configuring DRCP on the Server Side

  Oracle database includes a default connection pool called SYS_DEFAULT_CONNECTION_POOL. By default, this pool is created, but not started. To enable database resident connection pooling, you must explicitly start the connection pool.

  You must be a database administrator (DBA) and must log on as SYSDBA to start and end a pool.

  SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();

  For DRCP connections to be shared across multiple client-side ODP.NET connection pools, then set the OracleConnection.DRCPConnectionClass property to a string value before opening the ODP.NET connection. ODP.NET will first try to obtain an idle connection with the same DRCP connection class property value. If it does not find one, then it will create a new connection instead.

Example 3-1 Using Database Resident Connection Pooling: Sample Code

// This application uses the following connect descriptor:
// oracle = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)(SERVER=POOLED)))

using System;
using Oracle.ManagedDataAccess.Client;
     
class DRCP
{
    static void Main()
      {
            string constr = “user id=hr;password=hr;data source=oracle";
            OracleConnection con = new OracleConnection(constr);
            con.DRCPConnectionClass = "GroupA";
            con.Open();

            con.Dispose();

      }
}

Note:

To use DRCP with ODP.NET, Unmanaged Driver,:

• the .NET configuration setting, CPVersion, must be set to 2.0, or,

• have CPVersion not set at all, but have (SERVER=POOLED) in the TNS full descriptor that is used by the application.

Refer to settings section for more information.

See Also:

Oracle Database Administrator's Guide for more information on configuring the default connection pool or end pool.

DRCP for Oracle Multitenant

In Oracle Database 21c and higher, each PDB can have its own separate DRCP.

In Oracle Database 19c and earlier, a DRCP can only be created on the CDB level and then be shared among all its PDBs. Thus, ODP.NET would have one DRCP for the entire CDB that would be configured and managed (start, stop, or reconfigure) only by connecting to the root container.

To configure, set a session to point to the CDB and start the DRCP pool. For example:

alter session set container = cdb$root;
execute dbms_connection_pool.configure_pool('SYS_DEFAULT_CONNECTION_POOL');
execute dbms_connection_pool.start_pool();

DRCP for Oracle Real Application Clusters (RAC)

In an Oracle Real Application Clusters (RAC) environment, when a user creates a DRCP on an instance, that DRCP is replicated on all the instances of an Oracle RAC database and you can use any instance to manage the connection pool. Any changes you make to the pool configuration are applicable on all Oracle RAC instances.

DRCP Restrictions for ODP.NET

ODP.NET does not support the following features with DRCP:

• Open the proxy connection with end user authentication

• Open the sysoper connection

• Open using OpenWithNewPassword

• Set the SQLNET.AUTHENTICATION_SERVICES parameter value to nts, which enables Microsoft Windows native operating system authentication

• .NET Framework versions earlier than .NET Framework 4

Oracle Multitenant and Pluggable Databases

Oracle Database 12c introduced Oracle Multitenant, which enables an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appears to ODP.NET as a separate database. This self-contained collection is called a pluggable database (PDB).

Oracle Multitenant is a database architecture that enables customers to easily consolidate multiple pluggable databases without changing their application. This architecture delivers all the benefits of managing many databases as one, yet retains the isolation and resource prioritization of separate databases. In addition, Oracle Multitenant enables rapid provisioning and upgrades, and fully complements other Oracle database options.

Managed and unmanaged ODP.NET fully support Oracle Multitenant. In addition to being able to connect to individual pluggable databases, ODP.NET has been enhanced so that applications can request and obtain connections to specific PDBs from the same connection pool. The application can connect to any of the PDBs that are part of the same container database (CDB) and they must have the same common user. Moreover, if connections span multiple instances, such as with Oracle Real Application Clusters, then every instance must uniformly have the same exact PDBs and services for the common user to connect to. If these conditions are met, then ODP.NET will be able to connect to or implicitly switch connections from one PDB to another within the same pool in a highly performant manner.

To obtain a connection to a specific PDB (as a common user) set the OracleConnections properties, PDBName and ServiceName, and then call OracleConnection.Open() to obtain the a connection to the specified PDB and Service name. PDBName and ServiceName identify the PDB and service that the connection needs to be established to or switched to if using an existing idle connection from the connection pool. They are the pluggable database name and database service name, respectively. If only the PDBName property is set and the service name is not set by the user, then the provider will return a connection that uses the default (administrative) service.

Note:

For ODP.NET, Unmanaged Driver to be able to switch PDB connections within the same pool:

• the .NET configuration setting, CPVersion, must be set to 2.0, or,

• have CPVersion not set at all, but set PDBName and/or ServiceName to a non-null/non-empty value for the first connection request for a given connection string.

Refer to settings section for more information.

Example 3-2 Using Pluggable Database: Sample Code

// C#
using System;
using Oracle.ManagedDataAccess.Client;

class PDB
{
    static void Main()
    {
            string constr = “user id=hr;password=hr;data source=oracle”;
            OracleConnection con = new OracleConnection(constr);
            con.PDBName = "pdb1";
            con.ServiceName = "db1.company.com";
            con.Open();            
            con.Close();
    }
}

ODP.NET keeps track of the PDBName and ServiceName to which the pooled connections are established to. Upon the application requesting for a connection with a specified PDBName and ServiceName, ODP.NET will return a connections that matches that request. However, if a matching connection is not found, ODP.NET will create a new connection and/or alter the session to switch to the requested PDBName and ServiceName. If the PDBName and/or ServiceName has been altered, then the SwitchedConnection property will return true if called with the Open() method invocation.

ODP.NET does not support usage of the ALTER SESSION statement to modify the container database during the lifetime of a process. If using PDBs with Oracle Continuous Query Notification, you must connect to Oracle Database 12c Release 2 or higher. Hosting connections to multiple PDBs from the same pool requires ODP.NET for .NET Framework 4 or higher.

Note:

When the connection is implicitly being switched from one PDB/Service to another, Service Relocation Connection Timeout(SRCT) will not take effect.

See Also:

Managing Pluggable Databases in Oracle Database Administrator's Guide

Edition-Based Redefinition

Edition-based redefinition enables you to upgrade the database component of an application even while the application is being used. This minimizes or eliminates downtime for the application.

ODP.NET does not support usage of the ALTER SESSION statement to modify the Edition during the lifetime of a process.

Managed and unmanaged ODP.NET can connect to a database edition, and also have the ability to host connections to multiple Editions from within the same pool. An ODP.NET application can obtain a connection that is associated with a specified database edition by setting the OracleConnection.DatabaseEditionName property to the name of the database edition that the connection should be associated with. To use a single pool, the same user must be able to connect to both Editions. Applications will perform better and more efficiently when using Editions because ODP.NET can use the same connection pool and even share the same OracleConnection object by only changing Edition-specific properties.

Example 3-3 Using Edition-Based Redefinition: Sample Code

using System;
using Oracle.ManagedDataAccess.Client;

class Editions
{
    static void Main()
    {
           // Create a  connection
           string constr = “user id=hr;password=hr;data source=oracle”; 
           OracleConnection con = new OracleConnection(constr);

           // Obtain a connection associated with EditionX
           con.DatabaseEditionName = "EditionX";
           con.Open();

           // Obtain a connection associated with EditionY
           con.Close();
           con.DatabaseEditionName = "EditionY";
           con.Open();
           con.Dispose();
    }
} 

Changing the edition name through DatabaseEditionName while the connection is open is not allowed. It must be changed while the connection is in a closed state.

ODP.NET supports Editions when connecting to Oracle Database 11g Release 2 or later. Hosting multiple Editions in the same pool is available only in ODP.NET for .NET Framework 4 and above.

Note:

To use this Edition-Based Redefinition feature with unmanaged ODP.NET connection pools:

• the .NET configuration setting, CPVersion, must be set to 2.0, or,

• have CPVersion not set at all, but set the DatabaseEditionName property to a non-null/non-empty value for the first connection request for a given connection string.

Refer to settings section for more information.

Applications can specify an Edition at deployment time using the registry or configuration file. An application can create the following registry entry of type REG_SZ:

HKLM\Software\Oracle\ODP.NET\version\Edition

Here version is the version of ODP.NET, and Edition is a valid Edition string value.

An application can alternatively use the web.config or application.config configuration file to specify the Edition at deployment time. The machine.config configuration file can be used to specify the Edition for all applications that use a particular version of the .NET framework.

The following example sets the Edition to E1 in a .NET configuration file for ODP.NET, Unmanaged Driver:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <oracle.dataaccess.client>
  <settings>
   <add name="Edition" value="E1"/>
  </settings>
 </oracle.dataaccess.client>
</configuration>

To summarize, the Edition name can be set by the DatabaseEditionName property, in the .NET configuration file, or in the Windows Registry. If DatabaseEditionName is set, then it takes precedence over the other two settings. If the .NET configuration file has an Edition set, then it takes precedence over the registry.

See Also:

For more information on Editions refer to the Oracle Database Administrator’s Guide and Oracle Database Development Guide

Privileged Connections

Oracle allows database administrators to connect to Oracle Database with various privileges, such as SYSDBA, SYSASM, and SYSOPER. This is done through the DBA Privilege attribute of the ConnectionString property.

The following example connects scott/tiger as SYSDBA:

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class PrivilegedConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    //Connect scott/tiger as SYSDBA   
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "DBA Privilege=SYSDBA;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

See Also:

DBA Privilege "Table 6-49" for further information on privileged connections in the database

Connection Pooling with OracleCredential

To better secure passwords, all ODP.NET applications can use OracleCredential to store user names and passwords outside of the connection string. OracleCredential mitigates the possibility of exposing user credentials in a page file swap or in a crash dump.

OracleCredential can be used with or without connection pooling.

When using the OracleCredential object, one should be aware of the connection pool algorithm that has been modified to take this new feature into consideration, when opening connection to the Oracle database. With earlier implementations of ODP.NET connection pool algorithm, the uniqueness of connection string attribute values were used to decide if a new connection pool needs to be created or an existing connection pool can be used to dispense connections. But with the introduction of OracleCredential, the reference of this object is also used to decide which connection pool that OracleConnection object is associated with.

The decision to create a new connection pool now depends on two factors, first is the uniqueness of connection string (as in earlier releases) and the second is the reference comparison of the OracleCredential provided to the OracleConnection object while opening a new connection. If either of these is different, a separate pool is used. In order to keep re-using the same connection pool and not create additional connection pools while using the OracleCredential object, simply create one OracleCredential object per database user and re-use the same object when opening connections.

Here is an example to clarify the new algorithm:

using System;
using System.Security;
using Oracle.ManagedDataAccess.Client;
//using Oracle.DataAccess.Client;


class Test
{
  static void Main()
  {
    string connStr1 = "user id=hr; password=hr; data source=oracle";
    string connStr2 = "data source=oracle";

    SecureString secPwd = new SecureString();
    secPwd.AppendChar('h');
    secPwd.AppendChar('r');
    secPwd.MakeReadOnly();

    OracleCredential oc1 = new OracleCredential("hr", secPwd);
    OracleCredential oc2 = new OracleCredential("hr", secPwd);
    OracleCredential oc3 = new OracleCredential("hr", secPwd);

    // con1 and con2 are associated with the same connection pool since both are using the same
    // connection string and OracleCredential remains null in both cases.
    OracleConnection con1 = new OracleConnection(connStr1);
    OracleConnection con2 = new OracleConnection(connStr1, null);

    // con3 and con4 use the same connection string but different OracleCredential objects
    // (although same contents) so they are associated with different connection pools.
    OracleConnection con3 = new OracleConnection(connStr2, oc1);
    OracleConnection con4 = new OracleConnection(connStr2, oc2);

    // con5 and con6 use the same connection string and same OracleCredential object
    // so they are associated with the same connection pool.
    OracleConnection con5 = new OracleConnection(connStr2, oc3);
    OracleConnection con6 = new OracleConnection(connStr2, oc3);

    // Open the connections
    con1.Open();
    con2.Open();
    con3.Open();
    con4.Open();
    con5.Open();
    con6.Open();

    // Please note that con1 and con2 use the same connection pool.
    // In addition, con3 and con4 use different connection pools
    // And lastly con5 and con6 use the same connection pool.
    // Thus, in the end, there will be 4 different connection pools created in total.
  }
}

Note:

OracleCredential does not support double quotes around a SecureString password. Double quotes can be used within a password, however.

See Also:

OracleCredential Class

Password Expiration

Oracle allows users passwords to expire. ODP.NET lets applications handle the password expiration by providing a new method, OpenWithNewPassword, that opens the connection with a new password.

The following example uses the OracleConnection OpenWithNewPassword method to connect with a new password of panther:

/* Database Setup
connect / as sysdba;
drop user testexpire cascade;
-- create user "testexpire" with password "testexpire"
grant connect , resource to testexpire identified by testexpire; 
alter user testexpire password expire;
*/
 
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class PasswordExpirationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    try
    {
      con.ConnectionString = 
        "User Id=testexpire;Password=testexpire;Data Source=oracle";
      con.Open();
      Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    }
    catch (OracleException ex)
    {
      Console.WriteLine(ex.Message);
 
      //check the error number 
      //ORA-28001 : the password has expired
      if (ex.Number == 28001)
      {
        Console.WriteLine("\nChanging password to panther");
        con.OpenWithNewPassword("panther");
        Console.WriteLine("Connected with new password.");
      }
    }
    finally
    {
      // Close and Dispose OracleConnection object
      con.Close();
      con.Dispose();
      Console.WriteLine("Disconnected");
    }
  }
}

Note:

• The OpenWithNewPassword method should be used only when the user password has expired, not for changing the password.

• If connection pooling is enabled, then invoking the OpenWithNewPassword method also clears the connection pool. This closes all idle connections created with the old password.

See Also:

"OpenWithNewPassword(String)"

Proxy Authentication

With proper setup in the database, proxy authentication enables middle-tier applications to control the security by preserving database user identities and privileges, and auditing actions taken on behalf of these users. This is accomplished by creating and using a proxy database user that connects and authenticates against the database on behalf of a database user (that is, the real user) or database users.

Proxy authentication can then be used to provide better scalability with connection pooling. When connection pooling is used in conjunction with proxy authentication, the proxy authenticated connections can be shared among different real users. This is because only the connection and session established for the proxy is cached. An additional session is created for the real user when a proxy authenticated connection is requested, but it will be destroyed appropriately when the proxy authenticated connection is placed back into the pool. This design enables the application to scale well without sacrificing security.

ODP.NET applications can use proxy authentication by setting the "Proxy User Id" and "Proxy Password" attributes in the connection string. The real user is specified by the "User Id" attribute. Optionally, to enforce greater security, the real user's password can be provided through the "Password" connection string attribute. When using distributed transactions in conjunction with proxy authentication, the real user's password is no longer optional, and it must be supplied.

The following example illustrates the use of ODP.NET proxy authentication:

/*  Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.
    Create a proxy user and modified scott to allow proxy connection.
  
     create user appserver identified by eagle;
     grant connect, resource to appserver;
     alter user scott grant connect through appserver;
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ProxyAuthenticationSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    // Connecting using proxy authentication
    con.ConnectionString = "User Id=scott;Password=tiger;" + 
      "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; ";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

See Also:

• Oracle Database SQL Language Reference for the description and syntax of the proxy clause for the ALTER USER statement

• Oracle Database Security Guide for information about how auditing works for SQL statements and privileges in a multitier environment

Dynamic Distributed Transaction Enlistment

For those applications that dynamically enlist in distributed transactions through the EnlistDistributedTransaction of the OracleConnection object, the "Enlist" connection string attribute must be set to a value of "true". If "Enlist=true", the connection enlists in a transaction when the Open method is called on the OracleConnection object, if it is within the context of a COM+ transaction or a System.Transactions. If not, the OracleConnection object does not enlist in a distributed transaction, but it can later enlist explicitly using the EnlistDistributedTransaction or the EnlistTransaction method. If "Enlist" is equal to "false" or "dynamic", the connection cannot enlist in the transaction. ODP.NET, Unmanaged Driver in ODAC 12c Release 3 first introduced this new behavior for "Enlist=dynamic".

See Also:

• "Connection String Attributes and URLs"

• "EnlistDistributedTransaction"

Client Identifier and End-to-End Tracing

The client identifier is a predefined attribute from the Oracle application context namespace USERENV. It is similar to proxy authentication because it can enable tracking of user identities. However, client identifier does not require the creation of two sessions (one for the proxy user and another for the end user) as proxy authentication does. In addition, the client identifier does not have to be a database user. It can be set to any string. But most importantly, by using client identifier, ODP.NET developers can use application context and Oracle Label Security, and configure Oracle Virtual Private Database (VPD) more easily. To set the client identifier, ODP.NET applications can set the ClientId property on the OracleConnection object after opening a connection. If connection pooling is enabled in unmanaged ODP.NET, then the ClientId is reset to null whenever a connection is placed back into the pool. In managed ODP.NET, the ClientId is reset to null (assuming no new value is provided) upon the first database round trip after a connection is placed back into the pool.

The client identifier can also be used for end-to-end application tracing. End-to-end tracing simplifies the process of diagnosing performance problems in multitier environments. In multitier environments, a request from an end client is routed to different database sessions by the middle tier making it difficult to track a client across different database sessions. End-to-end tracing uses the client identifier to uniquely trace a specific end-client through all tiers to the database server.

ODP.NET exposes the ActionName, ClientId, ClientInfo, and ModuleName write-only properties on the OracleConnection object. These properties correspond to the following end-to-end tracing attributes:

• ActionName - Specifies an action, such as an INSERT or UPDATE operation, in a module

• ClientId - Specifies an end user based on the logon ID, such as HR.HR

• ClientInfo - Specifies user session information

• ModuleName - Specifies a functional block, such as Accounts Receivable or General Ledger, of an application

See Also:

• "OracleConnection Properties"

• Oracle Database SQL Tuning Guide for an overview of End-to-End Application Tracing

• Oracle Database Security Guide