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-
Database Blog 