0

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.

Improve this question

1 Answer 1

Reset to default
3

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.
Improve this answer
1
  • 1
    This did the trick. Now I can use "CREATE USER [newADuser@myuserhotmail.onmicrosoft.com] FROM EXTERNAL PROVIDER" with sqladmin account for contained db authorization. One thing to note: even if newADuser showed as created in Azure Portal, I had to wait couple minutes until CREATE USER script recognized it as an existing user.
    – Nicolas Hevia
    Feb 23, 2019 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.