Connect to Azure SQL in R via Microsoft Entry tokens • astro-theme-resume

One of the best features of the Azure SQL server is that you can connect to your database using your Azure Active Directory (AAD, now called Entra ID) identity.

This works by retrieving an authentication token from Entry ID and then specifying this in the pre-connection attribute SQL_COPT_SS_ACCESS_TOKEN. If you are interested in the details, you can checkout the issue about the implementation in the odbc package for R.

This method does not only work for R but for any language that provides a package for an odbc driver that supports it. More information can be found in the Microsoft docs for Python or a tutorial that explains the necessary steps in more detail.

Let’s go back to R. First, you need to use the odbc package for R and install the Microsoft ODBC driver for SQL Server. You have probably installed the driver before if you have been connected to your sql server.

Retrieve the token

There are multiple ways to retrieve a token. If you are developing locally, one of the easiest ways is to use Azure CLI. Make sure you have Azure CLI installed.

Make sure that you are logged by running this command in a terminal: az login --allow-no-subscriptions. The flag --allow-no-subscriptions makes sure you are able to retrieve a token even if you have no Azure subscriptions.

Then, you can retrieve a token. I am using the withr package here to make my life easier:

result <- withr::with_tempfile("tf", {
  suppressWarnings({
    token <- system2(
      "az",
      c(
        "account get-access-token",
        "--resource https://database.windows.net",
        "--query accessToken",
        "--only-show-errors",
        "--output yaml"
      ),
      stdout = TRUE,
      stderr = tf
    )
  })
  system2error <- readLines(tf)
  list(token = token[1], error = system2error)
})
print(result$token)

This should print the token in your R console. Please note: this token is like a password. Please handle it with care.

Connect to the database

You can check the attributes of this JWT using a handy tool from Microsoft directly: jwt.ms. It is safe to post your token there.

Finally, you have to connect to your server:

con <- DBI::dbConnect(
  odbc::odbc(),
  # please check which version of the driver you are using (18 is the most recent one)
  driver = "ODBC Driver 18 for SQL Server",
  server = "your.database.com,1433",
  database = "your-database-name",
  Encrypt="yes",
  TrustServerCertificate="No",
  attributes = list("azure_token" = token)
)

Note that some people were unable to connect with a token retrieved for the resource https://database.windows.net but had to retrieve one for https://database.windows.net/ (notice the / at the end).