Can't authenticate with sqlcmd -G running in Azure Container App Job - Stack Overflow

I have an Azure Container App Job running a shell script, one of the commands in said script is sqlcmd. Currently I can run the command successfully if I use user name/password authentication but I would like to use a System Assigned Managed Identity assigned to the container app to authenticate. When I try to run sqlcmd with the -G option I get the following error/logs.

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated'.
Error code 0xA190; state 41360
Error acquiring Kerberos credentials
Mechanism status: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_0)

Seems odd to have user empty. I do have the system assigned managed identity set up with the 'SQL Db Contributor' role, and use it with other commands successfully.

Giving it a goog, I'm not seeing anything specific to this issue.

I have an Azure Container App Job running a shell script, one of the commands in said script is sqlcmd. Currently I can run the command successfully if I use user name/password authentication but I would like to use a System Assigned Managed Identity assigned to the container app to authenticate. When I try to run sqlcmd with the -G option I get the following error/logs.

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated'.
Error code 0xA190; state 41360
Error acquiring Kerberos credentials
Mechanism status: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_0)

Seems odd to have user empty. I do have the system assigned managed identity set up with the 'SQL Db Contributor' role, and use it with other commands successfully.

Giving it a goog, I'm not seeing anything specific to this issue.

• azure
• containers
• sqlcmd
• azure-container-apps
• azure-container-app-jobs

Share Improve this question Follow asked Jan 2 at 18:07 hudsonschudsonsc 13311 gold badge11 silver badge66 bronze badges 1

• When you invoke sqlcmd -G on a Linux container, the driver attempts “Integrated Authentication,” which effectively tries to obtain it from Kerberos —something that typically requires domain-join or other Kerberos configuration. This is not how Azure Managed Identity is meant to work on Linux. – Arko Commented Jan 3 at 7:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As mentioned in comment section, your error indicates that sqlcmd is attempting to use *Active Directory Integrated Authentication, which relies on Kerberos credentials. However, Managed Identity authentication in Azure does not use Kerberos. Instead, it uses AAD access tokens. That is why the problem arises because sqlcmd is not configured to use the token acquired from the Managed Identity.

To fix this, enable the System Assigned Managed Identity for the Azure Container App Job.

az containerapp job identity assign \
    --name sqlcmd-job \
    --resource-group arkorg \
    --system-assigned

Assign the SQL DB Contributor role to the Managed Identity for the SQL server.

az role assignment create \
    --assignee <principal-id> \
    --role "SQL DB Contributor" \
    --scope /subscriptions/abcdefghijk/resourceGroups/arkorg/providers/Microsoft.Sql/servers/sql-arko

verify the same

az role assignment list \
    --assignee <principal-id> \
    --scope /subscriptions/abcdefghijk/resourceGroups/arkorg/providers/Microsoft.Sql/servers/sql-arko

Use the managed identity to acquire an AAD token and pass it to sqlcmd for authentication i.e. update your script to use AAD token for example-

#!/bin/bash

# Acquire AAD token using Managed Identity
ACCESS_TOKEN=$(curl -s "http://169.254.169.254/metadata/identity/oauth2/token?resource=https://database.windows.net/&api-version=2019-08-01" -H "Metadata: true" | jq -r '.access_token')

# Run sqlcmd with the token
sqlcmd -S sql-arko.database.windows.net \
       -d database-arko \
       -G -C -N \
       -Q "SELECT GETDATE()" \
       -v AZURE_SQL_TOKEN=$ACCESS_TOKEN

Done. Now when you test the job it runs fine

az containerapp job start \
    --name sqlcmd-job \
    --resource-group arkorg

Found few MS Docs:

• Authenticate with Microsoft Entra ID in sqlcmd
• Connect to Azure SQL resource with Microsoft Entra authentication