Connecting to the database

• 1: Configuring TLS for ADO.NET
• 2: Opening and closing the database connection (ADO.NET)
• 3: ADO.NET connection properties
• 4: Load balancing in ADO.NET
• 5: ADO.NET connection failover

1 - Configuring TLS for ADO.NET

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

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.

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.