Microsoft Fabric to RStudio Desktop

[sctyner]

Similar to this reddit user, I'm looking to connect data stored in Microsoft Fabric (either as a lakehouse or as a SQL endpoint) from my local RStudio Desktop (free) application.

Note: I do not want to use SparkR notebooks in fabric as this link suggests, because 1) tidyverse doesn't work seamlessly with SparkR SQL connections and 2) I prefer working in RStudio since I've been using it for years and it has nice features and keyboard shortcuts that don't work in MS Fabric notebooks.

Per Microsoft, accessing data stored in Fabric requires authentication with Azure Active Directory.

So far, I have tried:

1. Using the AzureRMR package to authenticate to Azure AD, which seemed to work.

library(AzureRMR)
az <- create_azure_login()

2. Assuming that this successfully authenticates with Azure AD, I've tried connecting to the SQL endpoint (accessed as described here) via ODBC.

   a. This required me to first install the appropriate driver as described here
   b. Then, I attempted several connection commands:

# first attempt 
con <- DBI::dbConnect(odbc::odbc(), 
                      driver = "ODBC Driver 18 for SQL Server",
                      database = "{SQL connection string}",
                      UID = "{Azure username}"
                      PWD = "{Azure password}",
                      host = "localhost", port = 1433)
# error message 
> Error: nanodbc/nanodbc.cpp:1135: 00000: [Microsoft][ODBC Driver 18 for SQL Server]
> Neither DSN nor SERVER keyword supplied  [Microsoft][ODBC Driver 18 for SQL Server]
> Invalid connection string attribute 

# second attempt
con <- DBI::dbConnect(odbc::odbc(), 
                      driver = "ODBC Driver 18 for SQL Server",
                      server = "{SQL connection string}",
                      UID = "{Azure username}"
                      PWD = "{Azure password}",
                      host = "localhost", port = 1433)
> Error: nanodbc/nanodbc.cpp:1135: 00000: [Microsoft][ODBC Driver 18 for SQL Server]
> Login timeout expired  [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Timeout error [258].   
> [Microsoft][ODBC Driver 18 for SQL Server]Unable to complete login process due to delay in prelogin response
> [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute 

It's possible I may be going at this completely the wrong way. I was asked to post this after I requested help on Twitter.

Thanks,
Sam

[tom_rstudio]

Howdy! Can you try with the AzureAuth package instead?

GitHub - Azure/AzureAuth: R package for OAuth 2.0 authentication with Azure Active Directory

The main function in AzureAuth is get_azure_token, which obtains an OAuth token from AAD. The token is cached in a user-specific directory using the rappdirs package, and future requests will use the cached token without needing you to reauthenticate.

library(AzureAuth) 
token <- get_azure_token(
   resource="myresource", 
   tenant="mytenant", 
   app="app_id",
    ...
)

[sctyner]

I thought of doing that, but I didn't know how to map the parameters resource, tenant, and app to the MS Fabric SQL endpoint or Lakehouse. Do you have any advice there?

[tom_rstudio]

This resource suggests using a PAT since you're using 3rd party software (R). I don't have access to OneLake or MS Fabric unfortunately so can't add much more detail.

There's a bit more detail for AzureStor vs ODBC, and since OneLake should be ADLS2 compliant, the workflows should work...

https://cran.r-project.org/web/packages/AzureStor/vignettes/intro.html

Also see: How do I connect to OneLake? - Microsoft Fabric | Microsoft Learn for how to connect to OneLake and the URI/URL concepts.