R with SQL server 2016 error "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Question

I am trying to connect R with SQL server 2016, when I tried RevoScaleR_SqlServer_GettingStarted.R script or my own script I got the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ODBC Error in SQLDisconnect Could not open data source. Error in doTryCatch(return(expr), name, parentenv, handler) : Could not open data source.

Note: I can execute R script in SQL management studio. I already configured Microsoft ODBC Driver for SQL Server Version 13.00.1300 in ODBC Data Sources (64-bit).

Answer 1

probably you should create a txt file in your r wd and save your connection string in it after that you should use readLines("your connection string.txt") to read your connection string and use it in your code worked for me..... FYI you should disable blocking R from you windows firewall with advanced security....

Answer 2

Ran into the same message... it looks like it is just a generic message from R that there is something wrong with your connection string.

At least this was my issue... there was a space in the sample code where it wanted your server instance name. Deleting the space fixed it for me.

# https://microsoft.github.io/sql-ml-tutorials/R/customerclustering/step/2.html
#Connection string to connect to SQL Server. Don't forget to replace MyServer with the name of your SQL Server instance

connStr <- paste("Driver=SQL Server;Server=", " localhost", ";Database=" , "tpcxbb_1gb" , ";Trusted_Connection=true;" , sep="" ); # Broken... due to space in the paste.
connStr <- paste("Driver=SQL Server;Server=", "localhost", ";Database=" , "tpcxbb_1gb" , ";Trusted_Connection=true;" , sep="" ); #Fixed.

Answer 3

I just finished troubleshooting the same error message.

"Data source name not found and no default driver specified" means that the dsn and driver need to be included in the connection details.

The following article from rstudio is helpful, and here's what worked for me: https://support.rstudio.com/hc/en-us/articles/214510788-Setting-up-R-to-connect-to-SQL-Server-

library(DBI)
con2 <- dbConnect(odbc::odbc(),
                 .connection_string = "Driver={Simba SQL Server ODBC Driver};",
                 server= "<server>", 
                 dsn = "<data source name>",
                 database = "<database>",
                 uid = rstudioapi::askForPassword(prompt = 'Please enter username: '),
                 pwd = rstudioapi::askForPassword("Database password"),
                 timeout = 10,
                 trusted_connection = TRUE)

In addition to getting the connection correct, an ODBC Manager needs to be setup correctly and a driver that works with SQL Server needs to be chosen/installed.