Troubleshoot database connections

Troubleshoot your system configuration if you have issues connecting to your databases from Posit products.
ImportantKeep ODBC drivers up to date

We strongly recommend upgrading drivers to prevent security and administrative issues. Update drivers system-wide with at least the same frequency as other Posit products.

Review the following sections that are appropriate for your distribution before contacting support:

Linux

If you have problems connecting to your databases from the RStudio IDE, your system likely has incorrect configuration.

We recommend completing the following steps before contacting support:

  1. Make sure you followed any additional steps for your specific driver listed in the installation documentation. For example, when using the Oracle driver, this means downloading and configuring the Oracle Instant Client.
  2. Test your connection from the command line with the isql tool, which is part of unixODBC. Set up a data source name (DSN) first.
  3. Review your configuration files for typos and other missed configurations. odbcinst.ini stores driver paths. odbc.ini stores DSNs. Locate these files with the odbcinst -j command.
  4. Examine specific driver dependencies with the ldd tool.
  5. Enable driver logging to identify problems when you create a new connection.

Test a database connection outside of R

You should test your database connections from the command line before creating new connections in RStudio.

  1. You can test connections by running isql, a command line tool for interactive SQL.

    isql requires you to create a data source name (DSN) first.

  2. To create a new DSN, open odbc.ini in a text editor and create a new test entry:

    [test]
Driver = SQLServer
Server = my.server.name
Database = dbname
Port = 1433

    The driver entry in the DSN should point to the corresponding driver name or path listed in odbcinst.ini.

  3. To test this connection from a terminal, enter:

    isql -v test

Find ODBC configuration files

ODBC configuration files typically reside in /etc/.

You can find the location of your specific odbc.ini and odbcinst.ini files by running the odbcinst -j command:

odbcinst -j
unixODBC 2.3.16
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Identify ODBC driver dependencies

ODBC drivers on Linux are shared objects with filenames ending in *.so. You can print the shared object dependencies with the ldd command. This is useful in identifying missing dependencies.

ldd sqlserver/bin/lib/libsqlserverodbc_sb64.so
  linux-vdso.so.1 =>  (0x00007fff4ffe2000)
  libdl.so.2 => /lib64/libdl.so.2 (0x00007f96c8b4e000)
  libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f96c8932000)
  librt.so.1 => /lib64/librt.so.1 (0x00007f96c8729000)
  libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f96c8421000)
  libm.so.6 => /lib64/libm.so.6 (0x00007f96c811f000)
  libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f96c7f08000)
  libc.so.6 => /lib64/libc.so.6 (0x00007f96c7b47000)
  /lib64/ld-linux-x86-64.so.2 (0x00007f96cb7ba000)

Enable driver logging

You can enable driver logging to track activity and troubleshoot issues:

  1. In a text editor, open your driver configuration file found in the bin/lib subdirectory of the driver installation path, for example, /opt/rstudio-drivers/sqlserver/bin/lib/rstudio.sqlserverodbc.ini.

  2. Set values for LogLevel (e.g., 6) and LogPath (e.g., /tmp).

  3. Ensure that users have permissions to write files to the path specified in LogPath.

  4. Set UseLogPrefix=1 to create a unique log for every user and process with a connection.

    Logging decreases performance and can consume a large quantity of disk space. Only enable logging long enough to capture an issue.

    [Driver]
LogLevel=6
LogPath=/tmp
UseLogPrefix=1

Open a support ticket

If the steps above did not identify and resolve the connection issue, contact support@posit.co and include the following:

  • Specific error you are seeing
  • The command that is generating the error, the DSN definition (found in odbc.ini) being used
  • The resulting driver log files
  • Feel free to remove any sensitive information (username, password) from the DSN file

For more information about opening a support ticket, see the Posit Support page.

The Connections pane does not show installed drivers

If you use Posit Workbench, you can use the Connections pane to establish connections to external sources.

When you click New Connection, you should see a list of existing DSNs along with all installed ODBC drivers. If the list of ODBC drivers does not appear, check /etc/odbcinst.ini. If that file contains lines that read Installer = RStudio, change them to read Installer = RStudio Pro Drivers and the drivers should appear in the New Connection window within Workbench.

macOS

If you have trouble connecting to a database from RStudio Desktop Pro on macOS, the Linux troubleshooting steps above also apply.

macOS x86_64

  • System data source names (DSNs) are defined at /usr/local/etc/odbc.ini.
  • For the corresponding RStudio version, drivers are installed to:
    • RStudio <=1.3: /Users/<user>/.rstudio-desktop/odbc/drivers
    • RStudio 1.4+: /Users/<user>/.local/share/rstudio/odbc/drivers

macOS aarch64

If you install odbc via Homebrew, define system data source names (DSNs) at /opt/homebrew/etc/odbc.ini.

If ODBC has trouble locating your system data source names, you may need to override the default location where ODBC looks for your configuration files.

To specify the location of your DSN configuration files, use one of the following options:

Option 1: Save setting to ~/.Renviron

  1. Create or open the ~/.Renviron file.
  2. Add ODBCSYSINI=/opt/homebrew/etc to your ~/.Renviron file and save your changes.
  3. Before connecting to a database, restart any open R sessions.

Option 2: Set ODBCSYSINI environment variable

  1. Set the ODBCSYSINI environment variable, e.g., ODBCSYSINI=/opt/homebrew/etc.
  2. Before connecting to a database, restart any open R sessions.

Windows

Follow any additional steps for your specific driver listed in the installation documentation. For example, when using the Oracle driver, download and configure the Oracle Instant Client.

Windows uses the ODBC Data Source Administrator to manage ODBC drivers. You can test connections from the ODBC Data Source Administrator, and enable driver logging with the following steps:

  1. To access logging options, open the ODBC Data Source Administrator where you created the DSN and select the DSN.
  2. Select Configure and then click Logging Options.
  3. From the Log Level dropdown, select the desired level of information to include in log files.
  4. In the Log Path box, enter the full path to the folder where you want to save log files.
  5. In the Max Number Files box, enter the maximum number of log files to keep. After you reach the maximum number of log files, the driver creates an additional file and deletes the oldest log file.
  6. In the Max File Size box, enter the maximum size of each log file in megabytes (MB). After the log file reaches the maximum file size, the driver creates a new file and continues logging.
  7. Click OK.
  8. Retry the connection.

The driver produces log output at the location you provide in the Log Path box. If you enable the UseLogPrefix connection property, the driver prefixes the log file name with the connection’s user name and the process ID of the application through which you make the connection.

Contact support

If the steps above do not identify and resolve the connection issue, contact Posit Support. Include the specific error you are seeing, the command that is generating the error, the DSN definition, and the resulting driver log files. Remove any sensitive information (username, password) from the DSN definition.