Active Directory(AD) Authentication in Azure Sql not working

Question

I am trying to get Azure AD Authentication working against my Azure SQL Database.

I created a PaaS database and it's associated PaaS Sql Server.
I assigned MY Azure AD account as "Active Directory admin" of the "PaaS Sql Server".

Next, I logged in to SSMS using MY domain account to create the user:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER; 
GO
sp_addrolemember db_datareader,  [[email protected]];  
GO
sp_addrolemember
db_datawriter,  [[email protected]];  
GO

When I attempt to login with the [email protected] account , I get back:

   Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient Data Provider)

    Server Name: zzzzz.database.windows.net
    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536

What am I missing ?

Weird observation, if I intentionally use the wrong password I get back this error: AADSTS70002: Error validating credentials. AADSTS50126: Invalid username or password, that tells the AD is somehow working, but something is broken in the overall process.

Another weird observation: If I add a valid AD account, it succeeds

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

If I generate a bad AD account

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

I get back:

Principal '[email protected]' could not be found or this principal type is not supported.

jlo-gmail jlo-gmail · Accepted Answer · 2018-06-30T21:45:29

From a "simialr post"

The Anonymous Logon error occurs when you haven't specified the database you want to connect to. Simply select "options" on the SSMS login screen and type in the database name you want to connect to. This is because your user is a contained user on the database it was created in. It does not exist on Master. – Greg Grater Mar 7 '17 at 1:23

This fixed my issue -- hours wasted!!!

Note: for ODBC conenctions the user must also be created in master

Active Directory(AD) Authentication in Azure Sql not working

1 Answers