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

user_12787694 0
Viewed 1495

BACKGROUND

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

  • test.user@company.com 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. (test.user@company.com)

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

  3. I have created a user in SSMS test.user@company.com with the following syntax:

     CREATE USER [test.user@company.com] FROM EXTERNAL PROVIDER;

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

     ALTER ROLE db_datareader ADD MEMBER [test.user@company.com]

  5. ISSUE: When I try to connect as test.user@company.com, the connection fails and I get this error:

    Login Failed for user test.user@company.com

    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

azure-active-directory azure-sql-database authentication sql-server
2 Answers

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.

user_127876940

It seems to be some kind of encoding problem. After almost giving up and using a different DB, I tried creating a new one, but without some special characters. And suddenly, it worked.

If you can't change your database's password, you can just edit the connection string in another editor and it works as well (Notepad, VS code, etc).

user_165053490
Related