.NET Oracle Connection String

This guide will help you construct the perfect connection string using dotConnect for Oracle. We'll decipher the essential elements and explore advanced options, ensuring a clear path to successful database interactions.

Direct mode connection strings

dotConnect for Oracle, like most applications that work with Oracle, uses Oracle Call Interface (OCI) to connect to the Oracle database server. This is the usual way to develop Oracle applications with a third-generation language. However, it requires Oracle Client software installed on the workstation, which is the cause of additional expenses for installation and administration. Moreover, .NET security settings may restrict the usage of unmanaged libraries, which makes it impossible to use the client software, for example, on ASP.NET servers.

In the Direct mode, dotConnect for Oracle connects to an Oracle server directly, without calling any third-party libraries. It allows your application to work with Oracle directly through the TCP/IP protocol without involving the Oracle Client software. To run your application built with dotConnect for Oracle you only need to have an operating system with the TCP/IP protocol support.

using Devart.Data.Oracle;
...
OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = true;
oraCSB.Server = "127.0.0.1";
oraCSB.Port = 1521;
oraCSB.ServiceName = "orcl";
oraCSB.UserId = "demo";
oraCSB.Password = "test";
OracleConnection myConnection = new OracleConnection(oraCSB.ConnectionString);
myConnection.Open();

Direct mode options properties

Property	Meaning
Direct	Specifies whether to use direct mode.
Server	States the IP address or hostname of the Oracle server.
Port	Indicates the port number for the Oracle server.
ServiceName	Determines the service name for the database instance.
Sid	Specifies the Oracle System Identifier (SID) for the database instance.
UserId	States the Oracle database username.
Password	Defines the password is associated with the Oracle database username.
License Key	Specify your license key in this parameter. This is required only when using .NET Standard compatible assemblies.

Privileged connection strings

The Connect mode connection string parameter allows opening a session with specified database privileges.

"UserId=system;Password=manager;ConnectMode=SysDba;Server=Ora;"

Oracle Connect mode enum values

Member	Description
Default	Opens an unprivileged session. It's a default value.
SysAsm	Opens a session with the SYSASM privilege.
SysBackup	Opens a session with the SYSBACKUP privilege.
SysDba	Opens a session with the SYSDBA privilege.
SysDg	Opens a session with the SYSDG privilege.
SysKm	Opens a session with the SYSKM privilege.
SysOper	Opens a session with the SYSOPER privilege.
SysRac	Opens a session with the SYSRAC privilege.

SSL/TLS connection strings

dotConnect for Oracle supports connections using SSL in the Direct mode (as well as in the OCI mode). It offers four ways to provide SSL certificates to the server (four kinds of certificate sources).

To specify a TNS descriptor

connection.ConnectionString = "Direct=True;Server=(DESCRIPTION=" +

"(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=2484)))" +

"(CONNECT_DATA=(SERVICE_NAME=orcl))(SECURITY=(SSL_SERVER_CERT_DN=\"C=UA,O=Devart,OU=DevartSSL,CN=TestSSL\")));" +
  "UserID=demo;Password=test;" +
  "SSL 
Cert=MIIB+zCCAWQCAQAwDQYJKoZIhvcNAQEEBQAwRjESMBAGA1UEAxMJQ2xpZW50U1NMMRIwEAYDVQQLEwlEZXZhcnRTU0wxDzANB" + 

"gNVBAoTBkRldmFydDELMAkGA1UEBhMCVUEwHhcNMTkxMjI0MTM1MTAwWhcNMjkxMjIxMTM1MTAwWjBGMRIwEAYDVQQDEwlDbGllbnRTU0wx" + 

"EjAQBgNVBAsTCURldmFydFNTTDEPMA0GA1UEChMGRGV2YXJ0MQswCQYDVQQGEwJVQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA5DQ" + 

"PPPYPVEsnuzhBfzdy0Y1rpNR5Ev6bK+YpajfdEKr42dno3x/dvQpRuno9ZUHfHTwQsLV+hXxsa/L1jfV45sqQW18DmXevllvgfik6DbeOMd" + 

"0xsHsxDPM3rNv2fJ8aSJKQd4kLE8oSkvjBViwtHaOx0bJbShrm5G5lndoHqc8CAwEAATANBgkqhkiG9w0BAQQFAAOBgQAEvH+cBRDM7n7MA" + 

"mQlkbtA1WxkV3aBTdwXXf0FecJRRTXHJOlhJsBmmejtvfy1YpugssHDXY+aQM8bH4HD/6NHORV+CX4tUmxRwPaCljxHNvlVZnwUoScZoWLy" + 
  "U/75txaWocHgzj6XQ3WlA/Kge/o1cpv9RxomXAoeIP+TcnUlfg==;" +
  "SSL 
Key=MIICXAIBAAKBgQDkNA889g9USye7OEF/N3LRjWuk1HkS/psr5ilqN90QqvjZ2ejfH929ClG6ej1lQd8dPBCwtX6FfGxr8vWN9Xj" + 

"mypBbXwOZd6+WW+B+KToNt44x3TGwezEM8zes2/Z8nxpIkpB3iQsTyhKS+MFWLC0do7HRsltKGubkbmWd2gepzwIDAQABAoGAQOYFR2L67R" +

"AKlpXjGpjcUHgVmuTKIfrCinOEZ67HccwNxXbRYVMgrnhW0d+dwkQ/fYLthDO5baD6/KA18U9UOFTPVN5yKE2Hsf5mwN07U5d2/ZbslK42o" + 

"iLaFHAKx2lJkb7HbdtnmlQMTXM8vwzl12ydIVX0rMFYGVRCqT0a1yECQQD+WhHZ5IS04+TRUSzMVGahHY3rgFVrCk6Qv0AMlzBaXeIEewGt" + 

"QrjXFAuvV80Ztb+fed2cVOfbwsUb6so/CD1pAkEA5a6dD8eamk3GK6NapfBv+G8Wanq4yEwZX/e08lNpHKSDq1L1a9jCTIpOe8p5zaPIdZw" +

"48LgxNglbSTHfAlBudwJAJ7EAhiMd/mhtxahIOF6XYV8OTYjKS5jhJ79gjFZvijqKUa6sVVBLLe0H4cXu0KtHCujmh0XMpMOhJLkf9HQhuQ" + 

"JAeBQlKvXI/zkADRp3LuAYOgMh7gNBDf6zGXgwkqxG/OGJsQ1LH9oQIuIADDocGgWxrMNDBZ7Wo5CauBapp9UTGwJBAJxdxA1JjFPIjqTfc" + 

"i5kgG2lg0kdgACobQbBICuHx+mIgxvw48vPYDUjUetVGKgeYg+sD6O3uyL0XYK/uDP+lz4=";

To set a shortened form

connection.ConnectionString = "Direct=True;Server=tcps://OracleSSL:2484/orcl;UserID=demo;Password=test;" +
  "SSL ServerCertDN=\"C=UA,O=Devart,OU=DevartSSL,CN=TestSSL\;" +
  "SSL 
Cert=MIIB+zCCAWQCAQAwDQYJKoZIhvcNAQEEBQAwRjESMBAGA1UEAxMJQ2xpZW50U1NMMRIwEAYDVQQLEwlEZXZhcnRTU0wxDzANB" +

"gNVBAoTBkRldmFydDELMAkGA1UEBhMCVUEwHhcNMTkxMjI0MTM1MTAwWhcNMjkxMjIxMTM1MTAwWjBGMRIwEAYDVQQDEwlDbGllbnRTU0wx" +

"EjAQBgNVBAsTCURldmFydFNTTDEPMA0GA1UEChMGRGV2YXJ0MQswCQYDVQQGEwJVQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA5DQ" +

"PPPYPVEsnuzhBfzdy0Y1rpNR5Ev6bK+YpajfdEKr42dno3x/dvQpRuno9ZUHfHTwQsLV+hXxsa/L1jfV45sqQW18DmXevllvgfik6DbeOMd" +

"0xsHsxDPM3rNv2fJ8aSJKQd4kLE8oSkvjBViwtHaOx0bJbShrm5G5lndoHqc8CAwEAATANBgkqhkiG9w0BAQQFAAOBgQAEvH+cBRDM7n7MA" +

"mQlkbtA1WxkV3aBTdwXXf0FecJRRTXHJOlhJsBmmejtvfy1YpugssHDXY+aQM8bH4HD/6NHORV+CX4tUmxRwPaCljxHNvlVZnwUoScZoWLy" +
  "U/75txaWocHgzj6XQ3WlA/Kge/o1cpv9RxomXAoeIP+TcnUlfg==;" +
  "SSL 
Key=MIICXAIBAAKBgQDkNA889g9USye7OEF/N3LRjWuk1HkS/psr5ilqN90QqvjZ2ejfH929ClG6ej1lQd8dPBCwtX6FfGxr8vWN9Xj" +

"mypBbXwOZd6+WW+B+KToNt44x3TGwezEM8zes2/Z8nxpIkpB3iQsTyhKS+MFWLC0do7HRsltKGubkbmWd2gepzwIDAQABAoGAQOYFR2L67R" +

"AKlpXjGpjcUHgVmuTKIfrCinOEZ67HccwNxXbRYVMgrnhW0d+dwkQ/fYLthDO5baD6/KA18U9UOFTPVN5yKE2Hsf5mwN07U5d2/ZbslK42o" +

"iLaFHAKx2lJkb7HbdtnmlQMTXM8vwzl12ydIVX0rMFYGVRCqT0a1yECQQD+WhHZ5IS04+TRUSzMVGahHY3rgFVrCk6Qv0AMlzBaXeIEewGt" +

"QrjXFAuvV80Ztb+fed2cVOfbwsUb6so/CD1pAkEA5a6dD8eamk3GK6NapfBv+G8Wanq4yEwZX/e08lNpHKSDq1L1a9jCTIpOe8p5zaPIdZw" +

"48LgxNglbSTHfAlBudwJAJ7EAhiMd/mhtxahIOF6XYV8OTYjKS5jhJ79gjFZvijqKUa6sVVBLLe0H4cXu0KtHCujmh0XMpMOhJLkf9HQhuQ" +

"JAeBQlKvXI/zkADRp3LuAYOgMh7gNBDf6zGXgwkqxG/OGJsQ1LH9oQIuIADDocGgWxrMNDBZ7Wo5CauBapp9UTGwJBAJxdxA1JjFPIjqTfc" +

"i5kgG2lg0kdgACobQbBICuHx+mIgxvw48vPYDUjUetVGKgeYg+sD6O3uyL0XYK/uDP+lz4=";

SSL/TLS options properties

Properties	Meaning
Cert	Gets or sets the location of the client certificate.
Key	Obtains or specifies the location of the user's private key.
ServerCertDN	Fetches or sets parameters for the Oracle server certificate check.
WalletPath	Determines the location of the Oracle Wallet to get a certificate for connecting to Oracle in the Direct mode.

SSH connection strings

dotConnect for Oracle supports both connecting to Oracle Cloud and on-premises Oracle servers using SSH in the Direct mode.

To enable SSH, you need to add the ssh:// prefix to the Host connection string parameter. Note also that the Host parameter must be set as if you are connecting from the computer with the SSH Server installed. Thus, if they are installed on the same computer, you need to set the Host parameter to ssh://localhost and specify the host domain name or address in the SSH Host connection string parameter.

The OracleConnection.SshOptions property points to an object that holds all information necessary to connect to the SSH server. Alternatively, you may specify the corresponding parameters in the connection string.

To set up an SSH connection, configure the corresponding SSH options as described below:

Password authentication

OracleConnection connection = new OracleConnection("Direct=True;Host=ssh://127.0.0.1;Port=1521;Sid=orcl;UserID=demo;Password=test;");
connection.SshOptions.AuthenticationType = SshAuthenticationType.Password;
connection.SshOptions.User = "sshUser";
connection.SshOptions.Host = "OracleSSH";
connection.SshOptions.Password = "sshPassword";
OracleCommand myCommand = new OracleCommand("select count(*) from dept", connection);
connection.Open();
Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
Console.WriteLine(count);
connection.Close();

Public key authentication

OracleConnection connection = new OracleConnection("Direct=True;Host=ssh://127.0.0.1;Port=1521;Sid=orcl;UserID=demo;Password=test;");
connection.SshOptions.AuthenticationType = SshAuthenticationType.PublicKey;
connection.SshOptions.User = "sshUser";
connection.SshOptions.Host = "sshServer";
connection.SshOptions.PrivateKey = "E:\\WORK\\client.key";
OracleCommand myCommand = new OracleCommand("select count(*) from dept", connection);
connection.Open();
Int64 count = Convert.ToInt64(myCommand.ExecuteScalar());
Console.WriteLine(count);
connection.Close();

SSH options properties

Property	Connection string parameter	Meaning
SshOptions.AuthenticationType	SSH Authentication Type	Client authentication methods.
SshOptions.CipherList	SSH Cipher List	List of ciphers that the client agrees to use, by colons.
SshOptions.Host	SSH Host	Name or IP address of the SSH server.
SshOptions.Passphrase	SSH Passphrase	Passphrase for the client key.
SshOptions.Password	SSH Password	User password for SSH server.
SshOptions.Port	SSH Port	Number of the port to connect to the SSH server.
SshOptions.PrivateKey	SSH Private Key	Location of the private key.
SshOptions.User	SSH User	User ID for the SSH server.

Locations of the private key can be specified in three ways:

As a file in the system: file://C:\Temp\client.key
As an item of the certificate store: storage://Name.Id (examples RSA.Client.key or DSA.Client.key)
As a compiled resource: resource://client.key
Loaded into memory, using the MemCryptStorage class: memory://key_id

dotConnect for Oracle supports two modes of block ciphering: Cipher-block chaining (CBC) and Counter (CTR). The following ciphers are available for SSH connections in the CBC mode:

3DES or 3DES(168) is the Triple Data Encryption Algorithm. The key size is 168 bits.
Blowfish is the symmetric-key block cipher, designed in 1993 by Bruce Schneier. The key size is 128 bits.
AES(128) is the Advanced Encryption Standard. The key size is 128 bits.
AES(192) is the Advanced Encryption Standard. The key size is 192 bits.
AES or AES(256) is the Advanced Encryption Standard. The key size is 256 bits.

In the CTR mode, the AES ciphers are used.

AES(128)-CTR is the Advanced Encryption Standard. The key size is 128 bits.
AES(192)-CTR is the Advanced Encryption Standard. The key size is 192 bits.
AES-CTR or AES(256)-CTR is the Advanced Encryption Standard. The key size is 256 bits.

Proxy connection strings

Proxy authentication allows middle-tier applications to control the security by preserving database user identities and privileges. Oracle lets create a proxy database user, connecting and authenticating against the database on behalf of database users. Proxy authentication is not supported in the Direct mode.

With dotConnect for Oracle, you can connect to Oracle using proxy authentication in two ways:

Using two connection instances and the Open method overload, accepting an OracleConnection
With the help of a single OracleConnection instance with the Proxy User ID and Proxy Password connection string parameters

Here's an example that uses two connections:

using (OracleConnection proxyConnection = new 
OracleConnection("DataSource=10.0.0.1;UserId=my_proxy_user;Password=my_proxy_user_password;")) {
  proxyConnection.Open();

  OracleConnection connection = new OracleConnection("UserID=demo;Pooling=false;");
  connection.Open(proxyConnection);

  OracleCommand cmd = connection.CreateCommand();
  cmd.CommandText = "SELECT 
SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual";
  using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
  reader.Read();
  Console.WriteLine("PROXY_USER '{0}'", reader.GetString(0));   // 'MY_PROXY_USER'
  Console.WriteLine("SESSION_USER '{0}'", reader.GetString(1)); // 'demo'
  }
}

The next example uses the Proxy User ID and Proxy Password connection string parameters:

using (OracleConnection proxyConnection = new 
OracleConnection("DataSource=10.0.0.1;ProxyUserId=my_proxy_user;ProxyPassword=my_proxy_user_password;UserId=demo;")) {
  proxyConnection.Open();

  OracleCommand connection = proxyConnection.CreateCommand();
  connection.CommandText = "SELECT SYS_CONTEXT('USERENV', 
'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual";
  using (OracleDataReader reader = 
connection.ExecuteReader(CommandBehavior.SingleRow)) {
  reader.Read();
  Console.WriteLine("PROXY_USER '{0}'", reader.GetString(0));   // 
'MY_PROXY_USER'
  Console.WriteLine("SESSION_USER '{0}'", reader.GetString(1)); // 
'demo'
  }
}

Proxy options properties

Property	Connection String Parameter	Meaning
DataSource	Proxy Host	Specifies the proxy server hostname or IP address.
ProxyUserId	Proxy User	Sets the proxy user name for authentication.
ProxyPassword	Proxy Password	Defines the proxy password for authentication.

OracleCredential connection strings

The OracleCredential class lets provide a password for connecting to Oracle in a more secure way than just specifying it in plain text in a connection string or assigning the System.String instance to the OracleConnection.Password property. OracleCredential class uses the SecureString class for storing and specifying the password. Passing credentials via the OracleCredential class is not supported in
the Direct mode.

using System;
using System.ComponentModel;
using System.Diagnostics;
using System.Security;
using Devart.Data.Oracle;

class Program {
 
    static void Main(string[] args) {
    {
        Console.Write("Enter user name: ");
        String userName = Console.ReadLine();
        
        // Instantiate the secure string.
        SecureString securePwd = new SecureString();
        ConsoleKeyInfo key;

        Console.Write("Enter password: ");
        do {
           key = Console.ReadKey(true);
           
           // Ignore any key out of range.
           if (((int) key.Key) >= 65 && ((int) key.Key <= 90)) {
              // Append the character to the password.
              securePwd.AppendChar(key.KeyChar);
              Console.Write("*");
           }   
        // Exit if Enter key is pressed.
        } while (key.Key != ConsoleKey.Enter);

        Console.WriteLine();
        
        try {
            OracleConnection connection = new OracleConnection("Server = Ora;");
            connection.Credential = new OracleCredential(userName,securePwd);
            connection.Open();

            // use connection...
        }
        catch (Exception e) {
            Console.WriteLine(e.Message);
        }
        finally {
           securePwd.Dispose();
        }
    }
}

Unicode property connection strings

Unicode connection strings ensure that the data retrieved from and sent to an Oracle database is in the Unicode format, which supports a wide range of characters. The following two functions demonstrate how to use the Unicode property to implement charset-safe
string transfer.

public void UploadString(OracleConnection connection)
{
  connection.Unicode = true;
  OracleCommand command = new OracleCommand("INSERT INTO Test.TextBlocks (BlockID, BlockName, BlockContent) VALUES(1,'First',:BlockText)", connection);
  command.Parameters.Add("BlockText",OracleDbType.VarChar,50).Value = "Place here some text that requires Unicode support.";
  connection.Open();
  try
  {
	Console.WriteLine(command.ExecuteNonQuery()+" rows affected.");
  }
  finally
  {
	connection.Close();
  }
}                                                                                                                                      	 


public void DownloadString(OracleConnection connection)
{
  connection.Unicode = true;
  OracleCommand command = new OracleCommand("SELECT * FROM Test.TextBlocks", connection);
  connection.Open();
  OracleDataReader reader = command.ExecuteReader(CommandBehavior.Default);
  try
  {
	while (reader.Read())
	{
  	string myString = (string)reader["BlockContent"];
  	Console.WriteLine(myString);
	}
  }
  finally
  {
	reader.Close();
	connection.Close();
  }
}

Pooling connection string options

Parameter	Meaning
Max Pool Size	The maximum number of connections allowed in the pool. Setting the Max Pool Size value of the ConnectionString can affect performance. The default value is 100. In the case of forgotten open transactions, the number of connections can exceed the Max Pool Size.
Min Pool Size	The minimum number of connections allowed in the pool. The default value is 0.

OciPooling connection string options

Parameter	Meaning
OciSessionPoolAllowWaiting	Enables or disables waiting for a connection to close when there is no free connection.
OciSessionPoolConnectionLifetime	Gets or sets the connection lifetime in the OCI pool.
OciSessionPoolIncrement	Specifies the number of sessions that are added at once.
OciSessionPooling	Enables or disables the OCI Session Pooling feature.
OciSessionPoolMaxSize	Sets the maximum number of sessions that can be opened in the session pool.
OciSessionPoolMinSize	Defines the minimum number of sessions in the session pool.
OciSessionPoolPassword	Gets or sets the password for proxy connections.
OciSessionPoolUserId	Obtains or determines the User ID for proxy connections.

Advanced connection string options

Parameter	Meaning
DescribeStoredProcedure	Enables or disables additional check queries, performed when executing an OracleCommand with CommandType equal to CommandType.StoredProcedure.
HAEvents	Allows dotConnect for Oracle to proactively remove connections to the corresponding Oracle Real Application Clusters (RAC) database service, service member, instance, or node from the pool when this service, service member, instance, or node goes down.
Home	Gets or sets which Oracle Home to use.
LobBlockSize	Specifies the size of a block (in megabytes) to read/write CLOB or NCLOB data from/to a database. This option is supported in the OCI mode only. Applicable only for Unicode Oracle servers.
NumberMappings	Fetches or sets the collection of number mappings used for this connection.
OraMts	Determines whether to use Oracle Services for Microsoft Transaction Server (OraMTS) for distributed transactions.
TrimFixedChar	Specifies whether to trim trailing spaces when reading data from fixed-length string data types (CHAR, NCHAR).
UsePerformanceMonitor	Enables dotConnect for Oracle performance counters that allow you to conveniently measure the frequency of connecting/disconnecting to the data source, the number of active connections, pooled connections, etc. via Windows Performance Monitor or programmatically.