ODBC connection in R with Azure Active Directory Password authentication

user_9112767 0
Viewed 1074

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 ?!

azure-active-directory odbc r sql-server
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

user_105050400
Related