How to connect RStudio server in unix to Microsoft SQL server

RStudio IDE
1

How to connect RStudio server in linux to Microsoft SQL server?
RSudio server is free version.

We got some information that creating ODBC and creating system DSN we can connect,but system DSN is not working.

So any idea will be appreciate.

2

Have you got the necessary odbc drivers for the MS SQL server?

3

Yes I got the driver and it is working in Rstudio terminal entering SQL service account user id and password but not working using my DSN.

It is saying invalid user etc.

So when I am using Rstudio terminal using below connection detail it is working.

library(odbc)
library(RODBC)

con <- dbConnect(odbc(),Driver = "ODBC Driver 17 for SQL Server",Server = "VABSQLDWMRTD04\\Dvlp1,14415",Database = "B2B_Mgmt_Datamart_Dvlp",UID ="SAQ-R-B2B-T",PWD = rstudioapi::askForPassword("Database password"))

con <- dbConnect(odbc::odbc(), "MSSQL")
4

Could you provide the error message in the console output, please.

dbConnect() is from the DBI package. I believe RODBC uses the odbcConnect() function to connect, so I'm not sure whether you have mixed things up.

5

now when I am trying below script it is giving me error.

> con <- odbcConnect(odbc::odbc(), "MSSQL")
Error in as.character.default(new("OdbcDriver", )) : 
  no method for coercing this S4 class to a vector
6

and when trying to connect using dbconnect I am getting below error

con <- dbConnect(odbc::odbc(), "MSSQL")
Error: nanodbc/nanodbc.cpp:950: 28000: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''.
7

Hi, I think you need to use:

con <- dbConnect(odbc::odbc(), "MSSQL" ,UID ="SAQ-R-B2B-T",PWD = rstudioapi::askForPassword("Database password"))

I'm thinking that neither the password nor the user id are part of the DSN entry

9

I missed the dsn name,so sending here with DSN name.

[MSSQL]
Driver = ODBC Driver 17 for SQL Server
Description = Microsoft ODBC Driver 17 for SQL Server
Server =tcp:VABSQLDWMRTD04\Dvlp1,14415
database =B2B_Mgmt_Datamart_Dvlp
UID=SAQ-R-B2B-T
PWD=HajdfkaF20$#

11

Ok, how about con <- dbConnect(odbc::odbc(), dsn = "MSSQL" ,UID ="SAQ-R-B2B-T",PWD = rstudioapi::askForPassword("Database password"))

12

Hi,
Thanks for the reply.
As per my above DSN(MSSQL) file.
I should try to connect directly in Rstudio .Why should I enter password again in RStudio?
I don't want that,that's why mentioning password in DSN.

Is my DSN looks good? If yes then how I will connect from Rstudio just mentioning DSN name and without entering password?

13

Hi, I guess the answer is that is not possible unless you are using a sort of integrated security with AD, or Kerberos. You'll need to have a password saved either in your DSN, in some other file, or enter it at run time (via the ask password mechanism)

14

That's true.
I am agree with you,but already I have saved my password in DSN and wanted to run using conn from RStudio why it is not working?
Do you mean to say both I have to do? i.e need to save password in DSN also have to enter wile running time?