Azure AD authentication giving error for signing in admin of database after azure deployment of the web app

Question

Link to SSMS snapshotI have created a web app in .NET MVC and added azure ad authentication to it. On running it on local host everything is working very correct. But when I deploy the application on azure web app and put that URL in redirect URI its throwing error as:

Login failed for user 'admin@poc'.

As a deployment dependency we need to create a database on Azure, I have done that too and placed the connection string in the web config also. The user id and passwords are correct as able to connect to database using same credentials in management studio.

Below is the connection string:

Below is the stack trace:

[SqlException (0x80131904): Login failed for user 'admin@poc'.] 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, SqlAuthenticationProviderManager sqlAuthProviderManager) +947 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +342 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) +514 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +91 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +438 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +79 System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +201 System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +156 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +22 System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) +92 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) +219 System.Data.SqlClient.SqlConnection.Open() +101 System.Data.Entity.Infrastructure.Interception.<>c.b__13_0(DbConnection t, DbConnectionInterceptionContext c) +12 System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) +72 System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) +424 System.Data.Entity.SqlServer.<>c__DisplayClass60_0.b__0() +426 System.Data.Entity.SqlServer.<>c__DisplayClass2_0.b__0() +10 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func1 operation) +189 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) +78 System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action1 act) +175 System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) +552 System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +90 System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection) +173 System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable1 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, Func3 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.CreateDatabaseIfNotExists1.InitializeDatabase(TContext context) +138 System.Data.Entity.Internal.<>c__DisplayClass66_01.<CreateInitializationAction>b__0() +76 System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +60 System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +372 System.Data.Entity.Internal.<>c.<InitializeDatabase>b__58_0(InternalContext c) +9 System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) +129 System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1 action) +196 System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() +86 System.Data.Entity.Internal.InternalContext.Initialize() +30 System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +16 System.Data.Entity.Internal.Linq.InternalSet1.Initialize() +53 System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() +15 System.Data.Entity.Infrastructure.DbQuery1.System.Linq.IQueryable.get_Provider() +38 System.Linq.Queryable.FirstOrDefault(IQueryable1 source, Expression1 predicate) +85 selftrial.Models.ADALTokenCache..ctor(String signedInUserId) in C:\Users\ShubhendraShukla\source\repos\selftrial\selftrial\Models\AdalTokenCache.cs:26 selftrial.Startup.b__7_0(AuthorizationCodeReceivedNotification context) in C:\Users\ShubhendraShukla\source\repos\selftrial\selftrial\App_Start\Startup.Auth.cs:54 Microsoft.Owin.Security.OpenIdConnect.d__9.MoveNext() +4047 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +27 Microsoft.Owin.Security.OpenIdConnect.d__9.MoveNext() +5436 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 Microsoft.Owin.Security.Infrastructure.d__31.MoveNext() +452 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 Microsoft.Owin.Security.Infrastructure.d__5.MoveNext() +217 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.d__7.MoveNext() +179 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 Microsoft.Owin.Security.Infrastructure.d__5.MoveNext() +519 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.d__7.MoveNext() +179 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.d__12.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() +429 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +50 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +163

First get running using SSMS under same conditions.You have to run SSMS from same machine as your server code and when you login on SSMS use Username and Password in login window (not Window Credential).When you run an application from inside Visual Studio you not running with Admin credentials unless you right click the VS shortcut and select Run As Admin.I'm suspecting you are working with window credentials (connection string has Integrated Security = True) and not when you use a Username/Password.Roles on Server may not accept Username/Password. Or you are not using same database. — jdweng
@jdweng I connected to SSMS using same sql authentication. So do you suggest to change something in config — Shubhendra Shukla
The connection string has to be the same as the SSMS. The SSMS login window has a Server Name, An Instance, and Credentials which can be windows (in connection string Integrated Security = True) or Username/Password. So fixing connection string should solve issue. The server may have more than one database so you have to make sure you are using the correct database. — jdweng
Check the snapshot to the SSMS, and it has only one db. Below is the URL to the image i.stack.imgur.com/0LUJs.png @jdweng — Shubhendra Shukla
If SSMS is working from same machine as c# application should also work without any other changes. The properties of the connection (c# connection string and SSMS login windows) have to also be the same. When running from VS you are not automatically running As Admin unless you start VS from right click shortcut and select Run As Admin. There is nothing wrong with your firewall and would ignore the answer below. — jdweng

MMThornberg MMThornberg · Accepted Answer · 2020-11-20T12:18:43

I suspect you have not configured the SQL firewall to allow connections from Azure resources. In the Azure Portal, find the Azure SQL Database and click "Set server firewall":

Ensure that the slider for "Allow Azure services(..)" is set to "Yes".

Azure AD authentication giving error for signing in admin of database after azure deployment of the web app

1 Answers