Connect Azure PowerShell Function to Azure SQL Database

I have been working on a big script for the last few weeks in my spare time, which is why there hasn’t been any recent updates. Its up to about 50 hours of work now and will probably take another couple of weeks to finish off. I thought I would just write a quick blog on some issues I ran into when trying to connect an Azure PowerShell Function to a SQL Database in Azure during my actual job.

I ran into an issue trying to figure out what the environment variable the connection string gets set in. It turns out based on the type of database you set Azure prepends to the name.

The different values it uses are:

• MySQL: MYSQLCONNSTR_
• SQLServer: SQLCONNSTR_
• SQLAzure: SQLAZURECONNSTR_
• Custom Connection : CUSTOMCONNSTR_

So in my case to get the connection string for an Azure SQL database I needed to use:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $env:SQLAZURECONNSTR_MyConnectionString

Where MyConnectionString is the actual name of the connection string in the settings.

After getting that to work I realised there was a far better way of connecting using the manged identity of the function app without using connection strings.

I followed this guide but was having problems getting the access token:

https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql

It turns out yet again you need to use another hidden environment variable. Instead of calling

$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata="true"}

You need to use $env:IDENTITY_ENDPOINT in place of the Uri:

$resourceURI = "https://database.windows.net"
$tokenAuthURI = $env:IDENTITY_ENDPOINT + "?resource=$resourceURI&api-version=2019-08-01"
$tokenResponse = Invoke-RestMethod -Method Get -Headers @{"X-IDENTITY-HEADER"="$env:IDENTITY_HEADER"} -Uri $tokenAuthURI
$accessToken = $tokenResponse.access_token