Posted August 4, 20231 yr 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.1.1-1_amd64.apk curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.10.1.1-1_amd64.apk curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.10.1.1-1_amd64.sig curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.10.1.1-1_amd64.sig 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 Continue reading...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.