Connect from R Studio to SQL server

Recently, I have had to assist one of our newly added Data Science team to connect R with SQL server. This is the first time I had to deal with R server.

One of the things I found out was that R studio did not use LDAP for authentication. After much effort, we settled with granting users to access using native SQL authentication.

To install and load the RODBC package, do the following:

  • Open the RStudio console (make sure the R version is at least 3.1.3: If it isn’t, then use the updateR() function)
  • Run the following command: install.packages(“RODBC”)
  • Run the following command: library(RODBC)

You will need to load the following libraries:

library(rstudioapi)
libary(RODBC)

After this, run the following command to connect to the SQL server

con <- DBI::dbConnect(odbc::odbc(),
Driver = “SQLServer”,
Database = “enter_db_name“,
UID = “enter_user_name“,
PWD = rstudioapi::askforPassword(),
Server = “enter_IP_Address“,
Port = 1433)

The rstudioapi::askForPassword function will prompt the user for a password and reduce the need to store passwords in code.

Thats it! After doing this, users were able to connect to SQL server from R studio and run queries.

Credit: https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s