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