Failure to Connect to Odbc Database in R

New to ODBC in R (RStudio), and getting a failed to connect error?

In Windows (unsure of other OSes) you need to go into the ODBC Data Source Administrator, and add the data source. The ODBC Data Source Administrator is accessed via the 'Administrative Tools' section of Control Panel (in Windows 10 at least).

The connection command is then simply

conn <- odbcConnect("QV Training")

ODBC Connection using R

Following is the one needed to solve the issue.

dbhandle_dev = odbcDriverConnect('driver={ODBC Driver 17 for SQL Server};server="DevSQL01";database="risk";trusted_connection=Yes') 

How to access SQL Server database hosted on Azure Windows VM using R from Rstudio Connect hosted on AWS ubuntu server?

At last this worked for me:

library(odbc)
library(DBI)

UserId <- 'mycompany\rkahn'
UserPassword <- 'myPassword'
dbname <- 'sqldb'

sqlsrvcon <- dbConnect(odbc(),
                 Driver = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1",
                 Server = "azure_wvm_name.corp.mycompany.com",
                 Database = dbname,
                 UID = UserId,
                 PWD = UserPassword,
                 Encrypt="yes",
                 TrustServerCertificate="yes",
                 Port = 1433)

---

Related Topics