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.