Connecting To A SQL Server

General
1

Dear All,
I am really new to R. I have just started using it last week. I am trying to connect to MS-SQL using the below code. I am going through the Programming in R course from Edx. Below is the link for it. I have found the code to connect to a server using the below code only. Remember the server details, all are dummy here( as I can't share the original).
I have also installed the RODBC and odbc packages, as told in the course.

Please help!!
https://courses.edx.org/courses/course-v1:Microsoft+DAT209x+1T2018/courseware/edee8bc7ed6b4ada9a3cbd046c4e7205/3cf83f25dc82444aa93815735cfaa9f1/1?activate_block_id=block-v1%3AMicrosoft%2BDAT209x%2B1T2018%2Btype%40vertical%2Bblock%409ffce533fd7d4130ab995a6924b25c37

connStr <- paste("Server= 1112.1024.101.174","Database = stab",
    "UserName = tab", 
    "Password = %12345",
    "Driver = {SQL Server}", 
    Sep =";"
)

Conn <- odbcDriverConnect(connStr)

I could run the first part of the code, ( creating the variable connStr), but cannot connect using the lsat part of the code. The error shown is below:

could not find function "odbcDriverConnect

Thanks In Advance,
Kumar

2

Hi @kumar111091, welcome to the R community! I'd encourage you to check out this website that is dedicated to R and Databases: http://db.rstudio.com/ I would recommend to start with the Getting Started section: http://db.rstudio.com/getting-started/

3

Hi @kumar111091, welcome to RStudio Community!

It looks like your code was not formatted correctly to make it easy to read for people trying to help you. Formatting code allows for people to more easily identify where issues may be occuring, and makes it easier to read, in general. I have edited you post to format the code properly.

In the future please put code that is inline (such as a function name, like mutate or filter) inside of backticks (`mutate`) and chunks of code (including error messages and code copied from the console) can be put between sets of three backticks:

```
example <- foo %>%
  filter(a == 1)
```

This process can be done automatically by highlighting your code, either inline or in a chunk, and clicking the </> button on the toolbar of the reply window!

This will help keep our community tidy and help you get the help you are looking for!

For more information, please take a look at the community's FAQ on formating code

4

Hi @tbradley, something like this ??
If you look at the video in the course it is written something of this sorts.


connStr <- paste("Server= 1112.1024.101.174",
    "Database = stab",
    "UserName = tab", 
    "Password = %12345",
    "Driver = {SQL Server}", 
    sep =";"
)

Conn <- odbcDriverConnect(connStr)

Thank You,

5

This function is part of RODBC :package:. You either need to load it with a library(RODBC) beforehand or use RODBC::odbcDriverConnect at the call. Otherwise, the function won't be find in your session.

6

HI @edgararuiz, I did read what was given on the page that you had shared. I had already installed the RODBC package and did a check for the drivers downloaded after the installation. I moved on to this page and used the code provided here:

http://db.rstudio.com/databases/microsoft-sql-server/

I tried what was given on the above page, but its still showing the below error.

Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I am really sorry, if I am making some very stupid error, but as I said I am new to this and doing it for the first time.
Thank You,
Kumar Ashwarya

7

SQL Server's documentation indicates that the user name and password fields are UID and PWD. When I run this on my system, I also find that Driver = SQL Server is problematic, but Driver=SQL Server (no spaces around parentheses) works well. Try the following:

library(RODBC)
connStr <- paste("Server= 1112.1024.101.174",
    "Database=stab",
    "UID=tab", 
    "PWD=%12345",
    "Driver=SQL Server", 
    sep =";"
)

channel <- odbcDriverConnect(connStr)

Optional: Using DBI and odbc

You could also do something equivalent with the DBI and odbc packages. The advantage here is that you may form connections with dplyr and not be required to work entirely in SQL if you don't want to (which has occasional advantages).

library(DBI)
library(odbc)

conn <- dbConnect(odbc(), 
    Driver = "SQL Server",
    Server = "1112.1024.101.174",
    Database = "stab",
    UID = "tab",
    PWD = "%12345")

Some other comments

If your Windows User name is stored in the SQL database, you can omit having to type in your password to make the connection by using the code below. This relies on Windows Authentication and we find it much preferable to having to enter passwords.

connStr <- paste("Server= 1112.1024.101.174",
    "Database=stab",
    "Trusted_Connection = yes",
    "Driver=SQL Server", 
    sep =";"
)

If Windows Authentication isn't an option and you must add the password, I would strongly recommend upgrading to ODBC Driver 13 for SQL Server. The updated driver will force encryption of your user name and password when connecting to the server. The call would look like

connStr <- paste("Server= 1112.1024.101.174",
    "Database=stab",
    "Trusted_Connection = yes",
    "TrustServerCertificate=yes", # See note below
    "Driver=ODBC Driver 13 for SQL Server", 
    sep =";"
)

Note: TrustServerCertificate=yes may be acceptable if you are on a protected network and are confident in the security. Otherwise, it may be necessary to install SSL certificates on your machine and the server.

2 Likes
8

Hi @nutterb,

Error: nanodbc/nanodbc.cpp:950: 28000: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'otab'

I am getting the above error when I used the below code:

library(DBI)
library(odbc)

conn <- dbConnect(odbc(), 
    Driver = "SQL Server",
    Server = "1112.1024.101.174",
    Database = "stab",
    UID = "tab",
    PWD = "%12345")

Also, is there a way using which I can get help on errors that I get ?

Thank You,
Kumar Ashwarya

9

This is a step forward:

Your previous error Data source name not found and no default driver specified suggested that your request was never making it to the SQL Server instance.

The new error, Login failed for user 'otab', indicates that the otab user does not exist on the SQL Server, or that it does not have permission to connect to it. Neither of these are problems we can solve in R, and you will need to work with the database administrators to gain access.

One exception that may be the case is if the user names on the SQL Server are associated with a domain. If you know what domain your user name is on, you may try UID = "domain\\tab".

But again, a lot of this depends on the settings on the SQL Server. Do you use SQL Server Management Studio to interact with your database at all?

10

@nutterb

Woooh!!!! Thank You for your help. The below code worked!
I was able to load connect the database. I can view the tables in the connections tab in R Studio.


> library("RODBC")
> conn <- dbConnect(odbc(), 
+                   Driver = "SQL Server",
+                   Server = "server name",
+                   Database = "databasename",
+                   UID = "userid",
+                   PWD = "password")

But I think I for I need to access one of the view from the database? Is there an article of how can I read a table from this connection?

Thank You again.

11

Did you read this one
Solutions - Database Queries With R

It should work with library(odbc) instead. You either need odbc or RODBC.

3 Likes
12

Sorry it was a typo, I had meant "odbc" only.