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.

# Replace 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' property 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 a 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

11 replies on “How to Use Managed Identity with Azure Function App?”

Can one also use the {ODBC Driver 17 for SQL Server} driver and just specify ActiveDirectoryMsi as the authentication method?

Hi Taiob,
Thanks for the excellent walkthrough.
There’s a typo on line 23 of the function, the ampersand got escaped. It should read:
$tokenAuthURI = $env:MSI_ENDPOINT + “?resource=$resourceURI&api-version=2017-09-01”

BTW, do you know how I can shorten the lifespan of the access token? In testing your code I found that I can reuse the same token after several hours. Since you accquire a token on every run, wouldn’t it be proper to set it to a very short period?
The documented procedure for this,
https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-configurable-token-lifetimes
doesn’t seem to apply here, as Get-AzureADApplication doesn’t list our Function App.

Hi Dan,
Thank you for reading the post. With the escaping, it appears to be a bug in the plugin. I will work on fixing it. Most likely need a filter.
I have not thought about shortening the lifespan of the token. I agree with what you are saying. If I can figure out, I will update the post.
Taiob

In the T-SQL line “CREATE USER sqlworldwidedemo …”, what does sqlworldwidedemo point to? I see multiple resources using that same name (azure storage, function app name), thus I’m not certain what I should be using for that value in my scenario. Thanks.

Can you let me know how to get this $env:MSI_ENDPOINT and $env:MSI_SECRET ?. I mean from where to get the value for these variable

Leave a Reply to Sunny 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.