June 17, 2024
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:
library(DBI)
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.
Instructions
- Install R library – odbc and other modules
Open RStudio to install the R packages:
getOption("repos")
## Install odbc packages
install.packages('odbc')
library(odbc)
library(DBI)
- Configuring the User DSN in ~/.odbc.ini
From a Terminal or text editor, create/edit the file ~/.odbc.ini:
[i2b2]
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
MGB_USERNAME="Partners\myusername"
MGB_PASSWORD="mypassword"
- 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.
getOption("repos")
##install odbc packages extracts
install.packages('odbc')
library(odbc)
library(DBI)
# 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")
Related Articles
HOWTO: Connect to MS SQL Database Server from Data Enclave Linux VDI using DBeaver
HOWTO: Connect to MS SQL Database Server from Data Enclave Linux VDI using Python
| Enclave Azure Platform – mgb |