Search code examples
asp.netasp.net-membership

Login Fail after moving database and website


I am relocating a website and database from azure to a local server (sql server 2008 r2). I successfully copied the database and set up the website in IIS. I can navigate to the website fine, but it won't logon on.

My Stack Trace

Message: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.)

Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle, SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock) at System.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate) at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate) at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) 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, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions) 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& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 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 EbpWebSite.Account.login.Page_PreLoad(Object sender, EventArgs e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Am I missing something? Did the move screw up the password salts for the login? My login tables are a standard aspnet_Membership, Roles, Users setup. I just copied from Azure to local DB. The first line would indicate, I got my connection strings updated correctly.

As suggested adding connectionString

<add name="EbpCloud" connectionString="Server=EBP-OCON-SQL1;User ID=dbread22;Password=*****;Trusted_Connection=True;Encrypt=True;Connect Timeout=0;Database=EbpReporting;" />

The original string was Server=tcp:server.database.windows.net

The website resides locally on the same server as the DB. (Do I need to have local as part of server name?) my login code:

      if (Membership.ValidateUser(userName, passWord)) {
    FormsAuthentication.Initialize();
    FormsAuthentication.SetAuthCookie(userName, false);
    using (var conn = Utilities.SqlConnectionEbp(Utilities.DatabaseEbp.EbpReporting)) {
      // call stored procedure to get the user default session settings
      using (var cmd = new SqlCommand("aspnet_UserDefaultSessionSettings", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@UserName", userName));
        // Check Sql State and open connection
        if (!conn.State.Equals(ConnectionState.Open))
          conn.Open();
        using (var reader = cmd.ExecuteReader()) {
          while (reader.Read()) {
            Session["userName"] = userName;
            Session["forceResetPassword"] = passWord.Equals("p@ssw0rd");
            Session["displayName"] = reader["displayName"].ToString();
            Session["instanceId"] = Convert.ToInt16(reader["instanceId"]);
            Session["userEmail"] = reader["email"].ToString();
            Session["userId"] = new Guid(reader["UserId"].ToString());
            //Session["clinicList"] = Utilities.GetClinicList();
          }
        }
      }
    }
    Response.Redirect("/administration/supplierinvoice.aspx", false);
  }

The called Utilities class:

      public static class Utilities
  {
    public enum DatabaseEbp
    {
      Ebp, EbpReporting, Master, ebpCIS
    }
    /// <summary>
    /// SqlConnectionEBP - EBP Connection object
    /// </summary>
    /// <returns>SqlConnection</returns>
    public static SqlConnection SqlConnectionEbp(DatabaseEbp database) {
      var connString = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["EbpCloud"].ToString());
      connString.InitialCatalog = database.ToString();
      var conn = new SqlConnection(connString.ToString());
      return conn;
    }

Solution

  • Sometimes it is the simple things. The website and the database both reside on the same physical server so changed connectionstring to

      <add name="EbpCloud" connectionString="Data Source=localhost;Initial Catalog=ebpreporting;Persist Security Info=True;User ID=dbread22;Password=*****" providerName="System.Data.SqlClient" />
    

    and it worked.