Vertica Integration with DataSunrise: Connection Guide

Vertica connection guides provide basic instructions for connecting a third‑party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and DataSunrise: Latest Versions Tested

Software	Version
Partner Client	DataSunrise Database Security Suite 8.1.10-3489
Desktop Platform	Windows 10 Professional using Chrome and Firefox browsers
Vertica Client	Vertica ODBC driver 12.0.0-0 (Windows 10 and Linux Redhat 7.9 with unixODBC)
Vertica Server	Vertica server 12.0.0
Server Platform	Linux Redhat 7.9

DataSunrise Overview

DataSunrise Database Security is an application firewall that protects relational databases from hacker attacks and insider-driven threats. DataSunrise runs on Windows and Linux operating systems. It runs independently of any applications and with no unnecessary load on the database server.

DataSunrise can perform the following tasks:

• Data Auditing: Logs all user actions, SQL queries, and query results to an internal database for auditing. The results can be exported to an external system.
• Data Protection: Acts as a database firewall. Analyzes database traffic and detects and prevents unauthorized queries and SQL injections on-the-fly. Resulting alerts and reports on detected threats can be sent to administrators for action.
• Data Masking: Prevents sensitive data exposure by obfuscating output of sensitive data and replacing it with random or real-looking data. The masking feature includes both dynamic and static data masking using a variety of masking algorithms.
• Data Discovery: Scans databases, locating personally-identifiable information (PII) and electronic protected health information (ePHI). Creates a security or masking rule for the columns with sensitive data.

Installing DataSunrise

1. First check the DataSunrise prerequisites documented in the DataSunrise Database Security Suite Admin Guide. To find the Admin Guide:
   1. On the DataSunrise website, open the Support tab.
   2. Select the Admin Guide for your platform.
   3. Supply the information required for download.
   4. Click Download.

   Note Prerequisites, installation, configuration, and usage information are available in the DataSunrise admin and user guides on the Support tab of the DataSunrise website.

2. On the home page of the DataSunrise website, click Download to download DataSunrise Database Security Suite.
3. During the download, supply the information requested, and select Vertica from the Database drop-down list.
4. Follow the instructions in the Admin Guide to install DataSunrise Database Security Suite and license. You can choose to install a 30-day trial license for evaluation.

Installing the Vertica Client Driver

DataSunrise uses an ODBC Data Source Name (DSN) to connect to Vertica. To create the DSN, download the Vertica client package and install the driver, then configure the DSN in the ODBC Driver Manager.

Downloading the Vertica Client

1. Navigate to the Vertica Client Drivers page.
2. Download the version of the Vertica client package that is compatible your Vertica server version.

Installing the ODBC Driver and Creating the DSN

Follow the instructions in the Vertica documentation:

• To install the ODBC driver, see Client Drivers.
• To create the DSN, see Creating an ODBC Data Source Name (DSN).

You can choose to install the complete Vertica client package or just the ODBC driver.

You will find all the details about connecting to Vertica in the Vertica documentation under Connecting to Vertica.

Connecting Vertica to DataSunrise

After you have installed and configured the Vertica ODBC driver for your database, you are ready to connect DataSunrise to Vertica. Follow these steps:

1. Open the DataSunrise User Guide. Go to the "DataSunrise Configurations" chapter and find the "Managing Databases" topic.
2. Follow the instructions for launching the DataSunrise web interface.

3. When you first enter the web interface you will be presented with an SMTP server setup form. Click Skip. This is not required during initial setup and can be configured later in the menus.

4. Expand the Configuration selection in the left menu and select Databases .
5. Click Add Database.
   The Database Connection Parameters form appears. The form fields are dynamic, so make sure to choose Database Type “Vertica” first to get the appropriate fields to fill in.

   Here is an example of a completed form:

   

6. Click Test Connection to ensure that DataSunrise can connect to the database.
7. If the connection is successful, successful some more fields are presented below the Test button.

   

   Note The port number in the Proxy field section is the port for client connections to the proxy.

   See Known Limitations in this document and the DataSunrise User Guide for more details about configuration considerations for this port number.

8. Click Save to save the profile for your database.

   A status message notifies you that DataSunrise is loading object information from each of the schemas in your database. This can take some time depending on the number of objects in your database.

9. When the loading process is complete, you can verify your connection between DataSunrise and Vertica as follows:
   1. In the Configuration menu, select Database Users.
   2. Verify that your Vertica users are defined in the target database.

      For example:

      

10. You can verify if a client application can access Vertica via the DataSunrise proxy by using your favorite SQL client tool, example, DBVisualizer. Connect as you normally would but use the ip address/hostname of the DataSunrise server instead of the Vertica server.

11. After you have connected via the proxy you can look at the System Events in the Monitoring menu to see the log in activity captured.
    
    

For further configuration and testing of DataSunrise with Vertica, see the DataSunrise User Guide.

Troubleshooting

• If you do not have an odbcinst.ini file, or if the name/value pair for Driver64/driver_path is not present or is incorrect, then the following error occurs when you test the connection or try to connect for the first time:

  Error code 0 [unixODBC][Driver Manager]Can't open lib 'Vertica' : file not found. . Used connection string 'Driver=Vertica;Server=10.20.71.180;Port=5433;Database=VMart;Uid=dbadmin;Pwd=XXXXXX;'  

  Note DataSunrise uses a standard ODBC method, the SQLGetInstalledDrivers function, for enumerating all installed drivers. From the list of installed drivers returned by the function, DataSunrise uses the first one with a name containing the substring Vertica.

  If you have multiple Vertica drivers in odbcinst.ini, make sure that the one you want DataSunrise to use is at the top of the list.

• If your VERTICAINI environment variable is not set to a valid readable vertica.ini file location, or if the name/value pair of ErrorMessagesPath is not set or is incorrect, then you may see the following if an error occurs:

  SELECT EXCEPTION vmart.vert_datatype_v1_0_4.varchar_table err [[DSI] The error message VPrepareError could not be found in the en-US locale. Check that /en-US/VerticaMessages.xml exists.. . errCode = -1], Query : SELECT "varchar_max_column","keycolumn","varchar_column" FROM "vert_datatype_v1_0_4"."varchar_table" LIMIT 100

  This message indicates that the Vertica driver cannot find the Vertica error messages file to look up the error code.

  Here are examples of the ODBC setup using /etc as the default location for the ini files:

  [dbadmin@vertica-node ~]$ env | grep INI
  VERTICAINI=/etc/vertica.ini
  ODBCSYSINI=/etc
  ODBCINI=odbc.ini
  ODBCINSTINI=odbcinst.ini
  
  [dbadmin@vertica-node ~]$ cat /etc/odbc.ini
  [ODBC Data Sources]
  VMart="Vertica on xx.xx.xx.xxx"
  [VMart]
  Description=Vmart Database
  Driver=Vertica
  Database=VMart
  Servername=verticahostnameorip
  UID=dbadmin
  PWD=
  Port=5433
  ConnSettings=
  AutoCommit=0
  #SSLKeyFile=/home/dbadmin/client.key
  #SSLCertFile=/home/dbadmin/client.crt
  Locale=en_US@collation=binary
  
  [dbadmin@vertica-node ~]$ cat /etc/odbcinst.ini
  # Example driver definitions
  [Vertica]
  Description=Vertica ODBC Driver
  Driver=/opt/vertica/lib64/libverticaodbc.so
  [ODBC]
  Threading = 1
  
  [dbadmin@vertica-node ~]$ cat /etc/vertica.ini
  [Driver]
  DriverManagerEncoding=UTF-16
  ODBCInstLib=/usr/lib64/libodbcinst.so
  ErrorMessagesPath=opt/vertica/en-US
  LogLevel=4
  LogPath=/tmp
  

Known Limitations

• Vertica is hard-coded to use port 5433 as the client communications port. When creating proxies for new Vertica clients, you would normally assign the new proxy to port 5433, and existing clients would easily be redirected through the DSNs. If the DataSunrise server is installed on a Vertica node then the proxy port cannot be 5433, because it is being used by the Vertica server. If you set the proxy port to a port other than 5433, then all the client side connection strings would have to be edited to use that port number. For this reason, installation of Vertica and DataSunrise on separate hosts is recommended.
• DataSunrise does not currently support backupservernode failover. If the Vertica host in the profile goes down, the proxy will fail to connect to the database. You would have to manually edit the database profile and modify the host IP address to a different Vertica host that is up, and switch it back once the failed Vertica host is replaced or repaired.
• DataSunrise can use rules filters to specify actions to take based on an application. This feature is not supported for Vertica, because client application names are not tracked in Vertica system tables.
• DataSunrise does not currently support native connection load balancing in Vertica. Currently, DataSunrise converts the host address in each client redirect to prevent clients from being redirected to alternate hosts and bypassing the proxy.

For More Information

• DataSunrise website
• Vertica Community Edition
• Vertica User Community
• Vertica Documentation