R - How to connect to a database with authentication 'SQL Server Authentication'-CodePudding

I'm trying to connect to a database through R that has authentication type 'SQL Server Authentication' as stated through 'Microsoft SQL Server Management Studio 18'.

I know the library DBI has a function called 'dbconnect()' that has an authentication parameter but the documentation does not show what the different type of authentications are. I've tried both 'SQLServer' and 'SQL Server' but it just outputs the same error:
"Invalid value specified for connection string attribute 'Authentication' "

Has anyone made a similar connection?

CodePudding user response：

Here is code that will connect to the Northwind database on LOCALHOST\EXPRESS instance as the current Windows user:

conn_str <- 'driver={SQL Server};server=LOCALHOST\\SQLEXPRESS;database=Northwind;trusted_connection=true')
mssql_conn <- DBI::dbConnect(odbc::odbc(), .connection_string = conn_str)

CodePudding user response：

not used this package myself but looking at https://github.com/r-dbi/odbc#connecting-to-a-database it gives this snippet as an example:

library(DBI)
con <- dbConnect(odbc::odbc(),
  driver = "PostgreSQL Driver",
  database = "test_db",
  uid = "postgres",
  pwd = "password",
  host = "localhost",
  port = 5432)

To make it specific to your usage you'll need to know which ODBC driver to use which will more than likely be something like "SQL Server Native Client 11.0" or "ODBC Driver 17 for SQL Server". You can check this (on Windows at least) by going to your ODBC Data Source Administrator and finding out which driver(s) you've got. Something like might be more what you're after:

library(DBI)
con <- dbConnect(odbc::odbc(),
  driver = "SQL Server Native Client 11.0",
  database = "db_name_goes_here",
  uid = "Wallace_H",
  pwd = "password_321",
  host = "server_goes_here")

Even easier is if you've got a named DSN Configuration (again can check via ODBC Data Source Admin on Windows) with the username and password embedded, if you've got that then you can connect via:

con <- dbConnect(odbc::0dbc(), "DSN_Name_goes_here")