0

The aim is to read data from a Microsoft SQL server in RStudio. I am on Windows. So I have:

  1. Installed RODBC package;
  2. Created ODBC DSN pointing to the SQL server I need to read data from.

I can successfully connect to the server and read data through the standart R on Windows (RGUI). I can define connection both with odbcConnect() or odbcDriverConnect() without any errors. For example:

> con <- odbcConnect("SQLB")
> as.list(odbcGetInfo(con))
$DBMS_Name
[1] "Microsoft SQL Server"

$DBMS_Ver
[1] "09.00.5000"

$Driver_ODBC_Ver
[1] "03.52"

$Data_Source_Name
[1] "SQLB"

$Driver_Name
[1] "SQLSRV32.DLL"

$Driver_Ver
[1] "06.03.9600"

$ODBC_Ver
[1] "03.80.0000"

$Server_Name
[1] "SQLB\\SQLB"

If I do the same in RStudio it always fails with error messages:

> con <- odbcConnect("SQLB")
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=SQLB") :
  [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In RODBC::odbcDriverConnect("DSN=SQLB") :
  [RODBC] ERROR: state 01000, code 67, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In RODBC::odbcDriverConnect("DSN=SQLB") : ODBC connection failed

It takes on average 16.5 seconds to return the error message.

> require(microbenchmark)
> microbenchmark(odbcConnect("SQLB"), times = 10L)
Unit: seconds
                expr      min       lq     mean  median       uq      max neval
 odbcConnect("SQLB") 16.52802 16.52953 16.53059 16.5298 16.53141 16.53639    10
There were 30 warnings (use warnings() to see them)

I have tried other drivers but the situation is the same - they all work for R but they fail for RStudio. I can not understand why it works in R but it does not work in RStudio. Please ask if more details should be provided.

0

This solution was valid for one month. It started to fail both for R and RStudio. Probably there were some security policy changes I was not aware of.


The solution is pretty unusual. Previously my RStudio installation was in

C:\Softs\RStudio\bin

I made a new installation of RStudio in

C:\Users\MLiberts\Documents\RStudio-1.0.44\bin

and the problem is gone. Keep in mind that I am still using the old settings of the RStudio as they are stored in

C:\Users\MLiberts\AppData\Local\RStudio-Desktop

and I have not changed them.

So it seems that Windows 8 is implying different permissions on executable files depending the location they are stored in. I have full permissions on C:\Users\MLiberts\Documents but I do not have full permissions on C:\Softs however I can read and write there.

C:\Softs\RStudio>icacls bin
bin BUILTIN\Administrators:(I)(OI)(CI)(F)
    NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F)
    BUILTIN\Users:(I)(OI)(CI)(RX)
    NT AUTHORITY\Authenticated Users:(I)(M)
    NT AUTHORITY\Authenticated Users:(I)(OI)(CI)(IO)(M)

C:\Users\MLiberts\Documents\RStudio-1.0.44>icacls bin
bin NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F)
    BUILTIN\Administrators:(I)(OI)(CI)(F)
    CSPDOM\mliberts:(I)(OI)(CI)(F)
|improve this answer|||||
0

The solution is to install R and RStudio as administrator in C:\Program Files\.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.