CODEWITHSUNDEEP
sql-serverrazurerodbc
Chris McKelt
Chris McKelt

Reputation: 1388

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

Upvotes: 0

Views: 1197

Answers (2)

Chris McKelt
Chris McKelt

Reputation: 1388

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()

Upvotes: 1

Umachandar - Microsoft
Umachandar - Microsoft

Reputation: 484

Did you choose public provisioning for the VM in Azure? Additionally, you need to open the ports for SQL Server in Windows Firewall (port number depends on default or named instance). Also, in case of named instance if you are using dynamic ports then SQL Browser also needs to be opened up. More information can be found in the link here.

Upvotes: 1

Related Questions