Deploy and connect Azure Function to SQL in Azure

This Article is the third in the series about using Azure Form Recognizer to extract data from a paystub and save it to a database. In the previous article we converted the console application to an Azure Function. Here we will setup the Azure environment and deploy the Azure Function to Azure. The code we will be deploying was written in the previous articles, if you haven't followed along the code is also available on GitHub.

This article also assumes you have provisioned the following:

Azure SQL Database
Azure Form Recognizer
Azure KeyVault

All these were setup in the initial article of this series, I encourage you to take a look but if you're in a hurry the scripts can be found on GitHub

Update KeyVault for RBAC Authentication

Because the Azure Function will be running under a Managed Identity, we need to update the KeyVault to allow the Azure Function to access the secrets stored in the KeyVault through it's Active Directory identity. To do this we need to enable RBAC authorization on the KeyVault. To complete this step you need the User Access Administrator role assigned. If you already have a KeyVault that needs RBAC enabled (the previous articles did not enable RBAC) you can run the following script to enable RBAC on the KeyVault. If you don't have the resources provisioned yet, you can run the updated Provision Form Recognizer Resources which will provision the KeyVault with RBAC enabled.

NOTE: If creating a new KeyVault with RBAC ensure you have Key Vault Secrets Officer role assigned so you can save secrets to the KeyVault.

Step 1: Enable RBAC on KeyVault

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $KeyVaultName
)

# -------------------------------------------------------
# Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$KEY_VAULT_NAME = $KeyVaultName

# -------------------------------------------------------
# Update Key Vault to Enable RBAC Authorization
# -------------------------------------------------------
az keyvault update `
    --name $KEY_VAULT_NAME `
    --resource-group $RESOURCE_GROUP_NAME `
    --enable-rbac-authorization true

Setup Azure Function App Resources

Before we can deploy our function we need an Azure Function App created in Azure. The following script will create a C# Azure Function App with an associated Application Insights component and Storage Account. Application Insights is used to monitor the Azure Function. The Storage Account is used to store the function code and logs.

Step 1: Create Azure Function App

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $StorageAccountName,
    [Parameter(Mandatory = $false)]
    [String]
    $StorageAccountType = "Standard_LRS",
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String]
    $Location
)

$RESOURCE_GROUP_NAME = $ResourceGroupName
$LOCATION = $Location

$FUNC_APP_NAME = $FunctionAppName
$FUNC_STORAGE_ACCOUNT = $StorageAccountName
$FUNC_STORAGE_ACCOUNT_TYPE = $StorageAccountType

# -------------------------------------------------------
# Provision Resource Group
# -------------------------------------------------------
az group create `
    --name $RESOURCE_GROUP_NAME `
    --location $LOCATION

# -------------------------------------------------------
# Provision Storage Account
# -------------------------------------------------------
az storage account create `
    --name $FUNC_STORAGE_ACCOUNT `
    --resource-group $RESOURCE_GROUP_NAME `
    --location $LOCATION `
    --sku $FUNC_STORAGE_ACCOUNT_TYPE

# -------------------------------------------------------
# Provision Function App
# -------------------------------------------------------
az functionapp create `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --storage-account $FUNC_STORAGE_ACCOUNT `
    --consumption-plan-location $LOCATION `
    --runtime dotnet `
    --assign-identity [system] `
    --functions-version 4 `
    --os-type Windows `
    --https-only true

Step 2: Assign Permissions to Azure Function

In order for the function to call Key Vault it needs the Key Vault Secrets User role assigned. To do this we need to get the Azure Function's Object ID. The following script will get the Azure Function's Object ID and assign the Key Vault Secrets User role to the Azure Function.

Note: The script is designed to accept an array of Roles in case you need to assign more roles in the future.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String[]]
    $RoleAssignments
)

# -------------------------------------------------------
# Define Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$FUNC_APP_NAME = $FunctionAppName

# -------------------------------------------------------
# Retrieve Resource Group ID for use as Scope
# -------------------------------------------------------
$RESOURCE_GROUP_ID = az group show `
    --name $RESOURCE_GROUP_NAME `
    --query id

# -------------------------------------------------------
# Assign Role to Function App
# -------------------------------------------------------
$FUNC_ID = az functionapp show `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --query id

$PRINCIPAL_ID = az functionapp identity show `
    --ids $FUNC_ID `
    --query principalId

foreach ($role in $RoleAssignments) {
    az role assignment create `
        --assignee $PRINCIPAL_ID `
        --role $role `
        --scope $RESOURCE_GROUP_ID
}

Step 3: Set Azure Function App Settings

Recall in the previous article we set our environment variables in the local.settings.json file. In this step we will set the applicaiton settings, which populate our environment variables, in the Azure Function App. The following script will set the environment variables in the Azure Function App.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName,
    [Parameter(Mandatory = $true)]
    [String[]]
    $AppSettings
)

# -------------------------------------------------------
# Define Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$FUNC_APP_NAME = $FunctionAppName
$APP_SETTINGS = $AppSettings

# -------------------------------------------------------
# Assign Azure Function App Settings
# -------------------------------------------------------
az functionapp config appsettings set `
    --resource-group $RESOURCE_GROUP_NAME `
    --name $FUNC_APP_NAME `
    --settings $APP_SETTINGS

Thats all that needs to be done to the function app. Next we need to configure the Azure SQL Database to allow our Azure Function to connect to it.

Configure Azure SQL Server

For the purposes of this article we will be assigning the Azure Function's Identity to the server admin on the SQL Database. This is not recommended for production environments. For production environments you should create a SQL Server User and assign the Azure Function's Identity to that user. However I wanted to keep this article simple and focused on the function deployment.

Step 1: Create SQL Server Firewall Rule for Azure Services

Similar to our Admin approach I will be opening the firewall to allow all Azure IP addresses, I do this by assigning the IP start and end addresses to 0.0.0.0 this is the simplest approach for our purposes.

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $SqlServerName
)

# -------------------------------------------------------
# Variables
# -------------------------------------------------------
$RESOURCE_GROUP_NAME = $ResourceGroupName
$SQL_SERVER_NAME = $SqlServerName

$START_IP = "0.0.0.0" # Allow all Azure IP addresses
$END_IP = "0.0.0.0" # Allow all Azure IP addresses

# -------------------------------------------------------
# Configure a server-based firewall rule
# -------------------------------------------------------
az sql server firewall-rule create `
    --resource-group $RESOURCE_GROUP_NAME `
    --server $SQL_SERVER_NAME `
    --name AzureServicesRule `
    --start-ip-address $START_IP `
    --end-ip-address $END_IP

Step 2: Configure Server Admin

When I first created the SQL Server I set it to only allow Active Directory authentication, and set the current user as the admin. This means I could access the SQL Server using my Azure AD credentials. If I assign only the function to the Server Admin role I won't be able to access the server with my own credentials. The solution is to create an Active Directory Group and assign that group to the Server Admin role. Then I only need to add my user and the function identity to the group; the script for this is below:

Param(
    [Parameter(Mandatory = $true)]
    [String]
    $ResourceGroupName,
    [Parameter(Mandatory = $true)]
    [String]
    $SqlServerName,
    [Parameter(Mandatory = $true)]
    [String]
    $FunctionAppName
)

# -------------------------------------------------------
# Define Variables
# -------------------------------------------------------
$SQL_GROUP_NAME = "${SqlServerName}-admins"

# -------------------------------------------------------
# Create AD Group
# -------------------------------------------------------
$GROUP_OBJECT_ID = az ad group create `
    --display-name $SQL_GROUP_NAME `
    --mail-nickname $SQL_GROUP_NAME `
    --query id `
    --output tsv

# -------------------------------------------------------
# Assign AD Group to SQL Server as Admins
# -------------------------------------------------------
az sql server ad-admin create `
    --resource-group $ResourceGroupName `
    --server $SqlServerName `
    --display-name $SQL_GROUP_NAME `
    --object-id $GROUP_OBJECT_ID

# -------------------------------------------------------
# Add Current Logged in User to AD Group
# -------------------------------------------------------
$CURRENT_USER_NAME_OBJECT_ID = az ad signed-in-user show `
    --query id `
    --output tsv

az ad group member add `
    --group $GROUP_OBJECT_ID `
    --member-id $CURRENT_USER_NAME_OBJECT_ID

# -------------------------------------------------------
# Add Function App Identity to AD Group
# -------------------------------------------------------
$FUNCTION_PRINCIPAL_ID = az functionapp identity show `
    --resource-group $ResourceGroupName `
    --name $FunctionAppName `
    --query "principalId" `
    --output tsv

az ad group member add `
    --group $GROUP_OBJECT_ID `
    --member-id $FUNCTION_PRINCIPAL_ID

Deploy and Test Function App

To deploy the function app to Azure we will use the azure-function-core-tools command line tool. If you followed along with the previous article this should already be installed. Because azure function names need to be unique accross Azure you only need to specify the function name when deploying. Simply navigate to the directory where your function app project files are located and run the following command:

func azure functionapp publish <Your Azure Function App Name>

Once the deploy is complete I encourage you to test the function in Azure. You may notice it's still a little slow, but now that it is in Azure we can focus on optimization of our Architecture and Code. In order to optimize we need to observe our application live and understand where the bottle necks are. This will be the topic of the next article in this series.

Blog