App Services passwordless SQL Database connection

App Service passwordless authentication to Azure SQL Database with Microsoft Entra authentication.

Infrastructure

Copy the .auto.tfvars template file:

cp infra/config/template.tfvars infra/.auto.tfvars

Create the VM SSH keys:

mkdir infra/keys
ssh-keygen -f infra/keys/temp_key
chmod 600 infra/keys/temp_key

Create the infrastructure:

terraform -chdir="infra" init
terraform -chdir="infra" apply -auto-approve

Note

App Service health checks will show as unhealthy until the app is deployed, as the health check path is configured to be at /healthz.

Permissions: App Service

Whe using System-Assigned managed identity, the configuration uses the App Service name as login.

SQL Server

Create the login from an external provider:

USE master
CREATE LOGIN [app-contoso-8hkgb] FROM EXTERNAL PROVIDER
GO

Check the server login:

SELECT name, type_desc, type, is_disabled 
FROM sys.server_principals
WHERE type_desc like 'external%'  

SQL Database

Create the database user associated with the external login:

CREATE USER [app-contoso-8hkgb] FROM LOGIN [app-contoso-8hkgb]
GO

Check the database user:

SELECT name, type_desc, type 
FROM sys.database_principals 
WHERE type_desc like 'external%'

Add the necessary permissions to the user:

ALTER ROLE db_datareader ADD MEMBER [app-contoso-8hkgb];
ALTER ROLE db_datawriter ADD MEMBER [app-contoso-8hkgb];
ALTER ROLE db_ddladmin ADD MEMBER [app-contoso-8hkgb];
GO

Deploy the application

Enter the application directory, then build and deploy the application:

bash build.sh
az webapp deploy -g rg-contoso-8hkgb -n app-contoso-8hkgb --type zip --src-path ./bin/webapi.zip

Once deployed, test the database connectivity:

curl <appservice>/api/icecream

Virtual Machine + Docker

Note

Steps for this configuration are detailed in this article. SQL Server authentication with service principle is used.

To create the Virtual Machine, change the control variable and apply.

enable_virtual_machine = true

Important

Repeat the steps below for the service principal. Steps also detailed in the App Services section to authorized the service principal authentication to the database.

-- master
CREATE LOGIN [docker-containers] FROM EXTERNAL PROVIDER
-- database
CREATE USER [docker-containers] FROM LOGIN [docker-containers]
ALTER ROLE db_datareader ADD MEMBER [docker-containers];
ALTER ROLE db_datawriter ADD MEMBER [docker-containers];
ALTER ROLE db_ddladmin ADD MEMBER [docker-containers];

Check if Docker and the Azure CLI installation was successful.

Build and push the application to the container registry:

export acr="crcontosojqanh"

Login to ACR using the Virtual Machine identity:

Tip

Make sure to add sudo to the login or other ACR commands will fail like in here.

sudo az login --identity
sudo az acr login --name crcontosojqanh

Run the container to make sure the Docker engine and ACR pull are working

sudo docker pull crcontosojqanh.azurecr.io/icecream:latest
sudo docker run -p 8080:8080 crcontosojqanh.azurecr.io/icecream:latest

Get the connectivity configuration:

mkdir /tmp/icecream-webapi

Copy the configuration from Key Vault:

az keyvault secret show -n docker-container-config --vault-name kv-contoso-jqanh --query "value" --output tsv

Save it locally:

nano /tmp/icecream-webapi/config.json

Run the container:

# Run from the configuration directory
sudo docker run -p 8080:8080 -v "$(pwd):/app" -e CONFIG_FILE=/app/config.json crcontosojqanh.azurecr.io/icecream:latest

If necessary, here's a command to create/reset secrets:

# Identifier uri, application id, or object id
az ad app credential reset --id 00000000-0000-0000-0000-000000000000 --append

Database-level roles

When creating database users, SQL offers native database-level roles. Commands below are based off of this article.

Run it from the master database:

-- create SQL login in master database
CREATE LOGIN [USERNAME]
WITH PASSWORD = '*****';

Now, from the application database:

-- add database user for login [USERNAME]
CREATE USER [USERNAME]
FROM LOGIN [USERNAME]
WITH DEFAULT_SCHEMA=dbo;

Assign roles:

-- add user to database role(s)
ALTER ROLE db_ddladmin ADD MEMBER [USERNAME];
ALTER ROLE db_datawriter ADD MEMBER [USERNAME];
ALTER ROLE db_datareader ADD MEMBER [USERNAME];

User is ready. It is necessary to inform the database name during authentication.

Clean-up

Delete the Azure resources:

terraform destroy -auto-approve

Reference

• Create and utilize Microsoft Entra server logins
• Connect to Azure databases from App Service without secrets using a managed identity
• Setup a Microsoft Azure SQL Database Service Principal