How to Connect to SQL Server Using RStudio | Windows Enclave VDI

This document offers tips for connecting to SQL server from the Windows Enclave Virtual Desktop using RStudio, Version 4.0.4. Note that this solution has been validated using the Windows 10 Enclave Virtual Desktop connection to a MS Windows SQL Server with either ODBC, RODBC or JDBC.

Note: This solution has been validated using the Windows 10 Enclave Virtual Desktop connection to a MS Windows SQL Server with either ODBC, RODBC or JDBC.

Requirements

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

Step-by-Step Instructions

Depending on how you want to run R scripts to connect to the SQL server, there are three drivers you can use. There are ODBC, RODBC or JDBC drivers.

Step 1. Install ODBC, RODBC or JDBC Drivers

Installing ODBC, RODBC or JDBC drivers should be as easy as this:

ODBC Driver | Download ODBC Driver

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.

The Microsoft ODBC Driver for SQL Server can be used to create new applications. You can also upgrade your older applications which currently use an older ODBC driver. The ODBC Driver for SQL Server supports connections to Azure SQL Database, Azure Synapse Analytics, and SQL Server.

Summary

RODBC Driver

• RODBC is simple to install, and binary distributions are available for Windows from CRAN. https://cran.r-project.org/web/packages/RODBC/index.html
• To install R packages in RStudio, simply run the following R script:

• install.packages("RODBC")

JDBC Driver

• JDBC creates a new DBI driver that can be used to start JDBC connections.
• JDBC function has two purposes:
  
  • One is to initialize the Java VM and load a Java JDBC driver (not to be confused with the JDBCDriver R object which is actually a DBI driver).
  • The second purpose is to create a proxy R object which can be used to a call dbConnect which actually creates a connection.
• JDBC requires a JDBC driver for a database-backend to be loaded. Usually, a JDBC driver is supplied in a Java Archive (jar) file. The path to such a file can be specified in classPath.
• The driver itself has a Java class name that is used to load the driver (for example the MySQL driver uses com.mysql.jdbc.Driver), this has to be specified in driverClass.

• JDBC (driverClass = "", classPath = "", identifier.quote = NA) 

• The RJDBC package is an implementation of R's DBI interface using JDBC as a backend. This allows R to connect to any DBMS that has a JDBC driver.

• install.packages("DBI", dep=TRUE) install.packages("rJava", dep=TRUE) install.packages("RJDBC", dep=TRUE)

Step 2 – Specifying DSNs

The ODBC driver managers have ‘User DSNs’ and ‘System DSNs’: these differ only in where the information is stored, the first on a per-user basis and the second for all users of the system.

Windows has a GUI you can use to set up the DSNs. For example, to set up the ODBC Data Source:

1. Go to the Start menu and type “ODBC”
2. When “Microsoft ODBC Administrator” or “Data Sources (ODBC)” appears under Programs, click on it.

Alternatively, go to the Start menu and select "Administrative Tools". In this step, you can add, remove and edit (‘configure’) DSNs as illustrated in the screenshots below.

You need to repeat these steps for each database you are planning to use.

3. In the ODBC Data Source Administrator window, select the User DSN tab
4. Click the Add button
5. Select ODBC Driver 17 for SQL Server from the list of drivers
6. Click Finish
7. Type the name you want to use for your data source, add a description of the data source, and the name of the Server you want to connect to (enclavemssql01 in this example).
8. Click Next to continue
9. Verify that the option “With Integrated Windows authentication” is selected
10. Click Next to continue
11. Check “Change the default database" box
12. Choose the name of the database to which you want to connect from the drop-down (COVID19_Mart in this example).
13. Click Next
14. Verify general configuration settings for the ODBC connection and click Finish.
15. Once the summary of configuration for the new data source is created, test the data source by clicking the Test Data Source button.
16. If everything works as expected, the confirmation screen will appear as indicated below. Click the OK button to close the test screen window.
17. Click the OK button again to close the creation window.
18. The ODBC data source has been successfully created. Click the OK button.

Step 3. Making a MS SQL Server Connection

ODBC works by setting up a connection or channel from the client (here RODBC) to the DBMSs as specified in the DSN.

Such connections are normally used throughout a session, but should be closed explicitly at the end of the session—however RODBC will clear up after you if you forget (with a warning that might not be seen in a GUI environment). There can be many simultaneous connections. 

The simplest way to make a connection is:

library(RODBC) cn <- odbcConnect("some dsn")

and when you are done with it,

close(cn)

or if you prefer

odbcClose(cn)

Here are three sample codes. These codes provides a template for connecting to an existing database. In this example the database is i2b2.

The first example is a very simple R program: “odbc connect to SQL Server using DSN Covid 19.R”

library(RODBC) cn <- odbcConnect("COVID 19") dataSQLQuery <- sqlQuery(cn, "SELECT * FROM i2b2.dbo.ACT_COVID") View(dataSQLQuery)

It uses the DSN created by following Step 2 above or the PDF files that is attached.

The second R program: “RODBC Connect to SQL Server i2b2 database from R”

libPaths() local({r <- list("cran" = "http://repo.analyticsenclave.org:8082/artifactory/cran")}) getOption("repos") install.packages('RODBC') library('RODBC') cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=mssql01-t.analyticsenclave. org;database=i2b2;trusted_connection=yes;") dataSQLQuery <- sqlQuery(cn, "SELECT * FROM i2b2.dbo.ACT_COVID") View(dataSQLQuery)

It uses odbcDriver Connection function with the connection strings.

The third R program: “RJDBC_enclave - RJDBC to querry i2b2 COVID table.R”

install.packages("DBI",dep=TRUE) install.packages("rJava",dep=TRUE) install.packages("RJDBC",dep=TRUE) library('DBI') library('rJava') library('RJDBC') drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "c:/Program Files/sqljdbc_8.2/enu/mssql-jdbc-8.2.2. jre8.jar", identifier.quote = "'") cn <- dbConnect(drv, "jdbc:sqlserver://mssql01.analyticsenclave.org:1433; databaseName=COVID19_Mart; domain=analyticsenclave.org;IntegratedSecurity=true") dataSQLQuery <- dbGetQuery(cn, "SELECT * FROM i2b2.dbo.ACT_COVID") View(dataSQLQuery)

Limitations

Please note that 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

Working with a JDBC connection - JDBC Driver for SQL Server | Microsoft Docs

Additional Resources

How to connect to Oracle using ODBC

1. Start the database connection wizard
2. Select Oracle (ODBC / JDBC), and then click Next.
3. Select ODBC
4. Click Edit Drivers
5. Select the Oracle drivers you wish to use (in this example, Oracle in OraClient11g_home1)
6. Click Back
7. Select Create a new data source name (DSN) with the driver, and then select the Oracle driver chosen in step 3.
8. Click Connect.

Related documents

Connect R (RStudio) with RJDBC to Microsoft SQL Server Express 2008 R2  | Incentergy