Login failed. User must change password at next login

When new SQL login is created by using SSMS - New Login dialog, we tend to give a SQL Login name and its password, and probably set default DB.

We, then, use the login information to connect to SQL Server in our application. If it's an ASP.NET web page, some kind of DB processing codes as below would be added.

public partial class _default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string strConn = "Data Source=(local);Initial Catalog=MyDB;User Id=MyUser;Password=myPasswd!1";
        DataSet ds = new DataSet();

        using (SqlConnection conn = new SqlConnection(strConn))
        {
            conn.Open();
            string sql = "SELECT * FROM Customer";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            adapter.Fill(ds);
        }

        Gridview1.DataSource = ds.Tables[0];
        Gridview1.DataBind();
    }
}

If the web page are published to local IIS web application and we browse the web page in IE, we might get HTML 404 error as follows.

In order to figure out what's going on, if we debug the web page in Visual Studio, we will find more clue about the error : SQL login failure. Even though username and password are correct, we might get login failure.

So the reason is SSMS New Login dialog basically enables [User must change password at next login] by default. Since ASP.NET application cannot show new password dialog, it just throws exception and simply dies.

To address this login failure, we can reset password and turn off expiration check as follows.

ALTER LOGIN [MyUser2] WITH PASSWORD = 'myPasswd!1'
GO
ALTER LOGIN [MyUser] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Once the TSQL is run, refreshing the ASP.NET webpage should work.