unable to connect to azure vm database from R

Question

I am trying to connect to a Microsoft SQL Server database running on a virtual machine in Microsoft Azure, from R.

Here is what my SQL server looks like

This is my connection string

library(RODBC)
channel = odbcConnect(dsn="something.cloudapp.net",uid="myusername",pwd="mypassword");

However, I keep getting this error

Warning messages:
1: In odbcDriverConnect("DSN=servername.cloudapp.net,1433;UID=myusername;PWD=mypassword") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("DSN=servername.cloudapp.net,1433;UID=myusername;PWD=mypassword"):
  ODBC connection failed

Why do I keep getting this error?

Have you [created a dsn](https://www.websense.com/content/support/library/data/v76/help/windows%20dsn.aspx) as RODBC, as far as I have used it, requires a defined dsn to login. I created it as a system dsn for what I do and it works for me. You do require admin to perform this task. — Badger, Sep 21 '15 at 21:54

score 3 · Answer 1 · edited May 23 '17 at 12:31

Try using the more flexible odbcDriverConnect function like this:

odbcDriverConnect('driver={SQL Server};Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;')

Here it is with carriage returns for readability:

odbcDriverConnect(
    'driver={SQL Server};
     Server=myServerAddress;
     Database=myDataBase;
     User Id=myUsername;
     Password=myPassword;')

unable to connect to azure vm database from R

1 Answers1