Support

Troubleshooting Connections

Follow

This article answers frequently asked questions from: 

If you need more information, please refer to our website for using Databases with RStudio.

General FAQ

When I click New Connection, why don't I see the kind of connection I want to make?

The RStudio IDE will automatically detect specific database connections that are set up on your system. There are four ways to set up connections that will automatically populate in the new connection dialog. For more information, see Using RStudio Connections. The four methods are:

  1. odbc.ini: ODBC DSN's installed on your system. If the odbc package is installed, any system DSN (Data Source Name) you have installed will be listed.

  2. odbcinst.ini: ODBC drivers installed on your system. If the odbc package is installed, any ODBC driver you have installed will be listed. The method for installing ODBC drivers varies by system. See our page on Setting up ODBC Drivers.

  3. R snippets: Connections supplied by your administrator. An administrator may put connection snippets into a folder to make them available to end users. If you're an administrator, see our page on RStudio Connection Snippet Files for more information.

  4. R packages: Connection types defined by installed R packages. Any R package can supply a connection. Currently, the sparklyr package and odbc package define connection types. Install one of these packages using e.g., install.packages("odbc").

When I try to connect, why do I always get a message saying I need to install a package?

The latest versions of the R packages that provide database connectivity are required.

If you are using a CRAN repository such as MRAN that does not include the latest package versions, you may not be able to install these from your default CRAN repository. You can either temporarily add our CRAN repository, or install the development version of the package from e.g., Github (see the package's help page for more information).

I made a connection in R code, but why isn't it showing up in the Connections pane?

The package which makes the connection needs to tell RStudio that the connection has been created. Not all R packages that connect to data do this. If you're the package author, you can implement the RStudio Connections Contract to integrate with the Connections pane.

How do I set up an ODBC connection?

If you want to connect R to a database, you will need to install and configure an ODBC driver on the system where you are running R. You can get ODBC drivers from third parties, the database vendor themselves, or from RStudio if you have purchased our professional products. If you work on a platform other than Windows, you may need to install the unixODBC driver manager if it does not already exist (contact your system administrator for assistance). You may also need to configure your driver location in the odbcinst.ini file and data source names (DSN) in odbc.ini. See Setting Up ODBC Drivers for more information. Also refer to your database vendor documentation for specific database material.

What is a DSN?

A data source name (DSN) is a configuration stored in odbc.ini that contains information about a database connection. The DSN has a name like "SQL Server Marketing DB", and points to a single data source and a specific driver listed in the odbcinst.ini file. A DSN is one of the four methods RStudio uses to discover existing connections. For more information, please read Setting Up ODBC Drivers

What are the odbc.ini and odbcinst.ini files?

The system file odbcinst.ini contains information about ODBC drivers available to all users, and the odbc.ini file contains information about DSN's available to all users. You can locate these files with odbcinst -j.

These files can exist at the system (i.e. server) level or at the user level. The system files are odbcinst.ini and odbc.ini (note no leading dot), and the user file is ~/.odbc.ini in each user's home directory (note leading dot). For more details, please see everything you wanted to know about odbcinst but were afraid to ask.

Should I use unixODBC or iodbc?

RStudio recommends using unixODBC, and the odbc R package depends on it. For more information, see the unixODBC User Manual.

Should I use odbc or RODBC?

The RODBC package is not compatible with DBI; therefore, we recommend switching to the newer odbc package. The DBI package is a common interface between R and database management systems. It has a long history with R, and has recently been enhanced with help from the R Consortium. It is important that modern database connectors for R be DBI-compliant. Compared to the existing RODBC package, odbc is also faster (~3x for reading, ~2x for writing), translates date/time data types, and is under active development.

How can I use JDBC with RStudio?

JDBC drivers are incompatible with the odbc R package maintained by RStudio. If you want to use JDBC drivers along with Java, you will have to install these open-source R packages: rJava, RJDBC, and DBI. The R console will recognize JDBC connections, but they will not be displayed in the the Connections pane unless you or a third party has created a connections contract. RStudio does not provide professional support for JDBC connections.

How do I enable driver manager tracing?

Typically, the best way to identify connection issues is to enable driver logging with your specific drivers (see your driver documentation for instructions). However, you can also enable driver manager tracing as another way to troubleshoot connection issues. For example, in non-Windows platforms, you can enable tracing by modifying odbcinst.ini:

[ODBC]
Trace=yes
TraceFile=/path/to/file/name.log

How can I connect to an Oracle database?

In addition to installing a driver, Oracle also requires you to download and install the Oracle Instant Client on the system where you are running R. If you use Oracle in your enterprise, you may already have this software installed. If not, you will have to create an Oracle account and accept the licensing terms before downloading the Oracle Instant Client software.

RStudio Server FAQ

How do I configure ODBC drivers with Linux?

If you are having problems connecting to your databases from RStudio, it’s likely that your system is not configured properly. We recommend these steps before contacting support.

  • Test your connection from the command line with the isql tool, which is part of unixODBC. You will have to set up a data source name (DSN) first.
  • Check your configuration. Driver paths are stored in odbcinst.ini. DSN's are stored in odbc.ini. You can locate these files with the odbcinst -j command.
  • Examine specific driver dependencies with the ldd tool.
  • Enable driver logging to identify problems when a new connection is created.

How can I test a database connection outside of R?

You should test your database connections from the command line before creating new connections in RStudio. You can test connections by running isql, a command line tool for interactive SQL. Note that isql requires you to create a data source name first. 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. To test this connection from a terminal enter:

$ isql test

Where are my ODBC configuration files?

Configuration files are typically located under /etc/. You can find the location of odbcinst.ini and odbc.ini files by running the odbcinst -j command:

$ odbcinst -j
unixODBC 2.3.1
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

How do I find dependencies for my ODBC drivers?

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)

How do I enable driver logging with RStudio Professional Drivers?

You can enable driver logging to track activity and troubleshoot issues. Open your driver configuration file (e.g. rstudio.sqlserverodbc.ini) in a text editor and set values for LogLevel (e.g. 6) and LogPath (e.g. /tmp). Make sure that users have permissions to write files to LogPath. Set UseLogPrefix=1 to create a unique log for every user and process with a connection. Important: 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

Where can I find driver specific guides?

The installation and configuration guide for your specific driver can be found in the bundle that you downloaded from RStudio Professional Drivers. Look for the PDF file under /etc/rstudio-drivers/<driver>/docs/.   

How do I install RStudio Professional Drivers?

RStudio Professional Drivers are ODBC data connectors that help you connect to some of the most popular databases. If you are using RStudio Server Pro, RStudio Connect, or Shiny Server Pro, then you can download and use RStudio Professional Drivers at no additional charge. For more information please see RStudio Professional Drivers or contact our sales team.

What are the system requirements for RStudio Professional Drivers?

To establish a connection to your database from R, you will need root access and the following components stored on your system:

  • At least one RStudio professional product
  • The unixODBC driver manager
  • The odbc R package
  • RStudio Professional Drivers 

You will also need the proper permissions and credentials to query your database. For detailed requirements, see System Requirements for RStudio Professional Drivers.

When will you add my data source to RStudio Professional Drivers?

We initially released RStudio Professional Drivers for some of today's most popular data sources. We are committed to increasing the number of data connectors we support in the future. If we don't support your database, we welcome your feedback. Please contact our sales team or email us at info@rstudio.com to let us know what you would like us to add.

How can I connect to an Oracle database?

Oracle also requires Oracle Instant Client on the system where you are running R. If you are working with RStudio Server, you should point your LD_LIBRARY_PATH at the installation path for the Oracle Instant Client. An easy way to do this is to add a line to rserver.conf like this:

rsession-ld-library-path=/usr/lib/oracle/12.1/client64/lib

Note: Whenever you modify rserver.conf, make sure to restart RStudio Server.

 

Comments