Connecting to Oracle Database

3.3.2 Connection String Attributes

Table 3-2 lists the supported connection string attributes .

Table 3-2 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. Not available in ODP.NET, Managed Driver	`true`
`Connection Lifetime`	Minimum life time (in seconds) of the connection.	`0`
`Connection Timeout`	Minimum time (in seconds) to wait for a free connection from the pool.	`15`
`Context Connection`	Returns an implicit database connection if set to `true`. Supported in a .NET stored procedure only	`false`
`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` or `SYSOPER`.	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`
`Promotable` `Transaction`	Indicates whether or not a transaction is local or distributed throughout its lifetime.	promotable
`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`
`User` `Id`	Oracle user name.	empty string
`Validate` `Connection`	Validation of connections coming from the pool.	`false`

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

3.3.3 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

3.3.4 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.

3.3.4.1 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";

3.3.4.2 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)))"

3.3.4.3 Using Easy Connect Naming Method

The easy connect naming method enables clients to connect to a database without any configuration.

Prior to using the easy connect naming method, make sure that EZCONNECT is specified by the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file as follows:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

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.

See Also:

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

3.3.4.4 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 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."

See Also:

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

3.3.4.5 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"

3.3.5 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. Both managed and unmanaged ODP.NET support SSL for database and transport authentication.

3.3.5.1 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).

Essentially, TLS is an incremental improvement to SSL version 3.0.

ODP.NET, Managed Driver supports SSL 3.0 and TLS 1.0, 1.1, and 1.2. ODP.NET, Unmanaged Driver supports the same SSL and TLS versions as the Oracle Database Client version it is certified with.

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 the match is done by comparing the SERVICE_NAME value to the Common Name (CN) portion of the server certificate's DN.

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.

3.3.5.2 ODP.NET Secure Sockets Layer Configuration

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.

SSL Configuration Topics:

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 Driver supports 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, the Windows domain credentials will be used for the client credentials. Thus, a valid domain logon must be used while running the ODP.NET application. ODP.NET, Managed Driver will retrieve the credentials from the MY or Personal certificate store. Note that the server must also be configured to use MCS wallets. See Microsoft Certificate Services in Oracle Database Platform Guide for Microsoft Windows for information about setting up the server for MCS.

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 Required SSL Version on the Client (Optional)

The SSL_VERSION parameter can be set through the sqlnet.ora or the .NET application.config, web.congig, 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=3.0

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

Set the SQLNET.AUTHENTICATION_SERVICE parameter in the sqlnet.ora or application.config, web.congig, or machine.config file to allow SSL to be used as a database external authentication methodology.

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

If SSL is to be used as a database external Authentication Service, then a database externally authenticated 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.

3.3.5.3 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 refers to orapki.exe. This utility is part of full Oracle client (administrator) installations. 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.

Create root wallet, for example, a CA wallet.

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

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

Export the self-signed certificate from the wallet.

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

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

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

Add a certificate request.

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

Export the certificate request.

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

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

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>
```

Add a user certificate.

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

Display contents of user wallet.

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

Create a server wallet.

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

Add a server certificate request.

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

Export the certificate request.

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

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

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>
```

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

Display contents of server wallet.

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

3.3.6 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. Both ODP.NET, Managed Driver and Unmanaged Driver 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.

3.3.6.1 Configuring Secure External Password Store (SEPS)

Steps for configuring 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

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.

3.3.7 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. ODP.NET, Unmanaged Driver and Managed Driver both support Kerberos for external authentication to the database server.

3.3.7.1 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 Call Interface (OCI) 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.

3.3.7.2 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.

3.3.7.3 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

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/dbhost.company.com@DOMAIN.COMPANY.COM /crypto all /mapuser oracleserver@DOMAIN.COMPANY.COM /pass <oracleserver password> /out v5srvtab 
 
> setspn -A kerberos_service_name/dbhost.company.com@DOMAIN.COMPANY.COM 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/dbhost.company.com@DOMAIN.COMPANY.COM

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 "ORACLECLIENT@DOMAIN.COMPANY.COM" identified externally; 
grant connect, create session to "ORACLECLIENT@DOMAIN.COMPANY.COM";

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

Step 12. Run the ODP.NET application

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.

3.3.8 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:

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.

3.3.8.1 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

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

Assuming a Step 0 setting of true, 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.

3.3.9 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.

3.3.9.1 Using Data Encryption

Managed and unmanaged ODP.NET support the following encryption standards and algorithms:

Advanced Encryption Standard (AES)
- AES 128-bit
- AES 192-bit
- AES 256-bit
RSA RC4
- 128-bit
- 256-bit
Triple-DES (3DES)
- 112-bit
- 168-bit

ODP.NET, Managed Driver uses the following settings to configure network encryption:

SQLNET.ENCRYPTION_CLIENT
SQLNET.ENCRYPTION_TYPES_CLIENT

See Also:

settings section for definition and information on usage.

3.3.9.2 Using Data Integrity

Managed and unmanaged ODP.NET support the following data integrity algorithms:

MD5
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, refer to the SQLNET.CRYPTO_CHECKSUM_CLIENT and SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT settings in "Oracle Data Provider for .NET, Managed Driver Configuration." 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.

3.3.10 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, indexes, and stored procedures.
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

3.3.10.1 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:

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
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"

3.3.11 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.");
  }
}

3.3.11.1 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.

When a new connection is opened, if the connection string is not an exact match to an existing pool, then a new pool is created. Prior to ODP.NET 12.1.0.2, only connection string attribute values had to match. Now, connection strings themselves must be an exact match. Keywords supplied in a different order for the same connection will be pooled separately. If a pool already exists with the requested signature, a connection is returned to the application from that 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 or Connection Lifetime is exceeded. In these two cases, 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 event 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.

Beginning with Oracle Data Provider for .NET release 11.1.0.6.20, enabling connection pooling by setting "pooling=true" in the connection string (which is the case by default) will also pool operating system authenticated connections.

3.3.12 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:

These APIs are not supported in a .NET stored procedure.

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"

3.3.13 Connection Pool Performance Counters

Installing Oracle Data Provider for .NET creates a set of performance counters on the target system. These performance counters are published by ODP.NET for each ODP.NET client application. These performance counters can be viewed using Windows Performance Monitor (Perfmon).

In Perfmon, administrators can add ODP.NET counters to the performance monitor graph. ODP.NET performance counters are published under the following Category Name: Oracle Data Provider for .NET. Administrators can choose the ODP.NET counters to monitor after selecting the Oracle Data Provider for .NET category.

As ODP.NET performance counters are not enabled by default, administrators must enable the specific counters of interest before attempting to monitor them. In addition, at least one ODP.NET instance must be actively running when attempting to monitor using Perfmon.

Oracle Data Provider for .NET enables or disables publishing performance counters for connection pooling, using registry entries.

Table 3-3 lists the performance counters used for connection pooling with their valid registry values.

Table 3-3 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`	Number of pooled active connections.
`NumberOfNonPooledConnections`	`512`	Number of non-pooled active connections.
`NumberOfReclaimedConnections`	`1024`	Number of connections which were garbage-collected implicitly.
`NumberOfStasisConnections`	`2048`	Number of connections that will be soon available in the pool. User has closed these connections, but they are currently awaiting actions such transaction completion before they can be placed back into the pool as free connections.

3.3.13.1 Publishing Performance Counters

Publication of individual performance counters is enabled or disabled using the registry value PerformanceCounters of type REG_SZ or a .NET configuration file. 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.

Multiple performance counters can be obtained by adding the valid values. For example, if PerformanceCounters is set to 3, both HardConnectsPerSecond and HardDisconnectsPerSecond are enabled.

3.3.13.2 Setting Performance Counters Using .NET Configuration Entry

Performance counters can be set using an .NET configuration entry. Since .NET configuration entries take precedence over the registry value setting, they can be used for a specific application.

An .NET configuration entry uses name/value pairs as in the following example:

<configuration>
  <oracle.dataaccess.client>
  <settings>
    <add name="PerformanceCounters"
         value="3"/>
  </settings>
  </oracle.dataaccess.client>
</configuration>

3.3.13.3 Instance Names of Performance Counters

Performance counters can now 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-4 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=scott;Password=tiger;data source=inst1;max pool size=125, 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=scott;data source=inst1;max pool siz...]
Program.exe [123, 1][ user id=scott;data source=inst1;max pool siz...] [instA]
Domain 2[123, 2]
Domain 2[123, 2][ user id=scott;data source=inst1;max pool siz...]
Domain 2[123, 2][ user id=scott;data source=inst1;max pool siz...] [instB]
Domain 2[123, 2][ user id=scott;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]

The .NET config file can set the Pool Name attribute.

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 attribute and format as listed above by replacing the <oracle.manageddataaccess.client> tags with <oracle.unmanageddataaccess.client> tags.

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>

The behavior of two of the performance counters has now changed in the 12c release:

NumberOfPooledConnections -- Sum of the active connections and free connections. Previously, this value was equal to just the number of active connections.
NumberOfStasisConnections -- No longer supported.

3.3.14 Pluggable Databases

Oracle Database 12c introduced a new feature, Pluggable Databases, which enable 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).

ODP.NET 12c and higher can connect to PDBs, which clients access through database services. Database services have an optional PDB property. When a PDB is created, a new default database service is created automatically. The service has the same name as the PDB and can be used to access the PDB using the easy connect syntax or the net service name. This service is intended primarily for performing administrative tasks. It is recommended that you create additional services for use in your applications.

All ODP.NET features can be used with PDBs with the following exceptions:

Continuous Query Notification
Switching from one PDB to another PDB using the ALTER SESSION SET CONTAINER statement

See Also:

Managing Pluggable Databases in Oracle Database Administrator's Guide

3.3.15 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 11g Release 2 (11.2.0.1), and higher, supports specifying an Edition at deployment time when used with Oracle Database 11.2 or later. 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>

Note:

ODP.NET only supports deployment-time configuration of Edition.
ODP.NET does not support usage of the "ALTER SESSION" statement to modify the Edition during the lifetime of a process.
ODP.NET, Managed Driver does not support Edition-Based Redefinition.

See Also:

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

3.3.16 Operating System Authentication

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:

Operating System Authentication is not supported in a .NET stored procedure.

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 Platform Guide for Microsoft Windows for information on how to set up Oracle Database to authenticate database users using Windows user login credentials

3.3.17 Privileged Connections

Oracle allows database administrators to connect to Oracle Database with either SYSDBA or SYSOPER privileges. 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-33" for further information on privileged connections in the database

3.3.18 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"

3.3.19 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

3.3.20 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:

3.3.21 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, the ClientId is reset to null whenever 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:

Action - 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
Client info - Specifies user session information
Module - 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

3.3.22 Transparent Application Failover (TAF) Callback Support

Transparent Application Failover (TAF) is a feature in Oracle Database that provides high availability.

Note:

ODP.NET, Managed Driver does not support TAF nor TAF callbacks.

TAF enables an application connection to automatically reconnect to another database instance if the connection gets severed. Active transactions roll back, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.

With TAF, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances, and also creates a failover order for each application.

When a session fails over to another database, the NLS settings that were initially set on the original session are not carried over to the new session. Therefore, it is the responsibility of the application to set these NLS settings on the new session.

3.3.22.1 TAF Notification

Given the delays that failovers can cause, applications may wish to be notified by a TAF callback. ODP.NET supports the TAF callback function through the Failover event of the OracleConnection object, which allows applications to be notified whenever a failover occurs. To receive TAF callbacks, an event handler function must be registered with the Failover event.

3.3.22.2 When Failover Occurs

When a failover occurs, the Failover event is raised and the registered event handler is invoked several times during the course of reestablishing the connection to another Oracle instance.

The first call to the event handler occurs when Oracle Database first detects an instance connection loss. This allows the application to act accordingly for the upcoming delay for the failover.

If the failover is successful, the Failover event is raised again when the connection is reestablished and usable. At this time, the application can resynchronize the OracleGlobalization session setting and inform the application user that a failover has occurred. No significant database operation should occur immediately after a FailoverEvent.Begin event. SQL and major database operations should wait until the FailoverEvent.End event. FailoverEvent.Begin is primarily used to reject failover or to trace it. FailoverEvent.Begin can also be used for non-database application operations, such as informing the end user a failover is in progress and to wait until it completes before proceeding. Transactions can be used in the FailoverEvent.End callback phase, such as to file fault tickets or audit. These transactions must be committed before the callback completes.

If failover is unsuccessful, the Failover event is raised to inform the application that a failover did not take place.

The application can determine whether or not the failover is successful by checking the OracleFailoverEventArgs object that is passed to the event handler.

3.3.22.3 Registering an Event Handler for Failover

The following example registers an event handler method called OnFailover:

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class TAFCallBackSample
{
  public static FailoverReturnCode OnFailover(object sender, 
                                              OracleFailoverEventArgs eventArgs)
  {
    switch (eventArgs.FailoverEvent)
    {
      case FailoverEvent.Begin :
        Console.WriteLine(
          " \nFailover Begin - Failing Over ... Please standby \n");
        Console.WriteLine(
          " Failover type was found to be " + eventArgs.FailoverType);
        break;
 
      case FailoverEvent.Abort :
        Console.WriteLine(" Failover aborted. Failover will not take place.\n");
        break;
 
      case FailoverEvent.End :
        Console.WriteLine(" Failover ended ...resuming services\n");
        break;
 
      case FailoverEvent.Reauth :
        Console.WriteLine(" Failed over user. Resuming services\n");
        break;
 
      case FailoverEvent.Error :
        Console.WriteLine(" Failover error gotten. Sleeping...\n");
        return FailoverReturnCode.Retry;
 
      default :
        Console.WriteLine("Bad Failover Event: %d.\n", eventArgs.FailoverEvent);
        break;
    }
    return FailoverReturnCode.Success;
  } /* OnFailover */
 
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
    con.Failover += new OracleFailoverEventHandler(OnFailover);
    Console.WriteLine("Event Handler is successfully registered");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}

The Failover event invokes only one event handler. If multiple Failover event handlers are registered with the Failover event, only the event handler registered last is invoked.

Note:

Distributed transactions are not supported in an environment where failover is enabled.

See Also:

3.3.1 Connecting to Oracle Database Exadata Express Cloud Service

3.3.2 Connection String Attributes

3.3.3 Connection String Builder

3.3.4 Specifying the Data Source Attribute

3.3.4.1 Using the TNS Alias

3.3.4.2 Using the Connect Descriptor

3.3.4.3 Using Easy Connect Naming Method

3.3.4.4 Using LDAP

3.3.4.5 Data Source Enumerator

3.3.5 Using Transport Layer Security and Secure Sockets Layer

3.3.5.1 Secure Sockets Layer and Transport Layer Security Differences

3.3.5.2 ODP.NET Secure Sockets Layer Configuration

3.3.5.3 Troubleshooting TLS/SSL Setup

3.3.6 Using Secure External Password Store

3.3.6.1 Configuring Secure External Password Store (SEPS)

3.3.7 Using Kerberos

3.3.7.1 File Based Credential Cache and MSLSA

3.3.7.2 ODP.NET, Managed Driver Dependency on MIT Kerberos

3.3.7.3 Configuring Kerberos Authentication with ODP.NET

3.3.8 Using Windows Native Authentication (NTS)

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

3.3.9 Network Data Encryption and Integrity

3.3.9.1 Using Data Encryption

3.3.9.2 Using Data Integrity

3.3.10 Schema Discovery

3.3.10.1 User Customization of Metadata

3.3.11 Connection Pooling

3.3.11.1 Using Connection Pooling

3.3.12 Connection Pool Management

3.3.13 Connection Pool Performance Counters

3.3.13.1 Publishing Performance Counters

3.3.13.2 Setting Performance Counters Using .NET Configuration Entry

3.3.13.3 Instance Names of Performance Counters

3.3.14 Pluggable Databases

3.3.15 Edition-Based Redefinition

3.3.16 Operating System Authentication

3.3.17 Privileged Connections

3.3.18 Password Expiration

3.3.19 Proxy Authentication

3.3.20 Dynamic Distributed Transaction Enlistment

3.3.21 Client Identifier and End-to-End Tracing

3.3.22 Transparent Application Failover (TAF) Callback Support

3.3.22.1 TAF Notification

3.3.22.2 When Failover Occurs

3.3.22.3 Registering an Event Handler for Failover