How to Connect to SQL Database Server using RStudio in Linux Enclave VDI

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

  1. 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)

  1. 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.

  1. 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"

  1. 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
Go to KB0014882 in the IS Service Desk