This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 49%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETZ%3C/text%3E%3C/svg%3E)
Hi, I have an aks with workload identity enabled. I have a user assigned managed identity created for WI and this user is the aad admin for the sql server. I have a pod on the AKS trying to connect to the same db using different type of connection strings. (ADO, JDBC, ODBC, SQLALCHEMY PYODBC). The ADO and JDBC connections were successful using workload identity. But I got [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user '<token-identified principal>' for the ODBCs.
Could you help on identifying the issues? Thanks.
For ODBC connection:
using System.Data.Odbc;
var connectionString="SERVER=something.database.windows.net;DATABASE=dbname;DRIVER={ODBC Driver 18 for SQL Server};Authentication=ActiveDirectoryMsi;";
DbConnection connection;
connection = new OdbcConnection(connectionString);
connection.Open();
For PYODBC:
Reference: https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/mssql/pyodbc.py#L122
import sqlalchemy
from azure.identity import DefaultAzureCredential
SQL_COPT_SS_ACCESS_TOKEN = 1256
TOKEN_URL = "https://database.windows.net/"
connection_string="mssql+pyodbc://:@something.database.windows.net:1433/dbname?driver=ODBC+Driver+18+for+SQL+Server&authentication=ActiveDirectoryMsi"
engine = sqlalchemy.create_engine(connection_string)
azure_credentials = DefaultAzureCredential()
connection = engine.connect()
@sqlalchemy.event.listens_for(engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams):
cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "")
raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le") token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token)
cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}
Part of the dockerfile for the pod image
FROM registry.access.redhat.com/ubi8/ubi-minimal:8.8 as ubi
WORKDIR /bin
ENV ACCEPT_EULA=Y
RUN curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
RUN microdnf -y install unixODBC openssl libtool-ltdl dotnet-runtime-6.0 java-1.8.0-openjdk-headless python3 python3-pip unixODBC-devel gcc-c++ python3-devel krb5-workstation krb5-libs mssql-tools
RUN microdnf -y download msodbcsql18
RUN microdnf -y install msodbcsql18-*.x86_64
RUN microdnf clean all
RUN pip3 install sqlalchemy pyodbc setuptools-rust azure.identity
Hi,@Tianqi Zhang Welcome to Microsoft Q&A thanks for posting your question
If I understand correctly you are trying to connect to the SQL server using ODBC and PYODBC connection strings from a pod on AKS with workload identity enabled. user-assigned managed identity created for WI and this user is the AAD admin for the SQL server. You also mentioned that the ADO and JDBC connections were successful using workload identity, but you got a "Login failed for user ''" error for the ODBC connection.
The error message indicates that the authentication failed for the user. This could be due to several reasons, such as incorrect credentials, insufficient permissions
Have you verified that the connection string is correct? what platform you are running on
Regards
Geetha
Hi Geetha,
The ODBC one is also using workload identity, connection strings were included in my question. The workload identity setup in correct as the pod can connect to sql using ADO and JDBC connection strings. Also, as I mentioned, the user-assigned managed identity I used to setup workload identity is the AAD admin of the SQL server.
Since you are using a User Assigned Managed Identity, you will need to add UID=myObjectID. For more info.
server=Server;database=Database;UID=myObjectId;Authentication=ActiveDirectoryMsi;Encrypt=yes;
You mentioned that the Managed Identity serves as the AAD Admin for the SQL server. However, have you created a SQL user from this Managed Identity within the target database?
AAD Auth Error - Login failed for user '<token-identified principal>' - Microsoft Community Hub
If you're encountering an AAD Authentication Error with the message "Login failed for user '<token-identified principal>'," it might be beneficial to open a support case for further investigation.
Regards
Geetha
Hi @Tianqi Zhang ODBC driver not supporting AKS
please check this URL https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-384-odbc-driver-not-supporting-aks-workload/ba-p/3858209
you can check the workaround yo use AccessToken
please mark as answer if this help