Connect to remote SQL Server with R

Question

I'm trying to connect R to a remote SQL Server based on this. I use

library(DBI)
conn <- dbConnect(
  drv = RMySQL::MySQL(),
  dbname = "td",
  host = "hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com",
  username = "trades",
  password = "u#6MS06")

but the connection is not working.Failed to connect to database: Error: Can't connect to MySQL server on 'hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com' (0) What do I miss? Note that I do not give the real credentials here. Then I want to connect with a table named "rep_user_listings".

After installing `ODBC Driver 11 for SQL Server I have also used :

library(DBI)
conn <- dbConnect(
  drv = odbc::odbc(),
  driver = "ODBC Driver 11 for SQL Server",
  database = "td",
  server = "hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com",
  uid = "trade",
  pwd = "u#6MS06X")

and I get:

Error: nanodbc/nanodbc.cpp:950: 08001: [Microsoft][ODBC Driver 11 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].

and

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com\\SQLEXPRESS; Database=tdsh;Uid=trade; Pwd=u#6MS06Xv; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from rep_user_listings;"))
odbcClose(dbconnection)

and I get:

Warning messages:
1: In odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com\\SQLEXPRESS; Database=tdsh;Uid=tradeshow; Pwd=u#6MS06Xvdoy; trusted_connection=yes") :
  [RODBC] ERROR: state 08001, code -1, message [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 
2: In odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com\\SQLEXPRESS; Database=tdsh;Uid=tradeshow; Pwd=u#6MS06Xvdoy; trusted_connection=yes") :
  [RODBC] ERROR: state HYT00, code 0, message [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
3: In odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com\\SQLEXPRESS; Database=tdsh;Uid=tradeshow; Pwd=u#6MS06Xvdoy; trusted_connection=yes") :
  [RODBC] ERROR: state 08001, code -1, message [Microsoft][ODBC Driver 11 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
4: In odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com\\SQLEXPRESS; Database=tdsh;Uid=tradeshow; Pwd=u#6MS06Xvdoy; trusted_connection=yes") :
  ODBC connection failed

It appears that you're using a MySQL driver (based on the naming), but attempting to connect to a SQL Server RDS instance. That won't ever work. You need to use an MS SQL Server driver. — alroc, Jul 27 '19 at 19:26
Also, please provide more detail than "doesn't work." It's not possible to help you with such a vague description. Error messages will give clues. Try connecting to another known good database first to rule out other factors. — alroc, Jul 27 '19 at 19:27
Possible duplicate of [Connect R to a SQL Server database engine](https://stackoverflow.com/questions/39401230/connect-r-to-a-sql-server-database-engine) — alroc, Jul 27 '19 at 19:29
As for what to change, you need to change the driver that you're using - the third line. You should be able to find the correct way to connect in https://stackoverflow.com/q/39401230/1324345. You do not "connect to" a table, you execute a query against that table using the connection to the database instance. — alroc, Jul 27 '19 at 19:30
I suggest that https://stackoverflow.com/questions/39401230 is *not* a duplicate based on (1) OP is using `DBI` not `RODBC` (generally incompatible), and (2) most `DBI`-based recommendations in that answer are either intentionally archived/deprecated or just have not been updated in years. (`RJDBC` does appear to be current, so I guess that's always an option, even if it does require `rJava` ... which can be problematic without access to a well-tuned java installation/configuration.) — r2evans, Jul 27 '19 at 19:50

score 3 · Accepted Answer · answered Jul 27 '19 at 19:46

There isn't a native SQL Server client in development for R, the most recent I'm aware of (https://github.com/imanuelcostigan/RSQLServer) is archived/deprecated in lieu of the odbc package.

Try this:

library(DBI)
conn <- dbConnect(
  drv = odbc::odbc(),
  driver = "ODBC Driver 17 for SQL Server",
  database = "td",
  server = "hmsales.cav7mnk7ifa9.us-west-2.rds.amazonaws.com",
  uid = "trades",
  pwd = "u#6MS06")

(And if that works, immediately change your password. :-)

I chose "ODBC Driver 17 for SQL Server" because I previously installed the mssql driver for my windows and linux machines (current version available here), and found the driver "name" here:

unique(odbc::odbcListDrivers()$name)
# [1] "SQL Server"                    "PostgreSQL ANSI(x64)"         
# [3] "PostgreSQL Unicode(x64)"       "SQLite3 ODBC Driver"          
# [5] "ODBC Driver 17 for SQL Server"

If you have an older version installed (such as 11 or 13) or something else, use that driver instead (or upgrade).

Error: nanodbc/nanodbc.cpp:950: 08001: [Microsoft][ODBC Driver 11 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. — firmo23, Jul 28 '19 at 10:16
Are you able to connect via any mechanism, not R? Are you certain that the rest of your parameters are correct? (e.g., port, server) Is the instance started and are security group set right? I do not have experience with AWS, perhaps https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToInstance.html might be useful. — r2evans, Jul 28 '19 at 20:23

score 1 · Answer 2 · answered Jul 29 '19 at 01:42

Firstly, don't give out your credentials online. Based on what i am looking at, open your terminal and ping your server to make sure you have network access to your server.

If you are using Linux one of the known issues is dont use domain name use ipaddess if you have also, make trusted connection =no.

Connect to remote SQL Server with R

2 Answers2