Connect to Azure Databases from R
R has great options for connecting to SQL databases through the DBI for R project.
RStudio also has great documentation on Databases using R, in particular the section “Best Practices”.
The major lines of this post is based on the RStudio documentation, but I’ve added some details that I spent time resolving.
When connecting to SQL databases in Azure I use the DBI package and the odbc package.
As explained in the “Best Practices” above, it is not desirable to enter database information and credentials directly in a connection string. That is, don’t do like this:
The security credentials are entered in your history for the world to see and it is quite verbose.
Instead, rely on a Data Source Name (DSN).
con <- DBI::dbConnect(odbc::odbc(),
server = "<server name>.database.windows.net",
database = "<database name>",
username = "<user>",
password = "<password>",
port = 1433
On Windows I use the “ODBC Data Source Administrator”:
A DSN with name “MyConnection” is then used in R as
At least when using Active Directory for authentication, there is no need for further authentication details in R.
con <- DBI::dbConnect(odbc::odbc(), dsn = "MyConnection")
You can test the connection with
On Linux (which is relevant when using Shiny Server) I rely on the Free TDS drivers.
On Ubuntu these are installed by running the following commands:
I have tried to make other drivers work, but with no luck.
apt-get install unixodbc unixodbc-dev --install-suggests
apt-get install tdsodbc
The ODBC drivers are specified in the file
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
Trace = no
Setup files may be in different locations on other systems, but at least the name of the shared libraries should be the same.
A connection is specified in the file
Driver = FreeTDS
Server = <server name>.database.windows.net
Database = <database name>
Port = 1433
Encrypt = yes
TDS_Version = 7.0
Timeout = 60
I can only make a connection work when
7.0 – using
x different from 0 has not worked for me.
In Azure one can make Azure Active Directory the only allowed form of authentication. So if you are having problems accessing a database, check if access with username & password is allowed.
You can also enter
odbc.ini, but there is no point: They are not read by R. That is, you need a connection string like:
Again, this is a bad idea.
One option that at least makes this line of code safe and usable for others is to store username/password in a (local)
con <- DBI::dbConnect(odbc::odbc(), dsn = "MyConnection", username = "<username>", password = "<password>")
This info can be retrieved with
db_username = "<username>"
db_password = "<password>"
(Check the references for
Renviron in a previous post.)
Differences between Linux and Windows
One thing that has caused me problems is that a SQL script passed to a SQL database in Azure through R is not executed in the same manner on Windows and Linux (with the setup I have described).
As a simple example, I often use SQL Server Management Studio (SSMS) on Windows to write and test SQL scripts (when the dbplyr package doesn’t suffice).
In SSMS the tables in a database are listed as
In R, the command
DBI::dbListTables(con) returns the tables without the
When executing a SQL script through
<prefix> it works on Windows, but not on Linux.