Login Failed for user '<token-identified principal>' for Azure Active Directory Admin

[MrGalvan]

Hello,

 I am having an issue where I am unable to connect to my Azure SQL database instance w/ my user that is the Active directory admin over the instance, along w/ the databases within that instance. I have been able to connect to this instance in the past with Management Studio. Now all of the sudden, I am unable to connect to any of my azure databases with this specific user. Non-admin accounts I can get into the instance and database, but my Active Directory Admin account is unable to log in. 

The error I am getting is: Login Failed for user '<token-identified principal>'

There is not a lot (any) help on this issue and I'm hoping someone knows why and can help. 

I've tried changing the default db to one of the other dbs in this instance w/ no success. 

I am using Active Directory - Integrated Authentication and have also tried Active Directory - Password and Active Directory - Universal with MFA Support.

Like I said, usually I will connect to these databases w/ no issue using Active Directory - Integrated. 

Not all the sudden it's throwing this error. 

I checked in my Azure environment and my user is still listed as the Active Directory Admin of this Instance/DB.

[#2]

Hi @MrGalvan, 

I also had this problem, my sql instance is hosted on Azure and I log in with my AD account, but I suddenly lost access. I had to update my credentials in AD Azure only to get access again. That done, everything went back to normal. Have you tried to update your credentials? 

Regards,

Marcos Guarnier

[#3]

Thanks @mguarnier - That's exactly what we ended up doing to resolve it. 

Thank you,

Marty Galvan

[#4]

Found the issue and have resolved it. 

What happened is the AD group that was assigned as the Active Directory Admin was dropped and created w/ the same name. However, the old group's Object Id wasn't the same as the newly created AD group's Object Id. 

Only found this out by looking at the Creation Date of the Active Directory Group. Which shows it was a recent creation date. A time frame within where the issue started happening.

So, it appears that the token assigned to the old group was still saved, but that Object Id no logger existed since the group was re-created w/ a new Object Id. 

Remedy: Just dropped AD admin and re-added it and now we're back in business. 

[#5]

@mguarnier Hello, could you let me know what you mean by 'update your credentials' - I have one person that seems to intermittently be able to connect via data studio and one that cannot connect at all - very confusing - they are trying to connect to an apollo DB within Azure.

[#6]

@Lanorix, Hi, have you tried to delete your AD account and create again? It can solve your problem. Before doing this operation, access your account in AD and make an update, for example: change your password and try the access again and if that doesn't resolve, delete the account and create a new one.