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 [xxx@yyy.com] FROM EXTERNAL PROVIDER;
GO
sp_addrolemember db_datareader, [xxx@yyy.com];
GO
sp_addrolemember
db_datawriter, [xxx@yyy.com];
GO
When I attempt to login with the xxxxx@yyy.com 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 [xxx@yyy.com] FROM EXTERNAL PROVIDER;
If I generate a bad AD account
CREATE USER [xxxABC@yyy.com] FROM EXTERNAL PROVIDER;
I get back:
Principal 'xxxABC@yyy.com' could not be found or this principal type is not supported.