Debugging a managed identity connection to Azure SQL in Azure App Service using containers
So you’ve been eager to deploy your containerized web application that you’ve been working on for a while, to Azure App Service. You follow the best security practices which means you’ve setup your connection to your Azure SQL database using a managed identity. Upon loading your application, you get a dreadful exception that you can’t connect to the database. You then wonder, is this a database permission problem or some another problem, such as the application cannot connect to the database using its managed identity. This problem is even tougher to diagnose in applications such as Custom Off the Shelf (COTS) that you don’t have the source code.
Rest assured, this happen to the best of us (yes, even me!). In this post, I will give you some techniques to help you determine if the problem is indeed a problem between the web app and the Azure SQL database, through the use of managed identities, without having to modify any code in your application, keeping the debugging strictly on the infrastructure side.
Using a Linux container
The steps required can be listed as follow:
- Create an image that
- has a dummy website (static page) hosted using NGINX or your web server of choice
- contains SSHD that is configured specifically for App Service
- contains sqlcmd (either installed through binaries or through the compiled binaries)
- Push your image to Azure Container Registry or your registry of choice that the App Service can consume
- Let the container and web app start
- Access the WebSSH option in the Kudu (Advanced Tools) where you should be able to have a shell
- Get an access token (AT) for database.windows.net using the Azure Instance Metadata Service (IMDS)
- Use that token as credentials using sqlcmd
Creating the container image
Using Docker, you can use the following Dockerfile to create a static website using NGINX, install and configure SSHD for App Service and lastly install sqlcmd.
You can find more information on SSHD in Linux containers for App Service in the documentation. A full article was also created by the Azure team and can be viewed here, but for simplicity I will go straight to the point with a more minimalist example, not using any specific programming languages (such as C# or java or python).
As described in the Azure team article, some files will be needed to create the image, In my case, there is 5:
- A sshd_config that will configure SSHD
- A entrypoint.sh file that will start NGINX along with SSHD
- A static HTML file that will be the index.html for NGINX
- A helper script to be able to connect to the database using sqlcmd
- The Dockerfile
sshd_config
As shown in the documentation, here’s a sshd_config file to configure OpenSSH. Create it along side your Dockerfile:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Port 2222 ListenAddress 0.0.0.0 LoginGraceTime 180 X11Forwarding yes Ciphers aes128-cbc,3des-cbc,aes256-cbc,aes128-ctr,aes192-ctr,aes256-ctr MACs hmac-sha1,hmac-sha1-96 StrictModes yes SyslogFacility DAEMON PasswordAuthentication yes PermitEmptyPasswords no PermitRootLogin yes Subsystem sftp internal-sftp |
This information can be important to share to your security teams.
Entrypoint shell script
Create the following entrypoint.sh along side your Dockerfile. Note the (optional) env vars line. As mentioned in the Azure blog post, by default with custom Docker images, when SSH’ing into a container, only a few certain environment variables may be seen when trying to use something like env or printenv.
To create the entrypoint script, you need to understand NGINX Dockerfile. Using the Debian flavor, you can see that the entrypoint is /docker-entrypoint.sh and it’s passing 3 parameters using the CMD instruction.
|
1 2 3 4 5 6 7 8 9 10 |
#!/bin/sh set -e # Get env vars in the Dockerfile to show up in the SSH session eval $(printenv | sed -n "s/^\([^=]\+\)=\(.*\)$/export \1=\2/p" | sed 's/"/\\\"/g' | sed '/=/s//="/' | sed 's/$/"/' >> /etc/profile) echo "Starting SSH ..." service ssh start ./docker-entrypoint.sh "nginx" "-g" "daemon off;" |
HTML index file
A simple HTML file can be created. Save it alongside your Dockerfile and name it index.html. Here you can be creative. If you feel adventurous, you can use Generative AI to create yourself a HTML static file.
Dockerfile
To create the image you will use in the web app, create a Dockerfile with the following content. Once saved, run the command to build your image. Note that there isn’t a native flavor for Debian as per the documentation, so I opted to use the go binary instead.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
FROM nginx:latest # install openssh, mssql-tools and tooling RUN apt-get update -y \ && apt-get install -y --no-install-recommends openssh-server jq bzip2 \ && apt-get clean -y RUN git_artifact_uri=$(curl -s 'https://api.github.com/repos/microsoft/go-sqlcmd/releases/latest' | jq -r '.assets[] | select(.name == "sqlcmd-linux-amd64.tar.bz2") | .browser_download_url') \ && echo "Downloading $git_artifact_uri" \ && curl -s -L -o /tmp/sqlcmd-linux-amd64.tar.bz2 $git_artifact_uri RUN mkdir -p /opt/mssql-tools/bin \ && tar -xvf /tmp/sqlcmd-linux-amd64.tar.bz2 -C /opt/mssql-tools/bin \ && chmod +x /opt/mssql-tools/bin/sqlcmd \ && ln -s /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd \ && rm /tmp/sqlcmd-linux-amd64.tar.bz2 COPY index.html /usr/share/nginx/html/ COPY entrypoint.sh / COPY helper.sh /home RUN echo "root:Docker!" | chpasswd \ && chmod u+x /entrypoint.sh COPY sshd_config /etc/ssh EXPOSE 80 2222 ENTRYPOINT ["/entrypoint.sh"] |
helper script
Here is a helper script that can be used to call all the necessary commands to connect to the database.
- The first parameter is the server name (from i.e. <servername>.database.windows.net)
- The second parameter is the database name
- The third parameter is the client id for the managed identity if you are using a user assigned identity
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
servername="$1" database="$2" client_id="$3" if [ -z "$client_id" ]; then # using System Identity accessToken=$(curl --silent --location --request GET ''"$IDENTITY_ENDPOINT"'?resource=https://database.windows.net&api-version=2019-08-01' --header 'X-IDENTITY-HEADER: '"$IDENTITY_HEADER"'' | jq --raw-output '.access_token' ) else # using User Identity accessToken=$(curl --silent --location --request GET ''"$IDENTITY_ENDPOINT"'?resource=https://database.windows.net&api-version=2019-08-01&client_id='"$client_id"'' --header 'X-IDENTITY-HEADER: '"$IDENTITY_HEADER"'' | grep -Po '"access_token":"\K[^"]*' ) fi echo $accessToken echo $accessToken | tr -d '\n' | iconv -f ascii -t UTF-16LE > tokenfile sqlcmd -S "$servername.database.windows.net" -d "$database" -G -P tokenfile -Q "SELECT @@servername" sqlcmd -S "$servername.database.windows.net" -d "$database" -G -P tokenfile -Q "SELECT TABLE_NAME FROM [$database].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'" |
Last steps…
Once you’ve built, pushed the container to your registry and deployed the container to your app service, you can select SSH in the app service pane and you will be connected to the WebSSH. You can then navigate to /home and call the helper shell script.
Using a Windows container
Similarly to the Linux container instructions, the steps required can be listed as follow:
- Create an image that
- has a dummy website (static page) hosted using IIS or your web server of choice
- contains sqlcmd through the compiled binaries
- Push your image to Azure Container Registry or your registry of choice that the App Service can consume
- Let the container and web app start
- Open up the Kudu (Advanced Tools) and navigate to the SSH tab. SSH doesn’t need to be installed in Windows image unlike in Linux based images. It is prebaked for you in the platform.
- Get an access token (AT) for database.windows.net using the Azure Instance Metadata Service (IMDS)
- Use that token as credentials using sqlcmd
It seems there is a problem with Docker when there are multiple networking adapters (Ethernet, Wi-Fi, etc.) present on the host.
You can fix this by following the post in the Docker issue here.
Dockerfile
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
# escape=` # see https://hub.docker.com/r/microsoft/windows-servercore-iis FROM mcr.microsoft.com/windows/servercore/iis:windowsservercore-ltsc2022 SHELL ["powershell", "-NoProfile -Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"] RUN Remove-Item -Recurse C:\inetpub\wwwroot\* RUN [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12; ` $items = Invoke-RestMethod -Uri 'https://api.github.com/repos/microsoft/go-sqlcmd/releases/latest'; ` $downloadLink = $items.assets | Where-Object { $_.name -like '*-windows-amd64.zip' } | Select-Object -ExpandProperty browser_download_url; ` $zipFile = Join-Path $env:TEMP 'sqlcmd-windows-amd64.zip'; ` Invoke-WebRequest -Uri $downloadLink -OutFile (Join-Path $env:TEMP 'sqlcmd-windows-amd64.zip'); ` New-Item -Type Directory 'C:\sqlcmd'; ` Expand-Archive -Path $zipFile -DestinationPath 'C:\sqlcmd'; RUN setx /M PATH $($Env:PATH + ';C:\sqlcmd') WORKDIR / COPY Helper.ps1 . WORKDIR /inetpub/wwwroot COPY index.html . |
Helper script
The helper script is the PowerShell equivalent to the Linux helper script above. Use it with the same parameters, that is, ServerName, Database and optionally a ClientId if you are using a user assigned identity.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
[CmdletBinding()] param ( [Parameter(Mandatory = $True)] [string] $ServerName, [Parameter(Mandatory = $True)] [String] $Database, [string] $ClientId ) $ErrorActionPreference = "Stop" # Function to get access token based on system or user identity function Get-AccessToken { param ( [string] $ClientId ) # Fetch the identity endpoint and header from the environment $identityEndpoint = $env:IDENTITY_ENDPOINT $identityHeader = $env:IDENTITY_HEADER $url = "$($identityEndpoint)?resource=https://database.windows.net&api-version=2019-08-01" # Using User Identity if (![String]::IsNullOrWhiteSpace($ClientId)) { $url += "&client_id=$ClientId" } $headers = @{ 'X-IDENTITY-HEADER' = $identityHeader } # Send HTTP GET request and extract access token $response = Invoke-RestMethod -Uri $url -Headers $headers return $response.access_token } # Get access token $accessToken = Get-AccessToken -ClientId $ClientId # Output token (optional) Write-Output "Access Token: $accessToken" # Write token to file in UTF-16LE format $tokenFile = "tokenfile" [System.Text.Encoding]::Unicode.GetBytes($accessToken) | Out-File -FilePath $tokenFile -Encoding utf8 -Force # Execute SQL queries using sqlcmd $server = "$ServerName.database.windows.net" # Query 1: Select @@servername # see https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connecting-with-sqlcmd?view=sql-server-ver16 for parameters Write-Output "Getting the server name from the database" & sqlcmd -S $server -d $Database -G -P $tokenFile -Q "SELECT @@servername" # Query 2: Get base tables from INFORMATION_SCHEMA.TABLES Write-Output "Listing the tables in the database" & sqlcmd -S $server -d $Database -G -P $tokenFile -Q "SELECT TABLE_NAME FROM [$Database].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'" |
Conclusion
With the above, you can keep those images handy in your Container Registry to diagnose managed identity connection problems. Hope this can accelerate your debugging and help you eliminate credentials when connecting to your databases, making your processes more secure.
