Viewing and adding ODBC drivers

To view the list of available drivers, open the “ODBC Data Sources” dialog:

• Type “ODBC” in the Start menu or screen.
• Select the “ODBC Data Sources” that corresponds to the R console you’ve started: on a 64bit OS there’s both 32bit or 64bit. If you can’t find it, it’s here:
  • 32bit for 64bit OS: %windir%syswow64odbcad32.exe
  • 32bit for 32bit OS, 64bit for 64bit OS: %windir%system32odbcad32.exe
• Click on the “Drivers” tab:

“SQL Server” was present by default with the versions of Windows I tested (8.1 and Server 2012) and was sufficient. If you want to try the other drivers, these are:

• SQL Server Native Client 11.0: search for “ODBC” on the page.
• ODBC Driver 11 for SQL Server.

Using RODBC’s odbcDriverConnect function

This is the simplest option as ODBC is available by default on Windows.

If the ODBC connection string suggested by the Azure Management site doesn’t work (usually: missing driver) try using one similar to the simplified version below.

library(RODBC)
myServer <- "MyServer.database.windows.net"
myUser <- "MyUser"
myPassword <- "MyPassword"
myDatabase <- "MyDatabase"
myDriver <- "SQL Server" # Must correspond to an entry in the Drivers tab of "ODBC Data Sources"

connectionString <- paste0(
    "Driver=", myDriver, 
    ";Server=", myServer, 
    ";Database=", myDatabase, 
    ";Uid=", myUser, 
    ";Pwd=", myPassword)
# This query simulates a table by generating a rowset with one integer column going from 1 to 1000
sqlQuery <- "
WITH nb AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM nb where n < 9)
SELECT n1.n+10*n2.n+100*n3.n+1 FROM nb n1 CROSS JOIN nb n2 CROSS JOIN nb n3
"
conn <- odbcDriverConnect(connectionString)
df <- sqlQuery(conn, sqlQuery)
close(conn) # don't leak connections !

str(df)
## 'data.frame':    1000 obs. of  1 variable:
##  $ : int  1 2 3 4 5 6 7 8 9 10 ...
summary(df)
##                  
##  Min.   :   1.0  
##  1st Qu.: 250.8  
##  Median : 500.5  
##  Mean   : 500.5  
##  3rd Qu.: 750.2  
##  Max.   :1000.0

Using RODBC’s odbcConnect function

This function allows for using a Data Source Name (DSN). The pros of the DSN is that you can hide some of the complexity from the R script, for example the choice of driver. To create a DSN, open the “ODBC Data Sources” dialog and then click on “Add” under the “User DSN” or “System DSN” tab.

Notes:

• If connecting to Azure, choose “SQL Server authentication”.
• The credentials (Login ID and Password) are not persisted, meaning even if you specify them in the dialog, you’ll have to specify them again in R.
• If you can’t specify a database besides master for the “SQL Server” driver, then don’t specify credentials (you won’t be able to test the connection anymore).

In the example below, I’ve created a “User DSN” (that is: only available to current user) called MySqlAzure:

library(RODBC)

conn <- odbcConnect("MySqlAzure", uid = myUser, pwd = myPassword)
sqlQuery(conn, "SELECT POWER(2, 10) AS Answer")
##   Answer
## 1   1024
close(conn)

Using RJDBC

To use RJDBC to connect to SQL Server, you need to:

• Install:
  • JDK
  • JDBC drivers for SQL Server
• Know the following:
  • driverClass: the class name. See the driver-specific documentation.
  • classPath: the location of the jar file. If you don’t know where it is, try running dir /s /b %systemdrive%*sql*.jar from a command prompt.
  • url connector prefix. Again, driver-specific.

In my specific setup:

library(RJDBC)
drv <- JDBC(
    driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    classPath = "C:/JDBC/Microsoft JDBC Driver 4.1 for SQL Server/sqljdbc_4.1/enu/sqljdbc41.jar")
conn <- dbConnect(drv, "jdbc:sqlserver://MyServer.database.windows.net", "MyUser", "MyPassword")
dbGetQuery(conn, "SELECT 1+1")
dbDisconnect(conn)

Using RSQLServer

RSqlServer also needs the JDK installed. It’s slighter easier to use than RJDBC:

library(RSQLServer)
conn <- dbConnect(
    RSQLServer::SQLServer(),
    "MyServer.database.windows.net",
    "useNTLMv2=false;user=MyUser;Password=MyPassword")
dbGetQuery(conn, "SELECT 1+1")
dbDisconnect(conn)