SQLServerException: Login failed for user token-identified principal

Question

I am connecting to Azure SQL database using managed identity but facing this error, what else am I missing there guys?

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ''

• Java 17
• SQL Driver 9.4.1.jre8

Connection string:

jdbc:sqlserver://host:1433;encrypt=true;databaseName=simple;encrypt=true;trustServerCertificate=false;loginTimeout=30;hostNameInCertificate=*.database.windows.net;authentication=ActiveDirectoryMSI;msiClientId=[Client id of the managed Identity myinstance];

I have enabled Azure AD for Azure SQL server, turn on System assigned for VM. In User assigned tab, I add my managed Identity.

In Azure SQL, I've added users with the name of my Virtual Machine (testMSSQL) and the name of the managed Identity (myinstance) using this statement:

CREATE USER testMSSQL FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER testMSSQL;

CREATE USER myinstance FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER myinstance;

Answer 1

Please make sure the Azure Active Directory user is created on the Azure SQL Database, because sometimes by default we connect to the master database when we use tools like SSMS to connect to Azure SQL. In addition, please make sure you created the Azure SQL user by taking in consideration all the permissions and steps described on Microsoft documentation. Add the Azure SQL user to the following roles and try again: added to the db_datareader, db_datawriter and db_ddladmin.

Try the "Allow Azure services and resources to access this server” set as Yes in the firewall and virtual networks settings in your Azure SQL Database.