Troubleshooting Posit Professional Drivers

How to troubleshoot your system’s configuration if you are having issues with connecting to your databases from RStudio.

Keeping 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.

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

• Linux
• macOS
• Windows

Linux

If you are having problems connecting to your databases from RStudio, it’s likely that your system isn’t correctly configured.

We recommend completing the following steps before contacting support:

1. Make sure you’ve 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. Driver paths are stored in odbcinst.ini. DSN’s are stored in odbc.ini. 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 a new connection is created.

Testing 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:

   File:

   [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

Finding ODBC configuration files

ODBC configuration files are typically located 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

Identifying 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)

Enabling 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 (e.g., /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:

   File:

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

Opening a support ticket

If the steps above didn’t identify and resolve the connection issue, please reach out to support@posit.co and include the following:

• Specific error you’re seeing
• The command that’s 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, please see the Posit Support documentation.

The Connections pane doesn’t show installed drivers

If you’re using Posit Workbench, the Connections pane can be used to establish connections to external sources.

• When you click on New Connection, you should be presented with a list of existing DSNs along with all installed ODBC drivers.
• If the list of ODBC drivers is missing, it’s likely due to /etc/odbcinst.ini.
• If that file contains lines that read Installer = RStudio, change them to read Installer = RStudio Pro Drivers and the drivers should show up in the New Connection window within Workbench.

macOS

If you are having 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 odbc is installed via Homebrew, system data source names (DSNs) are defined at /opt/homebrew/etc/odbc.ini.

If ODBC has trouble locating your system data source names, you could 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 below:

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

Make sure you’ve 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.

Windows uses the ODBC Data Source Administrator to manage ODBC drivers. Testing connections can be done from the ODBC Data Source Administrator, and driver logging can be enabled 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 drop-down menu, select the desired level of information to include in log files.
4. In the Log Path field, type the full path to the folder where you want to save log files.
5. In the Max Number Files field, type the maximum number of log files to keep. After the maximum number of log files is reached, each time and additional file is created, the driver deletes the oldest log file.
6. In the Max File Size field, type the maximum size of each log file in megabytes (MB). After the maximum file size is reached, the driver creates a new file and continues logging.
7. Click OK.
8. Retry the connection.

The driver produces log output at the location provided to the Log Path field. If you enable the UseLogPrefix connection property, the driver prefixes the log file name with the user name associated with the connection. And, the process ID of the application through which the connection is made.

Contact support

If the steps above don’t identify and resolve the connection issue, please contact support@posit.co. Include the specific error you’re seeing, the command that’s generating the error, the DSN definition, and the resulting driver log files. Remove any sensitive information (username, password) from the DSN definition.