Why does my Azure AD Authentication display "The system cannot find the file specified" error at login?

My MVC web application uses Azure AD Authentication to let uses sign in to a secured area, it has two areas called Area1 and Area2. To setup Azure Active Directory Authentication I chose the Configure Azure AD Authentication from my project menu and followed the wizard. This prompted me for a few things and since I'd never set it up before I allowed it to hook the AD to my client by itself.

The Azure Active Directory Authentication wizard adds and [Authorize] tag to every controller in my application (both areas) however, only Area1 needs to be secure Area2 is public. I removed the [Authorize] tag from the controllers in Area2 so that it knows not to request authentication before serving the pages up.

At this stage the [Authorize] tags only exist in Area1 controllers since those are the ones that are supposed to be secure.

When I run my application in Azure I can get the signin page just find but when I click on sign in I get the following error message.

The system cannot find the file specified

[Win32Exception (0x80004005): The system cannot find the file specified]

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 52 - Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.)]
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) +821
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +332
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +38
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +699
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +89
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +426
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +78
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +191
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +154
   System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +21
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +90
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +217
   System.Data.SqlClient.SqlConnection.Open() +96
   System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<Open>b__36(DbConnection t, DbConnectionInterceptionContext c) +10
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +72
   System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) +359
   System.Data.Entity.SqlServer.<>c__DisplayClass33.<UsingConnection>b__32() +426
   System.Data.Entity.SqlServer.<>c__DisplayClass1.<Execute>b__0() +10
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +189
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) +77
   System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action`1 act) +174
   System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act) +556
   System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +86
   System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +164
   System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +76
   System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase() +134
   System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) +119
   System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +142
   System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +78
   System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext) +89
   System.Data.Entity.Internal.InternalContext.CreateDatabase(ObjectContext objectContext, DatabaseExistenceState existenceState) +116
   System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +218
   System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context) +151
   System.Data.Entity.Internal.<>c__DisplayClassf`1.<CreateInitializationAction>b__e() +76
   System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +60
   System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +357
   System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c) +7
   System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input) +110
   System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action) +198
   System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() +73
   System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +28
   System.Data.Entity.Internal.Linq.InternalSet`1.Initialize() +53
   System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext() +15
   System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider() +38
   System.Linq.Queryable.FirstOrDefault(IQueryable`1 source, Expression`1 predicate) +83
   MyProject.Models.ADALTokenCache..ctor(String signedInUserId) in C:\Users\John\Desktop\Development\MyProject\Models\AdalTokenCache.cs:26
   MyProject.Startup.<ConfigureAuth>b__7_0(AuthorizationCodeReceivedNotification context) in C:\Users\John\Desktop\Development\MyProject\App_Start\Startup.Auth.cs:54
   Microsoft.Owin.Security.OpenIdConnect.<AuthenticateCoreAsync>d__1a.MoveNext() +4931
   System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +26
   Microsoft.Owin.Security.OpenIdConnect.<AuthenticateCoreAsync>d__1a.MoveNext() +6453
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Security.Infrastructure.<BaseInitializeAsync>d__0.MoveNext() +571
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Security.Infrastructure.<Invoke>d__0.MoveNext() +255
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<RunApp>d__5.MoveNext() +182
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Security.Infrastructure.<Invoke>d__0.MoveNext() +638
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<RunApp>d__5.MoveNext() +182
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +92
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<DoFinalWork>d__2.MoveNext() +180
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.StageAsyncResult.End(IAsyncResult ar) +69
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.IntegratedPipelineContext.EndFinalWork(IAsyncResult ar) +64
   System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +380
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

I can see that it's struggling to connect to a database I'm just not sure what/which one it's struggling with and I need help. I checked the event logs but they just report the same message.

The two lines that appear in the error stack trace that are of particular interest are:

 MyProject.Models.ADALTokenCache..ctor(String signedInUserId) in C:\Users\John\Desktop\Development\MyProject\Models\AdalTokenCache.cs:26
   MyProject.Startup.<ConfigureAuth>b__7_0(AuthorizationCodeReceivedNotification context) in C:\Users\John\Desktop\Development\MyProject\App_Start\Startup.Auth.cs:54

Where have I gone wrong?

This is caused by the fact that by default Azure AD-based login through login.live.com sets up a connection in web.config called "DefaultConnection" which tries to use the localdb, which is likely not installed on your remote web server by default. Hence "file not found" - it cannot find the localdb file.

What you need to do is:

1. Create a table called UserTokenCache in a SQL Server Database of your choice and populate it with the following fields:

   • UserTokenCacheId - int
   • webUserUniqueId - nvarchar
   • cacheBits - varbinary
   • LastWrite - datetime2

2. Change the connection string in web.config named "DefaultConnection" to point to the above database

3. Redeploy

Note: If you then see a "400 bad request" you may need to delete all your cookies (or use Incognito mode) as it will keep trying to use the old OAuth tokens