none
Azure AD Signin returns a database error

    Question

  • My MVC web application uses Azure AD Authentication to let users 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.

    [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

    The two lines that are interesting 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

    I investigated them to see what the problem could be and chased it back to a local MDF.  This local database file is in my MVC project App_data folder which I think it's using for token cache.  I'm not sure if this is the problem or not. This is line 26 of the ADALTokenCache class that it doesn't seem to like.

    ADALTokenCache:26 --> Cache = db.UserTokenCacheList.FirstOrDefault(c => c.webUserUniqueId == userId);

    So my investigations lead me on this trail, the db context is stored in the ApplicationDbContext class which points to "DefaultConnection" which in the web.config file is as follows:

    <add name="DefaultConnection" connectionString="Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-MyProject-d6930558-4696-46f3-a7fd-10a8b220a64a.mdf;Initial Catalog=aspnet-
    MyProject-d6930558-4696-46f3-a7fd-10a8b220a64a
    ;Integrated Security=True" providerName="System.Data.SqlClient" />

    This proves that the local MDF file is to blame or at least partically.  This is framework the Azure AD Authenticaation wizard puts in place but it doesn't seem to play well with Azure when you upload it.

    My project works fine locally but when I uploade to Azure I get the above error message and it seems to be the local DB file that's the problem.  What can I do??

    Tuesday, March 21, 2017 9:40 AM

All replies