Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ODBC connection in R with Azure Active Directory Password authentication

I'm trying to connect to SQL server from R and I'm using the Azure Active Directory Password authentication. My connection in R define as follow :

ch <- odbc::dbConnect(odbc(),
                        DSN = "myDSN",
                        Database = "dbname",
                        Authentication="ActivedirectoryPassword",
                        UID = uid,
                        PWD = password
        )

This is failing by the following error :

Error: nanodbc/nanodbc.cpp:983: FA004: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user 'my_user_id' in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0x800401F0; state 10
CoInitialize has not been called. 

However, when I test the exact same setting on Microsoft SQL Server DSN Configuration, everything is fine and I'm able to connect successfully to the same database : enter image description here

Any idea if I'm missing something here ?!

like image 284
user9112767 Avatar asked Sep 01 '25 03:09

user9112767


1 Answers

According to my test, if I use package odbc 1.2.3, it is ok for me.

library(odbc)
uid <- ""
pwd <- ""
ch <- odbc::dbConnect(odbc(),
  DSN = "mydsn",
  Database = "xxx",
  Authentication="ActivedirectoryPassword",
  UID = uid,
  PWD = pwd
)
dbListTables(ch)

enter image description here

like image 134
Jim Xu Avatar answered Sep 02 '25 18:09

Jim Xu