Change expired password without "Password Expired dialog box"

Question

I'm logging on my application using Sql Server Database logon account. However, when a user's password is expired, i can only catch the error message using "error:18488" and display a message to user.

When I login using Sql Server Management Studio with an account with expired password, a dialog box requiring me to change password appears.

Is there a way to allow my application to change the expired password with something like T-Sql statement?

Answer 1

I've found the solution here, I used SqlConnection.ChangePassword().

Here's the code:

class Program
{
    static void Main(string[] args)
    {
        System.Data.SqlClient.SqlConnection.ChangePassword(
        "Data Source=a_server;Initial Catalog=a_database;UID=user;PWD=old_password", 
       "new_password");
    }
}

Hope it helps others too. :)

Answer 2

You can change a user login using ALTER LOGIN.

ALTER LOGIN [userName] WITH PASSWORD=N'StrongPassword'

Another option is to use the Stored Procedure sp_password.

sp_password [ [ @old = ] 'old_password' , ]
     { [ @new =] 'new_password' }
     [ , [ @loginame = ] 'login' ]

C# code:

private readonly SqlConnection m_oConn = new SqlConnection("ConnectionString");
private readonly SqlCommand m_oCmd;

m_oCmd = new SqlCommand("sp_password", m_oConn) { CommandType = CommandType.StoredProcedure };

m_oCmd.Parameters.AddWithValue("@old", null);
m_oCmd.Parameters.AddWithValue("@new", "newpassword");
m_oCmd.Parameters.AddWithValue("@loginame", "username");

var da = new SqlDataAdapter { SelectCommand = m_oCmd };

var ds = new DataSet();
da.Fill(ds);