July 17, 2023
In this user facing document, we have a tested solution for connecting to SQL server from R in the Linux VDI. This solution is to connect from a Linux machine to a MS Windows SQL Server with odbc.
A connection to SQL server using odbc R package would look something like this:
con <- dbConnect(odbc::odbc(), "DSN", Database="DB_Name")
dbGetQuery(con, "SELECT xyz FROM table")
The solution here is provided as a minimum set of the instructions for the user to make the connection. You can find more details on using an ODBC driver at the RStudio website.
- Install R library – odbc and other modules
Open RStudio to install the R packages:
## Install odbc packages
- Configuring the User DSN in ~/.odbc.ini
From a Terminal or text editor, create/edit the file ~/.odbc.ini:
Driver = FreeTDS
Server = mssql01.analyticsenclave.org
Port = 1433
TDS_Version = 8.0
use ntlmv2 = yes
The first line between brackets name will be your Data Source Name (DSN). You can use any name/label that is representative to your data source (i.e. as i2b2 in our example). The Server and Port must be replaced by the actual IP address/hostname and port of the server you want to connect.
i Please keep in mind that connections to any server outside of the Enclave network is restricted.
- Setup Environment Variables
From a Terminal or text editor, set up the environment variables MGB_USERNAME and MGB_PASSWORD at the end of your ~/.Renviron file.
# Set MGB username and password
- Programming Examples using odbc
From Rstudio , you can use dbConnect to connect from a Linux machine to a MS Windows SQL Server with odbc.
Note: Replace "i2b2" with your actual DSN if you choose a different name. You can use a different option to pass your credentials, but hardcoding your username and password in your code must be avoided at all costs.
##install odbc packages extracts
# Get the MGB_USERNAME and MGB_PASSWORD
MGB_USERNAME <- Sys.getenv("MGB_USERNAME")
MGB_PASSWORD <- Sys.getenv("MGB_PASSWORD")
# Set up the database connection
connection <- dbConnect(odbc::odbc(),dsn="i2b2",uid=MGB_USERNAME,pwd=MGB_PASSWORD)
# Perform data query
DBI::dbGetQuery(connection, "SELECT count(*) FROM i2b2.dbo.ACT_COVID")
|Enclave Azure Platform – mgb|