Using CloudyR with ShinyApps.io, Azure Key Vault and Azure SQL Database

ShinyApps.io is a great resource for sharing dashboards

Basics

The Shiny package in R offers a relatively unique set of capabilities to deliver fast, but powerful, dashboarding. While it’s used locally, it can be relatively easy to access data stores and collect information — but productionising is a whole lot trickier.

ShinyApps.io is an outstanding product from the people at RStudio. It offers an exceptionally simple platform on which to deploy and serve Shiny dashboards (including authentication and logging). When attempting to move towards productionising these same reports, you can utilise the outstanding CloudyR package to access Azure resources with minimal fuss.

Getting Azure Set Up and Registering Your Shiny App

A few steps are required:

• Firstly, if using an Azure SQL Database, you have to check the list of IP addresses to be whitelisted on the SQL Server itself. This can be done in the firewalls and virtual networks section of the Azure Portal:

The IP addresses shown below are correct at the time of writing

• Secondly, the Shiny App should be deployed and the URL for the app itself can be used in the Azure App Registration resource, where the redirect URI can be passed in as the deployed dashboard URL (ie, https://[account].shinyapps.io/[dashboardname]). When this is completed, a client secret should be created, like below, and stored for later:

The client secret disappears shortly after creation

• Thirdly, access the Key Vault and save the connection string (or whatever breakdown you think is appropriate for your organisation) as a Key Vault secret. Then, using Access Control (IAM), grant access to read the Key Vault Secret to the app you just registered in the previous step.

Remember to save the role assignment — it’s easy to forget in the UI

With all this complete, it’s simply a case of using the right packages and accessing the right information within R/Shiny.

The connection string can be largely identical, with the exception of the driver selection, so we’ll store this in the config file. The ‘shinyapps’ segment of the config file will be used inside ShinyApps once deployed. I also added an ‘env’ segment to help keep simple some decisioning over which process to follow:

default:
    app: *insert the app ID found in the app registration*
    ten: *and the tenant ID found in the app registration*
    res: 'https://vault.azure.net/'
    vlt: 'https://[yourvault].vault.azure.net/'
    drv: 'driver={SQL Server};'
    kv_sec: *the secret name as define in your key vault*
    cl_sec: *the app registration secret you created earlier*
    env: 'local'
shinyapps:
    app: *insert the app ID found in the app registration*
    ten: *and the tenant ID found in the app registration*
    res: 'https://vault.azure.net/'
    vlt: 'https://[yourvault].vault.azure.net/'
    drv: 'driver={SQLServer};'
    kv_sec: *the secret name as define in your key vault*
    cl_sec: *the app registration secret you created earlier*
    env: 'remote'

• Locally, we can use ‘driver={SQL Server}’
• Within ShinyApps, we can use ‘driver={SQLServer}’ (no space)

To begin, we should import the config package to help store key information external to the codebase itself, as well as the AzureAuth and AzureKeyVault packages from the CloudyR suite. Additionally, the DBI package will help us build together the connection to the database:

library(config)
library(DBI)
library(AzureAuth)
library(AzureKeyVault)

And from here it’s a case of passing through the appropriate R code to allow for both local testing, and correct remote deployment.

# In ShinyApps, we have to create a token and pass that token to the Key Vault to retrieve the secret
# If we're testing locally, we can simply call the key_vault function without the token
if (conf$env == "remote") {
  tok <- AzureAuth::get_azure_token(
    resource = "https://vault.azure.net/",
    tenant = conf$ten,
    app = conf$app,
    auth_type = "client_credentials",
    password = conf$cl_sec
  )
  kv <- AzureKeyVault::key_vault(
    token = tok,
    url = conf$res,
    tenant = conf$ten
  )
} else if (conf$env == "local") {
  kv <- AzureKeyVault::key_vault(
    url = conf$vlt,
    tenant = conf$ten
  )
}
# Once we have access, we can collect the secret and pass it through to be our connection string
secr <- kv$secrets$get(conf$kv_sec)
con_str <- secr$value
# And finally, we can use the string to connect to, and collect from, the database
con <- dbConnect(
    odbc::odbc(),
    .connection_string = paste0(conf$drv, con_str),
    timeout = 5
)
dat <- dbGetQuery(
    conn = con,
    statement = "SELECT * FROM [Table];"
)

Hopefully this has helped navigate the complexities of using Azure Key Vault within a Shiny App. ShinyApps.io is a great resource for use within organisations and data functions — and it’s excellent to have packages like CloudyR helping integrate those with cloud providers like Azure!

Happy counting!