Connect to Azure SQL in R via Entra ID / AAD tokens from Azure CLI
Tags: r, sqlserver, azure
Published: 2024-06-28 13:31:15

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](https://github.com/r-dbi/odbc/issues/299).

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](https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-sql-python-quickstart?view=azuresql&tabs=windows%2Csql-inter) or a [tutorial](https://medium.com/@everton_b.o/how-to-connect-to-azure-sql-database-with-mfa-using-python-on-macos-linux-de11cfdefb63) that explains the necessary steps in more detail.

Let's go back to R. First, you need to use the [`odbc`](https://github.com/r-dbi/odbc/) package for R and install the [Microsoft ODBC driver for SQL Server](https://learn.microsoft.com/de-de/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver16). 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](https://learn.microsoft.com/de-de/cli/azure/install-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`](https://github.com/r-lib/withr) package here to make my life easier:

```R
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](https://jwt.ms/). It is safe to post your token there.

Finally, you have to connect to your server:

```R
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](https://github.com/r-dbi/odbc/issues/299#issuecomment-2192531506) 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).