1 - Configuring TLS for ADO.NET

You can optionally use TLS to secure communication between your ADO.NET application and Vertica.

You can optionally use TLS to secure communication between your ADO.NET application and Vertica.

Prerequisites

Before you configure ADO.NET for TLS, you must configure client-server TLS, setting the TLSMODE to ENABLE. Mutual mode (TRY_VERIFY or higher) is not supported for ADO.NET.

Linux

The following procedure configures TLS on a Linux system:

  1. On the client filesystem, create the file /etc/ssl/certs/server.crt with the certificate text of the server certificate. You can retrieve the certificate text from a certificate in Vertica by querying the CERTIFICATES system table.
  2. Run the following command to verify that the certificate file is valid. If it is valid, the command outputs information about the certificate:
    $ openssl x509 -in /etc/ssl/certs/server.crt -text -noout
    
    Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            65:e7:fe:f9:0e:60:8a:79:ff:97:e2:c2:e4:e8:57:09:bd:f3:34:20
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = US, ST = Massachusetts, L = Burlington, O = OpenText, OU = Vertica, CN = Vertica Root CA
        Validity
            Not Before: Aug  3 18:11:44 2023 GMT
            Not After : Aug 12 18:11:44 2024 GMT
        Subject: C = US, ST = Massachusetts, L = Burlington, O = OpenText, OU = Vertica, CN = *.example.com
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA Public-Key: (2048 bit)
                Modulus:
                    00:9a:3a:83:5b:e7:73:c2:a4:15:c7:0a:81:a0:02:
                    f3:a6:6c:bb:aa:fb:fc:c8:9a:db:b9:41:21:2d:ca:
                    d9:07:1a:b1:07:35:39:0b:f3:62:08:1c:31:49:d4:
                    e2:b3:21:a8:84:eb:f4:43:5f:92:9e:c3:34:3d:4b:
                    4b:ab:ad:75:05:3c:c4:82:b5:21:45:a3:a5:c2:5c:
                    1d:c9:e3:d2:93:c1:40:b4:f6:07:f7:6c:47:68:9f:
                    9b:5d:41:4b:85:83:e0:f2:56:36:67:ee:ac:1e:08:
                    8c:6c:3a:af:b8:20:84:1d:7e:bb:d2:5e:45:d0:a8:
                    6d:ca:d8:46:5a:83:e6:d0:8d:00:fc:c1:bf:ce:d7:
                    95:4c:1d:ed:3a:45:82:d5:4d:1b:2c:d6:c4:17:5c:
                    aa:78:bc:e3:c2:2b:06:70:c3:1a:42:57:3e:19:5f:
                    7c:2f:0c:f2:d5:09:6a:ad:04:cd:95:33:92:20:56:
                    41:86:62:b2:fb:a5:d1:c5:65:cd:be:f9:31:6c:45:
                    79:a5:7f:10:7d:07:1d:26:eb:f3:18:42:14:3b:37:
                    84:81:f4:4f:c0:8d:93:b2:57:da:4f:64:53:b8:cc:
                    ed:ce:a7:c5:cc:af:5b:d1:4a:3f:fc:32:5a:f3:84:
                    89:cb:19:52:43:22:5c:9d:54:88:6b:41:3a:39:00:
                    86:bd
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
            X509v3 Key Usage: critical
                Digital Signature, Key Encipherment
            X509v3 Subject Key Identifier:
                DA:39:A3:EE:5E:6B:4B:0D:32:55:BF:EF:95:60:18:90:AF:D8:07:09
            X509v3 Authority Key Identifier:
                keyid:DA:39:A3:EE:5E:6B:4B:0D:32:55:BF:EF:95:60:18:90:AF:D8:07:09
                DirName:/C=US/ST=Massachusetts/L=Burlington/O=OpenText/OU=Vertica/CN=Vertica Root CA
                serial:4C:92:49:E5:98:94:C3:9C:B9:3E:DE:30:39:ED:52:23:E6:A8:7E:D8
    
    Signature Algorithm: sha256WithRSAEncryption
         a7:f5:35:12:ef:f2:8e:7e:85:45:6a:a0:7a:64:7b:d7:82:62:
         fc:2b:b4:76:1c:5b:3e:73:f8:cb:a7:8a:07:e7:1a:f3:fc:bc:
         45:58:b0:3c:13:6f:29:fa:7b:1a:cc:7b:c7:79:bc:54:62:5c:
         3f:44:ae:7e:af:68:6d:bc:3a:38:93:3f:a6:c9:42:70:68:c3:
         39:fc:a4:1a:2f:d5:d6:5d:0f:e4:06:cb:53:61:a7:b3:44:a5:
         85:74:76:f7:b7:65:1b:74:bf:58:63:40:60:82:59:01:b7:0f:
         a4:8c:58:44:7e:41:c9:63:a2:da:92:64:0e:a0:a5:f7:ad:49:
         40:f9:e3:e4:21:f2:d3:9c:c9:06:03:d6:5d:61:ef:ef:31:49:
         e0:66:79:08:97:0e:20:ec:2f:03:6c:a1:6e:9e:3c:24:5d:da:
         cc:20:ec:29:10:92:28:b2:3d:af:fb:3a:46:7d:ca:e5:bb:48:
         57:93:ef:27:a4:4d:00:2d:6d:7c:3c:6b:55:83:af:11:ef:c3:
         2f:d2:16:09:f0:4e:45:64:8d:50:93:da:ab:07:33:fb:2b:6c:
         d2:12:16:f9:a7:3d:de:e7:b9:62:0c:c3:37:bc:51:24:e7:aa:
         64:6d:19:15:7e:f5:f0:31:e6:5c:14:56:3b:6f:f0:6b:e0:35:
         68:b1:fa:27
    
  3. On the client filesystem, create the file /usr/local/share/ca-certificates/root.crt with the certificate text of the CA certificate.
  4. Verify that the certificate was issued by the CA certificate:
    $ openssl verify -CAfile /usr/local/share/ca-certificates/root.crt /etc/ssl/certs/server.crt
    server.crt: OK
    
  5. Update the certificate store:
    $ update-ca-certificates
    

Windows

The Vertica ADO.NET driver uses the TLS certificates in the default Windows key store.

To use TLS for ADO.NET connections to Vertica:

  1. Import the server certificate into the Windows key store:
    1. Create a file server.crt with the certificate text of the server certificate.
    2. Double-click server.crt certificate file.
    3. Let Windows determine the key type and select Install.
  2. Import the CA certificate into the Windows key store:
    1. Create a file root.crt with the certificate text of the CA certificate.
    2. Double-click root.crt certificate file.
    3. Select Place all certificates in the following store.
    4. Select Browse, Trusted Root Certification Authorities, and Next.
    5. Select Install.

Enable SSL in your ADO.NET applications

In your connection string, enable SSL by setting the SSL property in VerticaConnectionStringBuilder to true, for example:

    //configure connection properties
    VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
    builder.Host = "192.168.17.10";
    builder.Database = "VMart";
    builder.User = "dbadmin";
    builder.SSL = true;
    //open the connection
    VerticaConnection _conn = new VerticaConnection(builder.ToString());
    _conn.Open();

2 - Opening and closing the database connection (ADO.NET)

Before you can access data in Vertica through ADO.NET, you must create a connection to the database using the VerticaConnection class which is an implementation of System.Data.DbConnection.

Before you can access data in Vertica through ADO.NET, you must create a connection to the database using the VerticaConnection class which is an implementation of System.Data.DbConnection. The VerticaConnection class takes a single argument that contains the connection properties as a string. You can manually create a string of property keywords to use as the argument, or you can use the VerticaConnectionStringBuilder class to build a connection string for you.

To download the ADO.NET driver, go to the Client Drivers Downloads page.

This topic details the following:

  • Manually building a connection string and connecting to Vertica

  • Using VerticaConnectionStringBuilder to create the connection string and connecting to Vertica

  • Closing the connection

To manually create a connection string:

See ADO.NET connection properties for a list of available properties to use in your connection string. At a minimum, you need to specify the Host, Database, and User.

  1. For each property, provide a value and append the properties and values one after the other, separated by a semicolon. Assign this string to a variable. For example:

    String connectString = "DATABASE=VMart;HOST=v_vmart_node0001;USER=dbadmin";
    
  2. Build a Vertica connection object that specifies your connection string.

    VerticaConnection _conn = new VerticaConnection(connectString)
    
  3. Open the connection.

    _conn.Open();
    
  4. Create a command object and associate it with a connection. All VerticaCommand objects must be associated with a connection.

    VerticaCommand command = _conn.CreateCommand();
    

To use the VerticaConnectionStringBuilder class to create a connection string and open a connection:

  1. Create a new object of the VerticaConnectionStringBuilder class.

    VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
    
  2. Update your VerticaConnectionStringBuilder object with property values. See ADO.NET connection properties for a list of available properties to use in your connection string. At a minimum, you need to specify the Host, Database, and User.

    builder.Host = "v_vmart_node0001";
    builder.Database = "VMart";
    builder.User = "dbadmin";
    
  3. Build a Vertica connection object that specifies your connection VerticaConnectionStringBuilder object as a string.

    VerticaConnection _conn = new VerticaConnection(builder.ToString());
    
  4. Open the connection.

    _conn.Open();
    
  5. Create a command object and associate it with a connection. All VerticaCommand objects must be associated with a connection.

    VerticaCommand command = _conn.CreateCommand;
    

To close the connection:

When you're finished with the database, close the connection. Failure to close the connection can deteriorate the performance and scalability of your application. It can also prevent other clients from obtaining locks.

 _conn.Close();

Example usage:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
        //Perform some operations
            _conn.Close();
        }
    }
}

3 - ADO.NET connection properties

You use connection properties to configure the connection between your ADO.NET client application and your Vertica database.

To download the ADO.NET driver, go to the Client Drivers Downloads page.

You use connection properties to configure the connection between your ADO.NET client application and your Vertica database. The properties provide the basic information about the connections, such as the server name and port number, needed to connect to your database.

You can set a connection property in two ways:

  • Include the property name and value as part of the connection string you pass to a VerticaConnection.

  • Set the properties in a VerticaConnectionStringBuilder object, and then pass the object as a string to a VerticaConnection.

Property Description Default Value
Database Name of the Vertica database to which you want to connect. For example, if you installed the example VMart database, the database is "VMart". none
User Name of the user to log into Vertica. none
Port Port on which Vertica is running. 5433
Host

The host name or IP address of the server on which Vertica is running.

You can provide an IPv4 address, IPv6 address, or host name.

In mixed IPv4/IPv6 networks, the DNS server configuration determines which IP version address is sent first. Use the PreferredAddressFamily option to force the connection to use either IPv4 or IPv6.

none
PreferredAddressFamily

The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name. Valid values are:

  • Ipv4—Connect to the server using IPv4.

  • Ipv6—Connect to the server using IPv6.

  • None—Use the IP address provided by the DNS server.

Vertica.Data.VerticaClient.AddressFamilyPreference.None
Password The password associated with the user connecting to the server. string.Empty
BinaryTransfer

Provides a Boolean value that, when set to true, uses binary transfer instead of text transfer. When set to false, the ADO.NET connection uses text transfer. Binary transfer provides faster performance in reading data from a server to an ADO.NET client. Binary transfer also requires less bandwidth than text transfer, although it sometimes uses more when transferring a large number of small values.

Binary transfer mode is not backwards compatible to ADO.NET versions earlier than 3.8. If you are using an earlier version, set this value to false.

The data output by both modes is identical with the following exceptions for certain data types:

  • FLOAT: Binary transfer has slightly better precision.

  • TIMESTAMPTZ: Binary transfer can fail to get the session time zone and default to the local time zone, while text transfer reliably uses the session time zone.

  • NUMERIC: Binary transfer is forcibly disabled for NUMERIC data by the server for Vertica 11.0.2+.

true
ConnSettings SQL commands to run upon connection. Uses %3B for semicolons. string.Empty
IsolationLevel

Sets the transaction isolation level for Vertica. See Transactions for a description of the different transaction levels. This value is either Serializable, ReadCommitted, or Unspecified. See Setting the transaction isolation level for an example of setting the isolation level using this keyword.

Note: By default, this value is set to IsolationLevel.Unspecified, which means the connection uses the server's default transaction isolation level. Vertica's default isolation level is IsolationLevel.ReadCommitted.

System.Data.
IsolationLevel.Unspecified
Label A string to identify the session on the server. string
DirectBatchInsert Deprecated true
ResultBufferSize The size of the buffer to use when streaming results. A value of 0 means ResultBufferSize is turned off. 8192
ConnectionTimeout Number seconds to wait for a connection. A value of 0 means no timeout. 0
ReadOnly A Boolean value. If true, throw an exception on write attempts. false
Pooling A boolean value, whether to enable connection pooling. Connection pooling is useful for server applications because it allows the server to reuse connections. This saves resources and enhances the performance of executing commands on the database. It also reduces the amount of time a user must wait to establish a connection to the database false
MinPoolSize

An integer that defines the minimum number of connections to pool.

Valid Values: Cannot be greater than the number of connections that the server is configured to allow. Otherwise, an exception results.

Default: 55

1
MaxPoolSize

An integer that defines the maximum number of connections to pool.

Valid Values: Cannot be greater than the number of connections that the server is configured to allow. Otherwise, an exception results.

20
LoadBalanceTimeout

The amount of time, expressed in seconds, to timeout or remove unused pooled connections.

**Disable: **Set to 0 (no timeouts)

If you are using a cluster environment to load-balance the work, then pool is restricted to the servers in the cluster when the pool was created. If additional servers are added to the cluster, and the pool is not removed, then the new servers are never added to the connection pool unless LoadBalanceTimeout is set and exceeded or VerticaConnection.ClearAllPools() is called manually from an application. If you are using load balancing, then set this property to a value that considers when new servers are added to the cluster. However, do not set it so low that pools are frequently removed and rebuilt, doing so makes pooling ineffective.

0 (no timeout)
Workload The name of the workload for the session. For details, see Workload routing. None (no workload)
SSL A Boolean value, indicating whether to use SSL for the connection. false
IntegratedSecurity Provides a Boolean value that, when set to true, uses the user’s Windows credentials for authentication, instead of user/password in the connection string. false
KerberosServiceName Provides the service name portion of the Vertica Kerberos principal; for example: vertica/host@EXAMPLE.COM vertica
KerberosHostname Provides the instance or host name portion of the Vertica Kerberos principal; for example: verticaost@EXAMPLE.COM Value specified in the servername connection string property

4 - Load balancing in ADO.NET

Native connection load balancing

Native connection load balancing helps spread the overhead caused by client connections on the hosts in the Vertica database. Both the server and the client must enable native connection load balancing. If enabled by both, then when the client initially connects to a host in the database, the host picks a host to handle the client connection from a list of the currently up hosts in the database, and informs the client which host it has chosen.

If the initially-contacted host does not choose itself to handle the connection, the client disconnects, then opens a second connection to the host selected by the first host. The connection process to this second host proceeds as usual—if SSL is enabled, then SSL negotiations begin, otherwise the client begins the authentication process. See About native connection load balancing for details.

To enable native load balancing on your client, set the ConnectionLoadBalance connection parameter to true either in the connection string or using the ConnectionStringBuilder(). The following example demonstrates connecting to the database several times with native connection load balancing enabled, and fetching the name of the node handling the connection from the V_MONITOR.CURRENT_SESSION system table.

using System;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "v_vmart_node0001.example.com";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            // Enable native client load balancing in the client,
            // must also be enabled on the server!
            builder.ConnectionLoadBalance = true;

            // Connect 3 times to verify a new node is connected
            // for each connection.
            for (int i = 1; i <= 4; i++) {
                try {
                    VerticaConnection _conn = new VerticaConnection(builder.ToString());
                    _conn.Open();
                    if (i == 1) {
                        // On the first connection, check the server policy for load balance
                        VerticaCommand sqlcom = _conn.CreateCommand();
                        sqlcom.CommandText = "SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES";
                        var returnValue = sqlcom.ExecuteScalar();
                        Console.WriteLine("Status of load balancy policy
             on server: " + returnValue.ToString() + "\n");
                    }
                    VerticaCommand command = _conn.CreateCommand();
                    command.CommandText = "SELECT node_name FROM V_MONITOR.CURRENT_SESSION";
                    VerticaDataReader dr = command.ExecuteReader();
                    while (dr.Read()) {
                        Console.Write("Connect attempt #" + i + "... ");
                        Console.WriteLine("Connected to node " + dr[0]);
                    }
                    dr.Close();
                    _conn.Close();
                    Console.WriteLine("Disconnecting.\n");
                }
                catch(Exception e) {
                    Console.WriteLine(e.Message);
                }
            }
        }
    }
}

Running the above example produces the following output:

Status of load balancing policy on server: roundrobin

Connect attempt #1... Connected to node v_vmart_node0001
Disconnecting.

Connect attempt #2... Connected to node v_vmart_node0002
Disconnecting.

Connect attempt #3... Connected to node v_vmart_node0003
Disconnecting.

Connect attempt #4... Connected to node v_vmart_node0001
Disconnecting.

Hostname-based load balancing

You can also balance workloads by resolving a single hostname to multiple IP addresses. The ADO.NET client driver load balances by automatically resolving the hostname to one of the specified IP addresses at random.

For example, suppose the hostname verticahost.example.com has the following entries in C:\Windows\System32\drivers\etc\hosts:

192.0.2.0 verticahost.example.com
192.0.2.1 verticahost.example.com
192.0.2.2 verticahost.example.com

Specifying the hostname verticahost.example.com randomly resolves to one of the listed IP addresses.

5 - ADO.NET connection failover

If a client application attempts to connect to a host in the Vertica cluster that is down, the connection attempt fails when using the default connection configuration. This failure usually returns an error to the user. The user must either wait until the host recovers and retry the connection or manually edit the connection settings to choose another host.

Due to Vertica Analytic Database's distributed architecture, you usually do not care which database host handles a client application's connection. You can use the client driver's connection failover feature to prevent the user from getting connection errors when the host specified in the connection settings is unreachable. The JDBC driver gives you several ways to let the client driver automatically attempt to connect to a different host if the one specified in the connection parameters is unreachable:

  • Configure your DNS server to return multiple IP addresses for a host name. When you use this host name in the connection settings, the client attempts to connect to the first IP address from the DNS lookup. If the host at that IP address is unreachable, the client tries to connect to the second IP, and so on until it either manages to connect to a host or it runs out of IP addresses.

  • Supply a list of backup hosts for the client driver to try if the primary host you specify in the connection parameters is unreachable.

  • (JDBC only) Use driver-specific connection properties to manage timeouts before attempting to connect to the next node.

For all methods, the process of failover is transparent to the client application (other than specifying the list of backup hosts, if you choose to use the list method of failover). If the primary host is unreachable, the client driver automatically tries to connect to other hosts.

Failover only applies to the initial establishment of the client connection. If the connection breaks, the driver does not automatically try to reconnect to another host in the database.

Choosing a failover method

You usually choose to use one of the two failover methods. However, they do work together. If your DNS server returns multiple IP addresses and you supply a list of backup hosts, the client first tries all of the IPs returned by the DNS server, then the hosts in the backup list.

The DNS method of failover centralizes the configuration client failover. As you add new nodes to your Vertica Analytic Database cluster, you can choose to add them to the failover list by editing the DNS server settings. All client systems that use the DNS server to connect to Vertica Analytic Database automatically use connection failover without having to change any settings. However, this method does require administrative access to the DNS server that all clients use to connect to the Vertica Analytic Database cluster. This may not be possible in your organization.

Using the backup server list is easier than editing the DNS server settings. However, it decentralizes the failover feature. You may need to update the application settings on each client system if you make changes to your Vertica Analytic Database cluster.

Using DNS failover

To use DNS failover, you need to change your DNS server's settings to map a single host name to multiple IP addresses of hosts in your Vertica Analytic Database cluster. You then have all client applications use this host name to connect to Vertica Analytic Database.

You can choose to have your DNS server return as many IP addresses for the host name as you want. In smaller clusters, you may choose to have it return the IP addresses of all of the hosts in your cluster. However, for larger clusters, you should consider choosing a subset of the hosts to return. Otherwise there can be a long delay as the client driver tries unsuccessfully to connect to each host in a database that is down.

Using the backup host list

To enable backup list-based connection failover, your client application has to specify at least one IP address or host name of a host in the BackupServerNode parameter. The host name or IP can optionally be followed by a colon and a port number. If not supplied, the driver defaults to the standard Vertica port number (5433). To list multiple hosts, separate them by a comma.

The following example demonstrates setting the BackupServerNode connection parameter to specify additional hosts for the connection attempt. The connection string intentionally has a non-existent node, so that the initial connection fails. The client driver has to resort to trying the backup hosts to establish a connection to Vertica.

using System;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder =
        new VerticaConnectionStringBuilder();
            builder.Host = "not.a.real.host:5433";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            builder.BackupServerNode =
        "another.broken.node:5433,v_vmart_node0002.example.com:5433";
            try
            {
                VerticaConnection _conn =
            new VerticaConnection(builder.ToString());
                _conn.Open();
                VerticaCommand sqlcom = _conn.CreateCommand();
                sqlcom.CommandText = "SELECT node_name FROM current_session";
                var returnValue = sqlcom.ExecuteScalar();
                Console.WriteLine("Connected to node: " +
            returnValue.ToString() + "\n");
                _conn.Close();
                Console.WriteLine("Disconnecting.\n");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}

Notes

  • When native connection load balancing is enabled, the additional servers specified in the BackupServerNode connection parameter are only used for the initial connection to a Vertica host. If host redirects the client to another host in the database cluster to handle its connection request, the second connection does not use the backup node list. This is rarely an issue, since native connection load balancing is aware of which nodes are currently up in the database. See Load balancing in ADO.NET.

  • Connections to a host taken from the BackupServerNode list are not pooled for ADO.NET connections.