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:
And then added this AD user as an AD admin to my SQL Server:
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):
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.