Overview
This guide is to help you troubleshoot Azure SQL connectivity and authentication issues on Linux App Services.
Note: At the time of writing this cannot be executed in Linux Function Apps, since we don't have access to the environmental variables needed to run the commands.
On Windows App Services you can performance such connectivity and authentication troubleshooting by running MSI Validator, but on Linux App Services we need to leverage the WebSSH functionality to troubleshoot.
So, to address below you can find a list of commands to test connectivity and access to Azure SQL Server Database on Linux App Services.
Connectivity troubleshooting steps
We would need to follow the 4 steps mentioned below to test the connectivity:
1. WebSSH into your Web App:
Browse to the Kudu site of the App Service and click on WebSSH.
Note: If you are using a custom Docker Image and do not have SSH (Secure Shell) enabled, you need to first follow these steps here before going forward with the next steps.
2. Install sqlcmd:
a. Ubuntu/Debian image
Run the following commands one by one.
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | tee /etc/apt/sources.list.d/msprod.list
apt-get update
apt-get install mssql-tools unixodbc-dev
apt-get update
apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
|
b. Alpine image
Run the following commands one by one.
apk add curl
apk --no-cache add curl gnupg
curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.10...
curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.10...
curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.10...
curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.10...
curl https://packages.microsoft.com/keys/microsoft.asc | gpg --import -
gpg --verify msodbcsql17_17.10.1.1-1_amd64.sig msodbcsql17_17.10.1.1-1_amd64.apk
gpg --verify mssql-tools_17.10.1.1-1_amd64.sig mssql-tools_17.10.1.1-1_amd64.apk
apk add --allow-untrusted msodbcsql17_17.10.1.1-1_amd64.apk
apk add --allow-untrusted mssql-tools_17.10.1.1-1_amd64.apk
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
|
3. Acquire an access token:
a. If you are using system-assigned manage identity:
The below command assumes that system assigned managed identity is already enabled, since the environmental variables IDENTITY_ENDPOINT and IDENTITY_HEADER should be injected to the container automatically by the platform.
Option 1 (works on most blessed images. For Alpine, follow option 2):
accessToken=$(curl --location --request GET ''"$IDENTITY_ENDPOINT"'?resource=https://database.windows.net&api-version=2019-08-01' --header 'X-IDENTITY-HEADER: '"$IDENTITY_HEADER"'' | grep -Po '"access_token":"\K[^"]*' )
To validate you have acquired a token you can do the below: echo $accessToken
|
Option 2 (For custom container images the grep command might not work so another option is to install and use jq):
apt-get install jq (for Debian) |OR| $ apk add jq (for Alpine) |OR| yum install jq (for centOS)
accessToken=$(curl --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' )
To validate you have acquired a token you can do the below: echo $accessToken
|
b. If you are using a User assigned managed identity:
For user assigned managed identity you need to first get the client ID and add it to the request by replacing the "XXXXXXX-XXXXXX-XXXXXXXXX" with the client ID on the below commands.
Option 1 (works on most blessed images. For Alpine, follow option 2):
accessToken=$(curl --location --request GET ''"$IDENTITY_ENDPOINT"'?resource=https://database.windows.net&api-version=2019-08-01&client_id=XXXXXXX-XXXXXX-XXXXXXXXX' --header 'X-IDENTITY-HEADER: '"$IDENTITY_HEADER"'' | grep -Po '"access_token":"\K[^"]*' )
To validate you have acquired a token you do the below: echo $accessToken
|
Option 2 (For custom container images the grep command might not work so another option is to install and use jq):
apt-get install jq (for Debian) |OR| $ apk add jq (for Alpine) |OR| yum install jq (for centOS)
accessToken=$(curl --location --request GET ''"$IDENTITY_ENDPOINT"'?resource=https://database.windows.net&api-version=2019-08-01&client_id=XXXXXXX-XXXXXX-XXXXXXXXX' --header 'X-IDENTITY-HEADER: '"$IDENTITY_HEADER"'' | jq --raw-output '.access_token' )
To validate you have acquired a token you do the below: echo $accessToken
|
Reference Screenshot:
4. Write the token to a file and test connectivity using sqlcmd
(Tokenfile will be created in the same directory from where we execute the commands.)
Run the following commands one by one.
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"
|
Successful execution result below:
References:
Connecting with sqlcmd - ODBC Driver for SQL Server | Microsoft Learn