Using RODBC in R to connect to Azure SQL

I am using R 3.2.4 and El captain, I wanted to connect to Azure SQL using the RODBC package in R, I did the following to install it

brew install unixodbc
install.packages("RODBC",type="source")

And also

brew install freetds --with-unixodbc

The output of above command:

Warning: unixodbc-2.3.4 already installed
Warning: freetds-0.95.80 already installed

But whenever I try to connect using the following:

library("RODBC")

con = odbcDriverConnect(
  'driver = {SQL Server};
  Server = xxxxxx;
  Database = xxxxx;
  User Id= xxxxx;
  Password= xxxxx;')

I get the following Error:

[RODBC] ERROR: state IM007, code 59478176, message [iODBC][Driver Manager]No data source or driver specified, dialog prohibited

Solution

So after a lot of trail and error, here is what I have:

1) Ditch RODBC and use RSQLServer

install.packages("RSQLServer")

Create a config file (use sublime) called 'sql.yaml', with the following contents:

SQL_PROD:
    server: 11.1.111.11
    type: &type sqlserver
    port: &port 1433
    domain: &domain companyname
    user: &user winusername
    password: &pass winpassword
    useNTLMv2: &ntlm true
SQL_DEV:
    server: 11.1.111.15
    type: *type
    port: *port
    domain: *domain
    user: *user
    password: *pass
    useNTLMv2: *ntlm
AW:
   server: <yourservername>
   type: sqlserver
   user: <username>
   password: <password>
   port: 1433

2) Save this YAML file in the following location(run the following in R: Sys.getenv("HOME")) e.g.: if username is dave its "/Users/dave"

 #############
 #     DBI   #
 #############

# Note we do not attach the RSQLServer package.
library(DBI)
# Connect to AW server in ~/sql.yaml
aw <- dbConnect(RSQLServer::SQLServer(), "AW", database = 'db')

# RSQLServer only returns tables with type TABLE and VIEW.
dbListTables(aw)