0

I've been trying to initiate a read only connection to MSSQL server using R but am having trouble.

I am able to connect without read only using the DBI and odbc packages:

library(DBI)
library(odbc)

con <- DBI::dbConnect(
  odbc::odbc(),
  Driver   = "DRIVER",
  Server   = "SERVER",
  Database = "DATABASE",
  UID      = "USERNAME",
  PWD      = "PASSWORD",
  Port     = PORT
)

(OR using connection string)

con <- DBI::dbConnect(odbc::odbc(),
.connection_string = "Driver={DRIVER};Uid=USERNAME;Pwd=PASSWORD;Server=SERVER;Port=PORT;Database=DATABASE;")

I assumed that if I added ApplicationIntent="ReadOnly" or ApplicationIntent=ReadOnly; to the respective codes, this would work but it results in a timeout.

I'm not really sure if this is even possible using ODBC/DBI, any help would be deeply appreciated!

quicklegit
  • 137
  • 1
  • 6
  • Isn't that set at the database end? – Esben Eickhardt Apr 03 '19 at 11:26
  • 1
    What about using the `RODBC` package? I was able to connect to a SQL Server database using `RODBC` with the command `con <- odbcDriverConnect('Driver=SQL Server;Server=SERVERNAME,1433;Database=DATABSENAME;Trusted_Connection=yes;ApplicationIntent=ReadOnly')` – Kerry Jackson Apr 03 '19 at 11:28
  • @KerryJackson is this for MSSQL? I just tried that and got a network error :/. When I remove the 'ApplicationIntent=ReadOnly' part though, it works! – quicklegit Apr 03 '19 at 13:18
  • Yes, it is for MS SQL Server, and it worked without error for me. Perhaps the error you are getting is not because of the package `RODBC` or `DBI`, but because your server does not allow this? There might be some useful links at https://stackoverflow.com/questions/15347541/what-does-applicationintent-readonly-mean-in-the-connection-string – Kerry Jackson Apr 03 '19 at 13:22

1 Answers1

0

You can also update the rights of the user whcih you are using for connection to view rights only. Solves the problem other way around. Otherwise you could maybe install and use: library(RSQLServer) Find it a better library for SQL connection myself.

Floris
  • 111
  • 1
  • 3