R markdown fails to compile using Azure interactive authentication

Question

I need to find a way to connect to an SQL database with multi factor authentication.

I am using the following code, which works fine for a normal R document:

server <- "xxx"
database <- "yyy"
user <- "zzz"


 conn <- dbConnect(odbc(),                 
Driver = "ODBC Driver 17 for SQL Server",                 
Server = server,                 
Database = database,                 
UID = rstudioapi::askForPassword(user),                 
Authentication = "ActiveDirectoryInteractive",                 
Port = 1433)

But I am using R markdown to create a report, and when I knit my file, it seems to work fine to begin with, but after I have written my password in the pop-up window, it stops compiling at 18% done, and just stays there (i have tried to leave it alone for an hour, but nothing happens, it's still at 18% - so I get no error, but it just won't finish compiling)..

Answer 1

You can use the code below to connect to an Azure SQL database in R markdown:

library(DBI)
server <- "<serverName>.database.windows.net"
database = "<databaseName>"
con <- DBI::dbConnect(odbc::odbc(), 
                 UID = rstudioapi::askForPassword("username"),
                 Driver="ODBC Driver 17 for SQL Server",
                 Server = server, Database = database,
                 Authentication = "ActiveDirectoryInteractive")

When you run the above code in R markdown, it will prompt for a username as shown below:

Enter your AD username, click "OK," and a window will pop up for password and verification as follows:

Enter your password and click "Sign In." A verification will be raised as shown below:

After completing the verification, the database will connect successfully without any errors, as shown below:

After a successful connection, insert an SQL chunk in the markdown and run a query with the connection above, as shown below: