|Share this article:|
Vertica Integration with DataSunrise: Connection Guide
To read a PDF version of this article, click here.
About Vertica Connection Guides
Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one version of Vertica and one version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested those other versions.
Vertica and DataSunrise Versions Tested
DataSunrise Database Security Suite 3.7.9
Windows 10 Professional using Chrome and Firefox browsers
Vertica ODBC driver 9.0.1-0 (Windows 10 and Linux CentOS 7.3 with unixODBC)
Vertica server 9.0
|Server Platform||Linux CentOS 7.3|
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.
- First check the DataSunrise prerequisites documented in the DataSunrise Database Security Suite Admin Guide. To find the Admin Guide:
- On the DataSunrise website, open the Support tab.
- Select the Admin Guide for your platform.
- Supply the information required for download.
- 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.
- On the home page of the DataSunrise website, click Download to download DataSunrise Database Security Suite.
- During the download, supply the information requested, and select Vertica from the Database drop-down list.
- 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.
Note During testing, we tried installing DataSunrise and Vertica on the same Linux host and encountered no issues. However, DataSunrise recommends installation on separate hosts for production use. See Known Limitations for details.
Install the 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.
Download the Vertica Client
- Navigate to the Vertica Client Drivers page.
- Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version. The Vertica client package includes client components and all the drivers.
Note Starting in Vertica 8.1.1, the ODBC drivers are both forward and backwards compatible. For details, see Client Driver and Server Version Compatibility in the Vertica documentation.
Install the ODBC Driver and Create 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.
Note Special considerations for Linux ODBC driver configuration:
- Vertica documentation says that using the odbcinst.ini method of single reference driver path for multiple DSNs is optional. DataSunrise does not use DSNs for connecting, so it requires that the odbcinst.ini be present and configured with a name/value pair for the driver. If odbcinst.ini is not found or not properly configured, DataSunrise cannot find the driver and returns an error.
- DataSunrise uses the ODBC driver to connect to Vertica, so the vertica.ini must be used and the name/value pair for the ErrorMessagesPath must be set. If not set, the ODBC driver cannot find the text for the ODBC error codes.
See Troubleshooting for more details.
Connect DataSunrise to Vertica
Once you have installed and configured the Vertica ODBC driver for your database, you are ready to connect DataSunrise to Vertica. Follow these steps:
- Open the DataSunrise User Guide. Go to the "DataSunrise Configurations" chapter and find the "Managing Databases" topic.
- Follow the instructions for launching the DataSunrise web interface.
- On the Configurations tab of the web interface, expand the Databases menu and click New Database Profile.
- Provide the details about your database in the New Database Profile form.
Here is an example of a completed form:
Note The port number in the Proxy or Similar Parameters section is the port for client connections to the proxy.
See Known Limitations in this document and "Database Profiles Proxy" in the DataSunrise User Guide for more details about configuration considerations for this port number.
- Click Test Connection to ensure that DataSunrise can connect to the database.
- If the connection is successful, 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.
- When the loading process is complete, you can verify your connection as follows:
- On the Configurations tab, expand the Database Users menu.
- Verify that your Vertica users are defined in the target database.
For further configuration and testing of DataSunrise with Vertica, see the DataSunrise User Guide.
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
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 in which to look up the error code.
- 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.
- If the Vertica host in the profile goes down, the proxy will fail to connect to the database. Currently there is no provision for a failover host. 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.
- Although planned for a future release, 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.