An ODBC (Open Database Connectivity) Connection exposes the LabKey schema and queries as a data source to external clients for analysis and reporting. This topic outlines using an ODBC connection with those tools:
Overview
Step 1: Before you can set up any of the tools covered in this topic, an administrator must have configured LabKey Server to accept ODBC connections as covered in this topic:
Step 2: Your local client machine must also have been setup following the directions in this topic, written for a Windows client. A similar process will need to be followed for other client machines
Step 3: Configure the external tool to use that data source, as described in this topic for several common tools. Other tools offering ODBC support may also be able to connect using similar steps.
The underlying exposure mechanism is an implementation of the
PostgreSQL wire protocol. Each LabKey container (a project or folder) is surfaced to clients as a separate PostgreSQL "database". These "databases" expose the LabKey virtual schema (the same view of the data provided by the Query Schema Browser).
Queries through an ODBC connection respect all of the security settings present on the LabKey Server container. Clients must have at least the Reader role to access/query the data. Only read access is supported; data cannot be inserted or updated using the virtual schema over an ODBC connection.
Tableau Desktop
To load data into Tableau Desktop:
- In Tableau Desktop, go to Data > New Data Source > More… > Other Databases (ODBC).
- Place a checkmark next to DSN and select your DSN in the dropdown. Click Connect.
- The Database field in the data source configuration should contain the container path to the project and folder on the LabKey Server. Shown below, the project named "Testing".
- Search for and select the Schema - 'core' is shown in the screenshot below.
- Search for and select the Table - 'Modules' is shown in the screenshot below.
We recommend that you set the Connection to "Extract" instead of "Live". (This helps to avoid the following errors from the ODBC driver: "ODBC escape convert error".)
Excel
To load data into Excel:
- In Excel, open an empty sheet and click the Data tab
- Select Get Data > From Other Sources > From ODBC. (Note this path may vary, depending on your version of Excel.)
- In the From ODBC popup dialog, select the system Data Source Name (DSN) you created; shown below it is named "MyLabKeyData", and will have been defined with a path to the container on LabKey containing the data you plan to access. Optionally, you can enter a SQL query under Advanced options.
- If you chose not to provide a SQL query, select the table to load using the Navigator dialog. Select the desired table and click Load.
- The data will be selected from the server and loaded into the worksheet.
Controlling Excel Data Loading
To control the SQL SELECT statement used by Excel to get the data, such as adding a WHERE or JOIN clause, double-click the table/query in the
Queries and Connections panel. In the
Power Query Editor, click
Advanced Editor.
To control the refresh behavior, go to
Data tab > Connections > Properties. The
Refresh control panel provides various options, such as refreshing when the sheet is opened.
Note that saving a sheet creates a snapshot of the data locally. Use with caution if you are working with PHI or otherwise sensitive data.
Access
Access can be run in snapshot or dynamic modes. Loading data into Access also provides a path to processing in Visual Basic.
See the Microsoft documentation at
Add an ODBC Data Source
Microsoft SQL Server Reporting Services (SSRS)
SQL Server Reporting Services is used for creating, publishing, and managing reports, and delivering them to the right users in different ways, whether that's viewing them in a web browser, on their mobile device, or via email.
To use SSRS, you must
install both the 32-bit and 64-bit ODBC drivers on your Windows machine. Visual Studio and Report Builder are 32 bit and recent versions of SSRS and MS SQL Server are 64 bit.
Important: give both the 32-bit and 64-bit ODBC data sources the same name.
Setting up both the Reporting Services and Visual Studio are covered in this topic:
MATLAB
- In MATLAB, click the Apps tab.
- Open the Database Explorer app. (If you don't see it, install the Database Toolbox.)
- In the Database Explorer, click New Query.
- In the Connect to a Data Source select your DSN and provide a username and password.
- In the popup dialog, select the target Schema. The Catalog (a LabKey container) is determined by the DSN.
- Select a table to generate SQL statements.
Other Tools
These other external tools have not been extensively tested and are not officially supported, but have been reported to be compatible with LabKey using ODBC connections.
Troubleshoot Common Errors
Error Messages (may vary by tool) | Possible cause(s) and solutions |
---|
ERROR: database path … could not be resolved to an existing container | Check that the path ("Database" in your Data Source Name configuration) exists, and that your credential has access to it. Confirm that there are no leading or trailing spaces in the path in your DSN configuration. |
Password authentication failed | Check that the password is correct. This error also occurs when your DSN configuration does not include a port number. |
Connection refused Is the server running on host "hostname.com" and accepting TCP/IP connections on port 5435? | Ensure that the LabKey Server has enabled ODBC connections, that you have the right host name and port, and that firewalls or other network configurations are not blocking TCP network traffic. |
SSL error: certificate verify failed | Your client tool is attempting to validate the server's certificate and does not trust it. Try the "require" option for the SSL Mode, and ensure that the server's certificate has been added to your root.crt file. |
SSL error: unsupported protocol | The server and client failed to negotiate a TLS protocol. Ask your server admin to check the protocol and ciphers setting in Tomcat's server.xml file, and validate that the configured keystore is valid. Or switch the LabKey Server ODBC configuration to use the self-signed certificate option. |
Debug with Loggers
To get additional detail about ODBC connections, you can temporarily turn on
DEBUG logging for the following loggers:
- org.labkey.connectors.postgresql.SocketListener : listener for incoming ODBC connections. This logger will record new connections and the port accessed (whether or not said connections are actually successful).
- org.labkey.connectors.postgresql.SocketConnection : See more detail about packets, protocols, and queries. This logger will record credential authentication issues, "Database" path accessibility issues, and other details that may point to details to correct.
For example, if you use a "Database" path that exists, but includes a trailing space, the connection will fail, but the client error reported may collapse such white space. Using debug logging you can see the issue more clearly (in the quoted string):
DEBUG SocketListener 2022-02-09T08:45:36,304 PostgreSQL Listener : New connection for Postgres socket 5435
DEBUG SocketConnection 2022-02-09T08:45:36,324 Postgres wire protocol 4 : First packet protocol version: 1234.5679
DEBUG SocketConnection 2022-02-09T08:45:36,327 Postgres wire protocol 4 : Protocols set in server config - TLSv1.2
DEBUG SocketConnection 2022-02-09T08:45:36,351 Postgres wire protocol 4 : First packet protocol version: 3.0
DEBUG SocketConnection 2022-02-09T08:45:36,351 Postgres wire protocol 4 : Properties: {database=/Tutorials/HIV Study , user=[username]}
DEBUG SocketConnection 2022-02-09T08:45:36,351 Postgres wire protocol 4 : Sending error to client: database path "/Tutorials/HIV Study " could not be resolved to an existing container
Tableau, Date Fields, and Escape Convert Errors
If you are working with date fields in Tableau Desktop and see an error like
Error message: "Bad Connection..." Bad Connection: Tableau could not connect to the data source.
ODBC escape convert error
<snip>Generated SQL statement is shown here</snip>
Edit the DSN you are using for connecting Tableau Desktop and confirm you selected "Extract". See
above for a screenshot.
Related Topics