Azure Active Directory Auth Broken - No known root cause

Question

I am building an ASP.NET MVC web application that relies on AAD auth. I used the standard process to add auth to the project (AAD Auth through VS) and it worked perfectly fine for the past two weeks.

Today, all of a sudden it is broken:

This occurs on ADALTokenCache initialization (standard AAD auth routine):

public ADALTokenCache(string signedInUserId)
{ 
    // associate the cache to the current user of the web app
    userId = signedInUserId;
    this.AfterAccess = AfterAccessNotification;
    this.BeforeAccess = BeforeAccessNotification;
    this.BeforeWrite = BeforeWriteNotification;
    // look up the entry in the database
    Cache = db.UserTokenCacheList.FirstOrDefault(c => c.webUserUniqueId == userId);
    // place the entry in memory
    this.Deserialize((Cache == null) ? null : MachineKey.Unprotect(Cache.cacheBits,"ADALCache"));
}

Seems strange and the root cause is unknown to me – there were no changes done to the app itself other than adding more views and controllers (nothing that should be messing with auth).

Clearing browser cookies does nothing. Manually setting the MachineKey doesn’t help either.

This repro-s both locally and on the remote server once the app is deployed.

Anyone encounter this before?

Seems odd to appear without any modifications to the config.

Answer 1

The solution to this was dropping the DB.

The cache is based on a local MDF file, and changing its name in Web.config:

<add name="DefaultConnection" 
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-Den-SomeNewName.mdf;Initial Catalog=aspnet-Den-SomeNewName;Integrated Security=True" 
providerName="System.Data.SqlClient" />

Answer 2

When I received this error it was because I had cached some bad log-in information and the authentication was struggling to retrieve it, causing an exception.

To solve this I dropped the DB from SSMS and then commented out the line:

<add name="DefaultConnection" 
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-Den-SomeNewName.mdf;Initial Catalog=aspnet-Den-SomeNewName;Integrated Security=True" 
providerName="System.Data.SqlClient" /> 

in Web.config.

Then right click on your solution and configure your Azure AD Authentication. Click finish to reconfigure it using the same setup as previously. Because you commented out the default connection it will automatically generate you a new string and create a new local database for your member information with a cleared cache.

The previous solution didn't work for me, but this did!

Answer 3

I had the same problem.

Clear the UserTokenCaches Table instead of dropping the entire database.

Answer 4

I believe there is a bug in the default implementation of the ADALTokenCache class that gets autogenerated by Visual Studio. As far as I can tell, there is no UNIQUE constraint on the WebUserUniqueId column, no .OrderByDesc(t=>t.LastWriteDate) in the call to .FirstOrDefault(c => c.WebUserUniqueId == userId) that retrieves the token from the Cache, and no purging of the cache that takes place. In other words, it's possible to authenticate multiple times against AAD and each time a new token can get written to the UserTokenCaches table in the database, but when the call to .FirstOrDefault() is made to retrieve the token with no sorting, it is not determinate which version gets retrieved, and with how SQL Server generally writes data, it is more likely that the oldest token gets retrieved from the table.

This bug could also result in an invalid_grant message (AADSTS70002) that states The refresh token has expired due to inactivity. The token was issued on ... and was inactive for ...

The fastest way to get back up and running is to purge the UserTokenCaches table in the database and have your users reauthenticate against Azure, or at least, run a DELETE UserTokenCaches WHERE LastWriteDate < ... to get rid of some of the oldest tokens.

The quick and dirty workaround for this is to purge the cache of the user's tokens in the method that sits in Startup.Auth.cs prior to the line of code that creates the AuthenticationContext and new ADALTokenCache(...) object.

db.UserTokenCacheList.RemoveRange(db.UserTokenCacheList.Where(t => t.webUserUniqueId == signedInUserID));
db.SaveChanges();

The default implementation of the UserTokenCaches table will likely have scalability issues as well with how it's constructed. The default type of webUserUniqueId is NVARCHAR(MAX) and cannot have a UNIQUE or index constraint placed on it. I would recommend changing the type to VARCHAR and of a length that holds the value correctly and to create a single UNIQUE CLUSTERED index on this column as the cacheBits column cannot be included in a small covering index. It may be optimal to limit the size of the cacheBits column as well, though I have no idea how large this or the webUserUniqueId column can grow. I also do not know how if the datatype change from NVARCHAR to VARCHAR may affect lookups from .Net through EF as I've seen autogenerated WHERE based searches promote parameters to NVARCHAR instead of VARCHAR resulting in an INDEX SCAN or TABLE SCAN operation instead of a SEEK operation. Rather than a call to .FirstOrDefault(...), I would hand optimize the call where db.UserTokenCacheList.FirstOrDefault(...) is used to ensure the correct index is used. (the following code is untested):

Cache = db.UserTokenCacheList.SqlQuery("SELECT * FROM UserTokenCacheList WHERE webUniqueUserId = @webUniqueUserId;", new System.Data.SqlClient.SqlParameter("@webUniqueUserId", System.Data.SqlDbType.VarChar({Value=signedInUserID}).FirstOrDefault();