CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-mvcazure-web-app-service
WinFXGuy
WinFXGuy

Reputation: 1599

Asp.net Membership provider fails to connect to on premise SQL Server when deployed to Azure app service

I am trying to port one of my asp.net MVC (V 4.0/ .Net Framework 4.5) to Azure as app service. My database is SQL Server and my application connects to it using Hybrid Connection. My application uses SQL Authentication to connect to database. Everything works fine when deployed to IIS server on one of our locally hosted servers, but the authentication feature breaks when deployed to Azure as an app service.

My controller code:

[HttpPost]
        [AllowAnonymous]
        [ValidateAntiForgeryToken]
        public virtual ActionResult Login(LoginModel model, string returnUrl)
        {
            if (ModelState.IsValid)
            {
                //var v = Membership.GetAllUsers();
                try
                {
                    if (Membership.ValidateUser(model.UserName, model.Password))
                    {
                        FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe);
                        if (Url.IsLocalUrl(returnUrl))
                        {
                            return Redirect(returnUrl);
                        }
                        else
                        {
                            return RedirectToAction("Index", "Home");
                        }
                    }
                    else
                    {
                        ModelState.AddModelError("", "The user name or password provided is incorrect.");
                    }
                }
                catch (Exception ex)
                {
                    ModelState.AddModelError("", ex.ToString());
                }
                
            }

Error on Azure:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'OSUser'. at 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) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) at System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) at System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) at WebMatrix.WebData.SimpleMembershipProvider.ValidateUser(String username, String password) at USC.Controllers.AccountController.Login(LoginModel model, String returnUrl) in D:\a\1\s\USC\Controllers\AccountController.cs:line 36 ClientConnectionId:b1f6b763-80f6-47f0-9a50-c70ab30635bd Error Number:18456,State:1,Class:14

Any ideas??? Thanks!

Upvotes: 1

Views: 301

Answers (1)

IpsitaDash-MT
IpsitaDash-MT

Reputation: 1430

The message "Login failed for User" is a generic error message that might have a variety of causes. The error message sent to the client does not include information about the reason for the login failure for security reasons.

You need to give this user logon permission to SQL Server and access to the database: Create a Login - SQL Server

It may be that the database you're connecting to was generated using an older version of the.NET Framework's aspnet regsql.exe tool when you deployed your app. Try using the same version of aspnet regsql that you used locally to create and configure the membership database. Maybe you just utilised SQL Express and the /App Data folder locally.

In both databases, compare the table structure. Please compare the AspNet Membership table.

You could even refer to : System.Data.SqlClient.SqlException: Login failed for user | The ASP.NET Forums

Troubleshooting: Login Failed for User 'x' | Microsoft Docs

Upvotes: 0

Related Questions