Sql-server – “Active Directory – Password” login in SSMS for Azure AD – Password expired

active-directoryazure-sql-databasesql serverssms

I'm trying to connect to an Azure SQL DB through SSMS (v17.9.1), using an Azure AD user, that is an Active Directory Admin for my SQL Server that hosts my Azure SQL Database.

I'm able to login with my Server Admin "azureuser", using SQL Server Authentication. But now I'm trying to use an Azure AD user (by password) to login through SSMS. I've created this user ("sqladmin") into Azure Active Directory:

enter image description here

And then added this AD user as an AD admin to my SQL Server:

enter image description here

Now, I don't know if this is the correct way to do this, but I'm resetting [sqladmin] password to know it, and then trying to connect with SSMS, but I keep getting the password expired, even when Azure Portal states that the password will be valid until the next login (where it'll be required to be changed):

enter image description here

Am I doing something wrong? I can't create EXTERNAL PROVIDER logins on this server either as it requires to be connected with an AD user (executing with azureuser – server admin):

CREATE USER [sqladmin@myuserhotmail.onmicrosoft.com] FROM EXTERNAL PROVIDER

Msg 33159, Level 16, State 1, Line 5
Principal 'sqladmin@myuserhotmail.onmicrosoft.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

Best Answer

When you reset a password in Azure AD, it automatically forces the user to change the PW on the next login. If you immediately attempt to login to Azure SQL it will give you an error that the PW is expired.

  • Log into the Azure Portal as sqladmin@myuserhotmail.onmicrosoft.com, set a new password and this will resolve your issue.