An ODBC (Open Database Connectivity) Connection exposes the LabKey schema and queries as a data source to external clients for analysis and reporting. This enables users to continue
outside tools they may already be using with data stored in LabKey.
Encrypting ODBC connections using Transport Level Security (TLS) is recommended for all deployments and are supported for both hosted and on-premise servers. Secure ODBC connections can piggyback Tomcat's TLS configuration (both certificates and keys), or the server can automatically generate a key pair and self-signed certificate. This topic describes how an administrator configures LabKey Server to accept ODBC connections.
Additional Topics
- Configure ODBC Access from Host Machine: Set up the host machine to use the ODBC connection, including installation of the TLS access certificate for each user.
- Supported ODBC Tools: Configure external tools to use LabKey Server Data.
Basic ODBC Connection Setup
Your server must include the
connectors module to support ODBC connections. This module leverages a selected port to pass data from LabKey to an external tool. Across this connection, the user will have access to the data they could also access directly from the UI or via API. The connection does not provide direct access to the database or to any schemas or tables that would not ordinarily be accessible.
To enable ODBC connections, follow the instructions below.
- Select (Admin) > Site > Admin Console.
- Under Premium Features, click External Analytics Connections.
- On the page Enable External Analytics Connections, place a check mark next to Allow Connections.
- By default the server will listen for client requests on port 5435. If desired, you can change the port number within the range: 1 to 65535.
- The other settings on this page are described in the next section.
- Click Save.
Secure ODBC Using TLS
To require that ODBC connections are encrypted, you can check the box for
Require TLS for encrypted connections. In this section you learn how to configure the LabKey Server deployment. LabKey Server can either get its configuration from Tomcat's HTTPS settings, or use a self-signed certificate. The page also shows the expiration dates for the available certificates.
Using Tomcat's HTTPS Configuration
For details see
Installation: Tomcat Configuration and
Creating & Installing SSL/TLS Certificates on Tomcat.
LabKey Server will use the certificateKeyAlias, certificateKeystoreFile, certificateKeystorePassword, and certificateKeystoreType attributes on the <Connector><SSLHostConfig><Certificate> element to configure its TLS connections. It does not support using the certificateFile, certificateChainFile, or certificateKeyFile elements.
If you have specified protocols and/or ciphers on the <Connector> or <Connector><SSLHostConfig> elements, LabKey Server will use them as well. While Tomcat supports both commas or colons as the delimiter for the ciphers list, to make it work with ODBC connections, a colon delimiter must be used in separating cipher suites. For example:
<SSLHostConfig
sslProtocol="TLSv1.3" protocols="TLSv1.3"
ciphers="TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384:TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384:
TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384"
>
Using Auto-Generated, Self-Signed Certificate
- Choose the validity period for newly generated certificates. The server will automatically generate a new certificate when the old one expires.
- Check the box to Regenerate certificate to force the server to generate a new certificate immediately.
- Click the (download) link to download the certificate.
- Click Save.
Configure PostgreSQL Client for Secure Connections
When you configure the client machine where your external tools will run, follow the guidance in this topic:
Making note of the data source name (DSN) requirements for
ensuring the connection is secure.
View ODBC Access Settings
Users can review the settings you have configured by selecting
(username) > External Tool Access. No editing is available from this page, but there is a button to
Download Certificate.
Related Topics
