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
- Examine specific driver dependencies with the ldd tool.
- Enable driver logging to identify problems when a new connection is created.
1. 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:
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 -v test
2. Where are my ODBC configuration files?
Configuration files are typically located under
/etc/. You can find the location of
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
3. 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)
4. 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
/tmp). Make sure that users have permissions to write files to
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
The Connections pane doesn't show installed drivers
If you're using RStudio Server Pro, 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 ODBC Installer and the drivers should properly show up in the New Connection window within RStudio Server Pro.