Creating an ODBC data source name (DSN)

• 1: Creating an ODBC DSN for Linux
• 1.1: Testing an ODBC DSN using isql
• 2: Creating an ODBC DSN for windows clients
• 2.1: Setting up an ODBC DSN
• 2.2: Encrypting passwords on ODBC DSN
• 2.3: Testing an ODBC DSN using Excel
• 3: Creating an ODBC DSN for macOS clients
• 3.1: Testing an ODBC DSN using iodbctest
• 4: ODBC DSN connection properties
• 5: Setting DSN connection properties

1 - Creating an ODBC DSN for Linux

You define DSN on Linux and other UNIX-like platforms in a text file. Your client's driver manager reads this file to determine how to connect to your Vertica database. The driver manager usually looks for the DSN definitions in two places:

• /etc/odbc.ini

• ~/.odbc.ini (a file named .odbc.ini in the user's home directory)

Users must be able to read the odbc.ini file in order to use it to connect to the database. If you use a global odbc.ini file, consider creating a UNIX group with read access to the file. Then, add the users who need to use the DSN to this group.

The structure of these files is the same—only their location differs. If both files are present, the ~/.odbc.ini file usually overrides the system-wide /etc/odbc.ini file.

odbc.ini file structure

The odbc.ini is a text file that contains two types of lines:

• Section definitions, which are text strings enclosed in square brackets.

• Parameter definitions, which contain a parameter name, an equals sign (=), and then the parameter's value.

The first section of the file is always named [ODBC Data Sources], and contains a list of all the DSNs that the odbc.ini file defines. The parameters in this section are the names of the DSNs, which appear as section definitions later in the file. The value is a text description of the DSN and has no function. For example, an odbc.ini file that defines a single DSN named Vertica DSN could have this ODBC Data Sources section:

[ODBC Data Sources]
VerticaDSN = "vmartdb"

Appearing after the ODBC data sources section are sections that define each DSN. The name of a DSN section must match one of the names defined in the ODBC Data Sources section.

Configuring the odbc.ini file:

To create or edit the DSN definition file:

1. Using the text editor of your choice, open odbc.ini or ~/.odbc.ini.

2. Create an ODBC Data Sources section and define a parameter:

   • Whose name is the name of the DSN you want to create

   • Whose value is a description of the DSN

   For example, to create a DSN named VMart, you would enter:

   [ODBC Data Sources]
   VMart = "VMart database on Vertica"
   

3. Create a section whose name matches the DSN name you defined in step 2. In this section, you add parameters that define the DSN's settings. The most commonly-defined parameters are:

   • Description – Additional information about the data source.

   • Driver – The location and designation of the Vertica ODBC driver, or the name of a driver defined in the odbcinst.ini file (see below). For future compatibility, use the name of the symbolic link in the library directory, rather than the library file:

     • ( /opt/vertica/lib, on 32-bit clients

     • /opt/vertica/lib64, on 64-bit clients

     For example, the symbolic link for the 64-bit ODBC driver library is:

     /opt/vertica/lib64/libverticaodbc.so
     

     The symbolic link always points to the most up-to-date version of the Vertica client ODBC library. Use this link so that you do not need to update all of your DSNs when you update your client drivers.

   • Database – The name of the database running on the server. This example uses vmartdb for the vmartdb.

   • ServerName — The name of the server where Vertica is installed. Use localhost if Vertica is installed on the same machine.

     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.

   • UID — Either the database superuser (same name as database administrator account) or a user that the superuser has created and granted privileges. This example uses the user name dbadmin.

   • PWD —The password for the specified user name. This example leaves the password field blank.

   • Port — The port number on which Vertica listens for ODBC connections. For example, 5433.

   • ConnSettings — Can contain SQL commands separated by a semicolon. These commands can be run immediately after connecting to the server.

   • SSLKeyFile — The file path and name of the client's private key. This file can reside anywhere on the system.

   • SSLCertFile —The file path and name of the client's public certificate. This file can reside anywhere on the system.

   • Locale — The default locale used for the session. By default, the locale for the database is: en_US@collation=binary (English as in the United States of America). Specify the locale as an ICU Locale. See the ICU User Guide (http://userguide.icu-project.org/locale) for a complete list of parameters that can be used to specify a locale.

   • PreferredAddressFamily:

     The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name, one of the following:

     • ipv4: Connect to the server using IPv4.

     • ipv6: Connect to the server using IPv6.

     • none: Use the IP address provided by the DNS server.

For example:

[VMart]
Description = Vmart Database
Driver = /opt/vertica/lib64/libverticaodbc.so
Database = vmartdb
Servername = host01
UID = dbadmin
PWD =
Port = 5433
ConnSettings =
AutoCommit = 0
SSLKeyFile = /home/dbadmin/client.key
SSLCertFile = /home/dbadmin/client.crt
Locale = en_US@collation=binary

See ODBC DSN connection properties for a complete list of parameters including Vertica-specific ones.

Using an odbcinst.ini file

Instead of giving the path of the ODBC driver library in your DSN definitions, you can use the name of a driver defined in the odbcinst.ini file. This method is useful method if you have many DSNs and often need to update them to point to new driver libraries. It also allows you to set some additional ODBC parameters, such as the threading model.

Just as in the odbc.ini file, odbcinst.ini has sections. Each section defines an ODBC driver that can be referenced in the odbc.ini files.

In a section, you can define the following parameters:

• Description— Additional information about the data source.

• Driver— The location and designation of the Vertica ODBC driver, such as /opt/vertica/lib64/libverticaodbc.so

For example:

[Vertica]
Description = Vertica ODBC Driver
Driver = /opt/vertica/lib64/libverticaodbc.so

Then, in your odbc.ini file, use the name of the section you created in the odbcinst.ini file that describes the driver you want to use. For example:

[VMart]
Description = Vertica Vmart database
Driver = Vertica

If you are using the unixODBC driver manager, you should also add an ODBC section to override its standard threading settings. By default, unixODBC serializes all SQL calls through ODBC, which prevents multiple parallel loads. To change this default behavior, add the following to your odbcinst.ini file:

[ODBC]
Threading = 1

Configuring additional ODBC settings

On Linux and UNIX systems, you need to configure some additional driver settings before you can use your DSN. See ODBC driver settings for details.

1.1 - Testing an ODBC DSN using isql

The unixODBC driver manager includes a utility named isql, which is a simple ODBC command-line client. It lets you to connect to a DSN to send commands and receive results, similarly to vsql.

To use isql to test a DSN connection:

1. Run the following command:

   $ isql –v DSNname
   

   Where DSNname is the name of the DSN you created.

   A connection message and a SQL prompt display. If they do not, you could have a configuration problem or you could be using the wrong user name or password.

2. Try a simple SQL statement. For example:

   SQL> SELECT table_name FROM tables;
   

   The isql tool returns the results of your SQL statement.

2 - Creating an ODBC DSN for windows clients

To create a DSN for Microsoft Windows clients, you must perform the following tasks:

2.1 - Setting up an ODBC DSN

A Data Source Name (DSN) is the ODBC logical name for the drive and other information the database needs to access data. The name is used by Internet Information Services (IIS) for a connection to an ODBC data source.

This section describes how to use the Vertica ODBC Driver to set up an ODBC DSN. This topic assumes that the driver is already installed, as described in Installing Client Drivers on Windows.

To set up a DSN

1. Open the ODBC Administrator. For example, you could navigate to Start > Control Panel > Administrative Tools > Data Sources (ODBC).

   Note

   The method you use to open the ODBC Administrator depends on your version of Windows. Differences between Windows versions and Start Menu customizations could require you to take a different action to open the ODBC Administrator.

2. Decide if you want all users on your client system to be able to access to the DSN for the Vertica database.

   • If you want all users to have access, then click the System DSN tab.

   • Otherwise, click the User DSN tab to create a DSN that is only usable by your Windows user account.

3. Click Add to create a new DSN to connect to the Vertica database.

4. Scroll through the list of drivers in the Create a New Data Source dialog box to locate the Vertica driver. Select the driver, and then click Finish.

   Note

   If you have installed more than one version of the Vertica client drivers on your Windows client system, you may see multiple versions of the driver in this list. Choose the version that you know is compatible with your client application and Vertica Analytic Database server. If you are unsure, use the latest version of the driver.

   The Vertica ODBC DSN configuration dialog box appears.

5. Click the More >>> button to view a description of the field you are editing and the connection string defined by the DSN.

6. Enter the information for your DSN. The following fields are required:

   • DSN Name — The name for the DSN. Clients use this name to identify the DSN to which they want to connect. The DSN name must satisfy the following requirements:

     • Its maximum length is 32 characters.

     • It is composed of ASCII characters except for the following: { } , ; ? * = ! @ \

     • It contains no spaces.

   • Server — The host name or IP address of the Vertica server to which you want to connect. Use localhost, if Vertica is installed on the same machine.

     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.

     The PreferredAddressFamily option is available on the Client Settings tab.

   • Backup Servers — A comma-separated list of host names or IP addresses used to connect to if the server specified by the Server field is down. Optional.

   • Database —The name of the Vertica database.

   • User Name — The name of the user account to use when connecting to the database. If the application does not supply its own user name when connecting to the DSN, this account name is used to log into the database.

   The rest of the fields are optional. See DSN Parameters for detailed information about the DSN parameters you can define.

7. If you want to test your connection:

   1. Enter at least a valid DSN name, Server name, Database, and either User name or select Windows authentication.

   2. If you have not selected Windows authentication, you can enter a password in the Password box. Alternately, you can select Password for missing password to have the driver prompt you for a password when connecting.

      Caution

      Passwords entered into the Password box are saved, in plaintext, to the Windows registry.

   3. Click Test Connection.

8. When you have finished editing and testing the DSN, click OK. The Vertica ODBC DSN configuration window closes, and your new DSN is listed in the ODBC Data Source Administrator window.

9. Click OK to close the ODBC Data Source Administrator.

After creating the DSN, you can test it using Microsoft Excel 2007.

Setting up a 32-Bit DSN on 64-Bit versions of Microsoft windows

On 64-bit versions of Windows, the default ODBC Data Source Administrator creates and edits DSNs that are associated with the 64-bit Vertica ODBC library.

Attempting to use these 64-bit DSNs with a 32-bit client application results in an architecture mismatch error. Instead, you must create a specific 32-bit DSN for 32-bit clients by running the 32-bit ODBC Administrator usually located at:

c:\Windows\SysWOW64\odbcad32.exe

This administrator window edits a set of DSNs that are associated with the 32-bit ODBC library. You can then use your 32-bit client applications with the DSNs you create with this version of the ODBC administrator.

2.2 - Encrypting passwords on ODBC DSN

When you install an ODBC driver and create a Data Source Name (DSN) the DSN settings are stored in the registry, including the password. Encrypting passwords on ODBC DSN applies only to Windows systems.

Encrypting passwords on an ODBC data source name (DSN) provides security against unauthorized database access. The password is not encrypted by default and is stored in plain-text.

Enable password encryption

Use the EncryptPassword parameter to enable or disable password encryption for an ODBC DSN:

• EncryptPassword = true enables password encryption

• EncryptPassword = false (default) disables password encryption

Set EncryptPassword in the Windows registry - HKEY_LOCAL_MACHINE > Software > Vertica > ODBC > Driver EncryptPassword=<true/false>.

Encrypted passwords get updated in the following registry locations:

For a user DSN:

HKEY_CURRENT_USER-> Software -> ODBC -> ODBC.INI -> DSNNAME -> PWD

For a system DSN:

HKEY_LOCAL_MACHINE-> Software -> ODBC -> ODBC.INI -> DSNNAME -> PWD

Verify password encryption

Use Windows Registry editor to determine if password encryption is enabled based on the value of EncryptPassword. Depending on the type of DSN you installed, check the following:

For a user DSN: HKEY_CURRENT_USER > Software > ODBC > ODBC.INI > dsn name > isPasswordEncrypted=<1/0>

For a system DSN: HKEY_LOCAL_MACHINE > Software > ODBC > ODBC.INI > dsn name > isPasswordEncrypted=<1/0>

For each DSN, the value of the isPasswordEncrypted parameter indicates the status of the password encryption, where 1 indicates an encrypted password and 0 indicates an unencrypted password.

2.3 - Testing an ODBC DSN using Excel

You can use Microsoft Excel to verify that an application can connect to an ODBC data source or other ODBC application.

1. Open Microsoft Excel, and select Data > Get External Data > From Other Sources > From Microsoft Query.

2. When the Choose Data Source dialog box opens:

   1. Select New Data Source, and click OK.

   2. Enter the name of the data source.

   3. Select the Vertica driver.

   4. Click Connect.

3. When the Vertica Connection Dialog box opens, enter the connection information for the DSN, and click OK.

4. Click OK on the Create New Data Source dialog box to return to the Choose Data Source dialog box.

5. Select VMart_Schema*, and verify that the Use the Query Wizard check box is deselected. Click OK.

6. When the Add Tables dialog box opens, click Close.

7. When the Microsoft Query window opens, click the SQL button.

8. In the SQL window, write any simple query to test your connection. For example:

   SELECT DISTINCT calendar_year FROM date_dimension;
   

9. * If you see the caution, "SQL Query can't be represented graphically. Continue anyway?" click **OK**.      * The data values 2003, 2004, 2005, 2006, 2007 indicate that you successfully connected to and ran a query through ODBC.
   

10. Select File > Return Data to Microsoft Office Excel.

11. In the Import Data dialog box, click OK.

    The data is now available for use in an Excel worksheet.

3 - Creating an ODBC DSN for macOS clients

You can use the Vertica ODBC Driver to set up an ODBC DSN. This procedure assumes that the driver is already installed, as described in Installing the ODBC client driver.

Setting up a DSN

1. Using your web browser, download and install the Apple ODBC Administrator Tool.

2. Locate and open the ODBC Administrator Tool after installation:

   1. Navigate to Finder > Applications > Utilities.

   2. Open the ODBC Administrator Tool.

3. Click the Drivers tab, and verify that the Vertica driver is installed.
   

4. Specify if you want all users on your client system to be able to access the DSN for the Vertica database:

   • If you want all users to have access, then click the System DSN tab.

   • Otherwise, click the User DSN tab to create a DSN that is only usable by your Macintosh user account.

5. Click Add... to create a new DSN to connect to the Vertica database.

6. Scroll through the list of drivers in the Choose A Driver dialog box to locate the Vertica driver. Select the driver, and then click OK. A dialog box opens that requests DSN parameter information.

7. In the dialog box, enter the Data Source Name (DSN) and an optional Description. To do so, click Add to insert keywords (parameters) and values that define the settings needed to connect to your database, including database name, server host, database user name (such as dbadamin), database password, and port. Then, click OK.

8. In the ODBC Administrator dialog box, click Apply.

   See ODBC DSN connection properties for a complete list of parameters including those specific to Vertica.

After configuring the ODBC Administrator Tool, you may need to configure additional driver settings before you can use your DSN, depending on your environment. See Additional ODBC Driver Configuration Settings for details.

3.1 - Testing an ODBC DSN using iodbctest

The standard iODBC Driver Manager on OS X includes a utility named iodbctest that lets you test a DSN to verify that it is correctly configured. You pass this command a connection string in the same format that you would use to open an ODBC database connection. After configuring your DSN connection, you can run a query to verify that the connection works.

For example:

# iodbctest "DSN=VerticaDSN;UID=dbadmin;PWD=password"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008
Driver: 07.01.0200 (verticaodbcw.so)
SQL> SELECT table_name FROM tables;
table_name
--------------------------------------------------------------------------------------------------------------------------------
customer_dimension
product_dimension
promotion_dimension
date_dimension
vendor_dimension
employee_dimension
shipping_dimension
warehouse_dimension
inventory_fact
store_dimension
store_sales_fact
store_orders_fact
online_page_dimension
call_center_dimension
online_sales_fact
numbers
result set 1 returned 16 rows.

4 - ODBC DSN connection properties

The following tables list the connection properties you can set in the DSNs for use with Vertica's ODBC driver. To set these parameters, see Setting DSN connection properties.

Required connection properties

These connection properties are the minimum required to create a functioning DSN.

Optional properties

Advanced settings

Identification

OAuth connection properties

The following connection properties pertain to OAuth in ODBC.

Encryption

Third-party compatibility

Kerberos connection properties

Use the following properties for client authentication using Kerberos.

See also

5 - Setting DSN connection properties

The properties in the following tables are common for all user and system DSN entries. The examples provided are for Windows clients.

To edit DSN properties:

• On UNIX and Linux client platforms, you can edit the odbc.ini file. The location of this file is specific to the driver manager. See Creating an ODBC DSN for Linux.

• On Windows client platforms, you can edit some DSN properties using the Vertica ODBC client driver interface. See Creating an ODBC DSN for windows clients.

• You can also edit the DSN properties directly by opening the DSN entry in the Windows registry (for example, at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DSNname). Directly editing the registry can be risky, so you should only use this method for properties that cannot be set through the ODBC driver's user interface, or via your client code.

• You can set properties in the connection string when opening a connection using the SQLDriverConnect() function:

  sqlRet = SQLDriverConnect(sql_hDBC, 0, (SQLCHAR*)"DSN=DSNName;Locale=en_GB@collation=binary", SQL_NTS, szDNS, 1024,&nSize, SQL_DRIVER_NOPROMPT);
  

  Note

  In the connection string ';' is a reserved symbol. If you need to set multiple properties as part of the ConnSettings property use '%3B' in place of ';'. Also use '+' instead of spaces.

  For example:

  sqlRet = SQLDriverConnect(sql_hDBC, 0, (SQLCHAR*)"DSN=Vertica SQL;ConnSettings=set+search_path+to+a,b,c%3Bset+locale=ch;SSLMode=prefer", SQL_NTS,
  szDNS, 1024,&nSize, SQL_DRIVER_NOPROMPT);
  

• Your client code can retrieve DSN property values after a connection has been made to Vertica using the SQLGetConnectAttr() and SQLGetStmtAttr() API calls. Some properties can be set and using SQLSetConnectAttr() and SQLSetStmtAttr().

  For details of the list of properties specific to Vertica see ODBC Header Files specific to Vertica.