Search code examples
sql-serverrazurerodbc

R connection to MS SQL

Using R I am trying to connect to MS SQL 2014 on an Azure VM (not windows authentication)

library(RODBC)
conn <- odbcDriverConnect(connection = "Driver=SQL Server;Server=someinternetmachine.cloudapp.net;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;")
queryResult <- sqlQuery(conn, "SELECT top 10 * FROM sometable")

With RODBC is there anywhere to do this using just a connection string (no DSN)?

Using the above I get the below errors

Warning messages:
1: In odbcDriverConnect("driver={SQL Server};server=servername\\instancename,port;database=testing;uid=abc;pwd=123456") :
  [RODBC] ERROR: state 08001, code 6, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]Specified SQL server not found.
2: In odbcDriverConnect("driver={SQL Server};server=servername\\instancename,port;database=testing;uid=abc;pwd=123456") :
[RODBC] ERROR: state 01000, code 11001, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver={SQL Server};server=servername\\instancename,port;database=testing;uid=abc;pwd=123456") :

ODBC connection failed

Solution

  • Managed to get this working with 

    driver.name <- "SQL Server"
db.name <- "master"
host.name <- "someinternetmachine.cloudapp.net"
port <- ""
server.name <- "someinternetmachine.cloudapp.net"
user.name <- "MyUsername"
pwd <- "MyPassword"
# Use a full connection string to connect 
con.text <- paste("DRIVER=", driver.name,
              ";Database=", db.name,
              ";Server=", server.name,
              ";Port=", port,
              ";PROTOCOL=TCPIP",
              ";UID=", user.name,
              ";PWD=", pwd, sep = "")

con1 <- odbcDriverConnect(con.text)

res <- sqlQuery(con1, 'select * from information_schema.tables')

odbcCloseAll()