CODEWITHSUNDEEP
rodbcr-dbi
Mel
Mel

Reputation: 750

How to connect via R to MS SQL database requiring integrated active directory authentication using odbc and DBI packages?

I am currently trying to connect to some SQL databases from within RStudio.

The first database that uses windows authentication I can access easily using:

con <- dbConnect(odbc(), Driver = "SQL Server", Server = "ServerName", 
                 Database = "DatabaseName", Trusted_Connection = "True")

However we have another database that requires integrated active directory authentication and the above code (changed for the relevant server and database names) generates the error:

Error: nanodbc/nanodbc.cpp:950: HY000: [Microsoft][ODBC SQL Server Driver][SQL Server]Windows logins are not supported in this version of SQL Server.

I have tried various things including the following:

con <- dbConnect(odbc(), Driver = "SQL Server", Server = "Server_Two_Name", 
                 Database = "Database_2_Name", Trusted_Connection = "True", Authentication = "ActiveDirectoryIntegrated")

con <- dbConnect(odbc(), Driver = "SQL Server", Server = "Server_Two_Name", 
                 Database = "Database_2_Name", Trusted_Connection = "FALSE", Authentication = "ActiveDirectoryIntegrated")

However nothing I have tried allows me to connect successfully. (I can successfully connect to the database using SQL Server Management Studio if I specify the server and database names and set the authentication to "Active Directory - Integrated", without needing to enter a password or user name).

I have tried reading the guidance at https://db.rstudio.com and the documentation for the DBI and odbc packages but I did not fully understand the information provided.

Please can anyone advise?

Upvotes: 2

Views: 7530

Answers (1)

StatsStudent
StatsStudent

Reputation: 1594

This works for my Azure cloud SQL Server databases that require "Azure Active Directory - Integrated" authentication:

#Database Connection Details
SQL_SERVER_NAME <- "Your SQL Server Name Goes Here"
SQL_DATABASE_NAME <- "Your Database Name Goes Here"

# Connect to database (assumes odbc and DBI packages are installed and libraries added)
con <- dbConnect(odbc(),
  Driver = "ODBC Driver 17 for SQL Server",
  Server = SQL_SERVER_NAME,
  Database = SQL_DATABASE_NAME,
  Authentication = "ActiveDirectoryIntegrated"
)

Note too that this also requires the ODBC Driver 17 for SQL Server to be installed on your machine.

Upvotes: 2

Related Questions