Azure AD user unable to connect to Azure SQL Error: 18456,State:1,Class:14

1
votes

BACKGROUND

The user is defined in Azure AD and in Azure SQL.

  • [email protected] is defined and active in Azure AD. I’m able to log in with this user in Azure portal.
  • I have created a contained database user with the clause "from external provider" in the Azure SQL database and added the user to the db_datareader system role. The commands commits without error. I have compared the Asset ID in Azure AD and the SID in database and they match. I have tried with different users.
  • I am able to login as an AD User assigned to the SQL servers assigned "Active Directory Admin"
  • SQL Server users also login without any issue

Login test in SSMS

I have set default database to the specific database I’m trying to log into (Options-Connect to database).

I have tried with different Authentication modes:

  • “Azure Active Directory – Universal with MFA” and
  • “Azure Active Directory – Password”

When I try to login with SSMS I get

Error: 18456, state 1, class 14.

When I query [sys].[event_log] it returns

[event_systype]= 4, [event_subtype_desc] = login_failed_for_user.

PROCESS FOLLOWED

  1. I have a user defined in Azure AD. ([email protected])

  2. In my Azure SQL database, I have set an Active Directory administrator.

  3. I have created a user in SSMS [email protected] with the following syntax:

     CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

  4. I have assigned the user to the db_datareader role with the following syntax:

     ALTER ROLE db_datareader ADD MEMBER [[email protected]]

  5. ISSUE: When I try to connect as [email protected], the connection fails and I get this error:

    Login Failed for user [email protected]

    Details of error contains:

    Error Number: 18456, State: 1, Class: 14

I have tried to find an answer on docs.microsoft.com, googled my ass off and found some useful resources on this, but none that actually helps me with pinpointing what to do to resolve the issue. If anybody have an Idea of what I'm doing wrong, ref "PROCESS FOLLOWED" I'd be forever grateful :)

enter image description here

1
sql-serverauthenticationazure-active-directoryazure-sql-database
Does GRANT CONNECT TO [email protected]; work? - Joseph Xu
@JosephXu Thanks, but no, it did not work. - Atle Røen
@AtleRøen Did you create the user [email protected] with the AD admin account? - Leon Yue
@LeonYue yes, I did - Atle Røen
From the Azure CLI (could be cloud shell) ensure you can generate an access token for that account. eg az login then az account get-access-token --resource 'https://database.windows.net/'. If you can, then use that AccessToken with a System.Data.SqlClient.SqlConnection, which is what SSMS will try. - David Browne - Microsoft

1 Answers

0
votes

I solved it by a workaround, setting up a new database. I did not find the cause of the problem, but it must be related to database setup "DTU"-"Basic" or some setting that I have managed to set without remembering. Anyways, my process works when I set up a new database with "DTU"-"Standard" Configuration.