[Master Guide] How to Connect to a Data Source from RStudio in Windows Enclave VDI

Introduction

This document details how to create a database connection to different relational data sources from R, in the Windows Enclave Virtual Desktop using ODBC (i.e. the Complete PDSR Curated Data Set, COVID-19 Mart, etc.)

The Microsoft ODBC Drivers for SQL Server are stand-alone ODBC drivers which provide an application programming interface (API) implementing the standard ODBC interfaces to Microsoft SQL Server.

Requirements

1. Access to Enclave Platform should already be granted and your workspace provisioned. If you do not have the approval to access the Enclave Platform, complete and submit the Access Request Form.
2. You should be logged on to your Enclave Windows Virtual Desktop. You cannot access the Enclave Platform resources from outside the platform.

Step 1: Create a DSN

The simplest way to make a connection from R is using a Data Source Name (DSN). You will need to create a DSN for the data source you are trying to connect to (i.e. Complete PDSR Curated Data Set, COVID19 Mart, etc.)

Such connections, are created using the user's MGB credentials, and are normally used throughout an active session, as shown in the example below. Please follow the step-by-step instructions in the document, How to Create a Connection to a Data Source in Windows Enclave VDI, to create a DSN for your application.

Step 2: Creating a Database Connection from R Programs

Please Note: The example will not run if you don't install odbc packages. Here is the installation of dependent packages.

install.packages("odbc")

Now, paste and run the R code below.

library(odbc) con <- dbConnect(odbc::odbc(), DSN="DSN_Name") dbGetQuery(con, "select getdate()")

Limitations

Note: The above code was tested to run successfully under R 4.0.4

Related References

Connect to an ODBC Data Source (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Docs

Setting up R to connect to SQL Server | RStudio Support