How to Use Managed Identity with Azure Function App?

January 27, 2020

With the announcement of Powershell support in Azure Functions, it has become easier for data professionals to use functions to manage cloud resources such as Azure SQL Database, Managed Instances. A common challenge when using functions is how to manage the credentials in function code for authenticating databases. Keeping the credentials secure is an important task. Ideally, the credentials should never appear in the code or in the source control.

Manged Identity can solve this problem as Azure SQL Database and Managed Instance both support Azure AD authentication. You can read mode about Managed Identity here.

In this article, I will show how to set up Azure Function App to use Managed Identity to authenticate functions against Azure SQL Database.

To follow along, create an Azure SQL Server, Azure SQL Database, and Function App. I am naming my Function App ‘sqlworldwidedemo’ with Runtime stack ‘PowerShell Core’.

Once you create a new Function App, create a system-assigned managed identity.

A system assigned managed identity enables Azure resources to authenticate to cloud services (e.g. Azure Key Vault) without storing credentials in code. Once enabled, all necessary permissions can be granted via Azure role-based-access-control. The lifecycle of this type of managed identity is tied to the lifecycle of this resource. Additionally, each resource (e.g. Virtual Machine) can only have one system assigned managed identity. Learn more about Managed identities.

You are ready to give the newly created managed identity, privilege to access Azure SQL Database. In this demo, I am making the user a member of the db_owner database role. It will vary in your case depending on the kind of task the functions will perform.

-- You need to be in the right context of user database as use databasename is not allowed with Azure SQL database

CREATE USER sqlworldwidedemo FROM EXTERNAL PROVIDER
GO
ALTER  ROLE db_owner ADD MEMBER sqlworldwidedemo
GO

For demo purposes, I wrote a function which will rebuild all indexes on a table. You can change the code and replace it for any other tasks. Line 22-25 is where I am getting an access token from managed identity and passing it to the connection on line 29.

# Rreplace the built in code for Azure function
# Input bindings are passed in via param block.

param($Timer)

# Get the current universal time in the default string format

$currentUTCtime = (Get-Date).ToUniversalTime()

# The 'IsPastDue' porperty is 'true' when the current function invocation is later than scheduled.

if ($Timer.IsPastDue) {
    Write-Host "PowerShell timer is running late!"
}

# This function app is using 'Managed Service Identity' to connect to the Azure SQL Database.
# Used help from following resources in setting up 'Managed Service Identity'
# https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi
# https://www.azurecorner.com/using-managed-service-identity-in-azure-functions-to-access-azure-sql-database/
# https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=powershell

$resourceURI = "https://database.windows.net/"
$tokenAuthURI = $env:MSI_ENDPOINT + "?resource=$resourceURI&api-version=2017-09-01"
$tokenResponse = Invoke-RestMethod -Method Get -Headers @{"Secret"="$env:MSI_SECRET"} -Uri $tokenAuthURI
$accessToken = $tokenResponse.access_token

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source =azuresqlservername.database.windows.net ; Initial Catalog = azuresqldatabasename"
$SqlConnection.AccessToken = $AccessToken
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText =  "ALTER INDEX ALL ON testRebuild REBUILD;"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) 

If you want to test the function, run below code into an Azure SQL Database. Check the index fragmentation before and after executing the function.

SET NOCOUNT ON
-- Create a table
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='testRebuild' AND XTYPE='U')
CREATE TABLE dbo.testRebuild 
(c1 int, c2 char (100), c3 int, c4 varchar(1000))
GO

-- Create clustered index
CREATE CLUSTERED INDEX ci ON testRebuild(c1)
GO

-- Inserting 1000 rows, takes about 8 seconds
DECLARE @i int
SELECT @i = 0
WHILE (@i < 1000)
BEGIN
INSERT INTO testRebuild VALUES (@i, 'hello', @i+10000, REPLICATE ('a', 100))
SET @i = @i + 1
END
GO

-- Inject fragmentation
UPDATE testrebuild 
SET    c4 = Replicate ('b', 1000) 

GO

-- Check the fragmentation
-- Run the function and check fragmentation again
SELECT avg_fragmentation_in_percent, 
       avg_fragment_size_in_pages, 
       fragment_count, 
       avg_page_space_used_in_percent 
FROM   sys.Dm_db_index_physical_stats (Db_id(), Object_id('testRebuild'), NULL, NULL,           'DETAILED') 
GO

1 reply on “How to Use Managed Identity with Azure Function App?”

Leave a Reply to Managed Identity with Azure Functions – Curated SQL Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.