Unable to determine why JWT access token not working with Azure SQL. Connection fails Login failed for user ''

dqmdev 1 Reputation point
2021-10-19T14:45:33.897+00:00

Unable to determine what is preventing a JDBC connection to Azure SQL using an accesstoken.

Scenario

Azure AD has two users defined, who are members of an Azure Group.
Both users and the group were added to Azure SQL database xxyyzz using the Active AD user admin associated to the Azure SQL DB.

CREATE USER [@xxyyzz.onmicrosoft.com] FROM EXTERNAL PROVIDER;
EXECUTE sp_addrolemember db_datareader, [
@xxyyzz.onmicrosoft.com];

Both AZ AD users can connect to the Azure SQL database xxyyzz using Azure Data Studio.

Application registered with AD that was granted permission to delegate access to Azure SQL.

Application able to obtain JWT access token from AD, which it passes to Azure SQL via SQL Server JDBC driver 9.4.0.
Connection fails Login failed for user '<token-identified principal>'

URL
jdbc:sqlserver://xxyyzz.database.windows.net:1433;database=xxyyzz;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Properties
accessToken=....

Both unique_name and upn in the decoded JWT reflect the Azure AD User principal name ******@xxyyzz.onmicrosoft.com

Azure SQL Database
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.