CODEWITHSUNDEEP
rrodbc
Anubhav Dikshit
Anubhav Dikshit

Reputation: 1829

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

Upvotes: 2

Views: 8662

Answers (3)

vaudt
vaudt

Reputation: 181

This is my first post, so please be forgiving. I have got this set up working for a windows environment and an Azure SQL server that use Active Directory.

Plan of attack for this problem:

  • First try to connect to the Azure database using the SQL server management Studio.

  • Secondly, try to connect to the Azure database using the odbcad32. If that works you can create a User DSN, which for the example's sake I will call example.

  • Finally use odbcDriverConnect("DSN=example;") in R

First of all try to login to the Azure database using Sql Server Management Studio. This involves two substeps.

  1. On the target database your user domain and name may be different from the user domain and name on the machine from which you are trying to connect to the target database. Should that be the case then create a credential using the credential manager in windows.

Creating a windows credential to match your identity with that used by the Azure database

  1. Verify the method of authentication from the dialog you get when connecting with SQL server management studio. On my machine I thus saw that the Azure SQL server uses a thing called 'Active Directory Integration' .

Select the method of authentication that applies

Secondly instead of directly testing using RODBC calls it is more convenient to use the ODBC administrator. Run 'odbcad32'. You have to choose the driver.

If you want to use Active Directory for Authentication you need the 'ODBC Driver 13 for SQL Server'. The other drivers, such as 'SQL Server' and the 'Native Client' do not support Active Directory.

Create a user DSN and test it. The end result should look like this.

If you do not get the desired success you may instead get the following error messages.

If this occurs simply go ahead and install the required software

Go ahead and install the Sign in assistant.

Another error that may pop up is the error saying that you have not installed the Active Directory Authentication Library for SQL Server. Surely this is not true if you have the set up working via SQL server management studio. The problem is that odbcad32 cannot find adalsql.dll. The solution is to add a few registry entries.

Which registry entries to add

Addendum: Below some people note they got it working with the 11.0 driver instead of the 13.0 one. They are perfectly right, but that is because they use user / password authentication. If your server uses active directory integration you do really need the 13.0 or higher versions of the ODBC driver.

Upvotes: 3

Andy Borst
Andy Borst

Reputation: 338

I installed the "odbc" library and setup the connection as described on the RStudio pages. The 11.0 driver worked for me. 

library(odbc)
con <- dbConnect(odbc(),
             Driver = "SQL Server Native Client 11.0",
             Server = "servername",
             Database = "databasename",
             UID = "username",
             #PWD = rstudioapi::askForPassword("Database password"),
             PWD = "pwd",
             Port = 1433)

Upvotes: 1

Anubhav Dikshit
Anubhav Dikshit

Reputation: 1829

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"

3)

 #############
 #     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)

Upvotes: 2

Related Questions