How to check if old password matches before change it?

Question

I have this code:

//Update login query
string sql = "ALTER LOGIN " + login.ToUpper() + " WITH PASSWORD = '" + password + "' OLD_PASSWORD = '" + oldpassword + "'";

//Try connection and execute
using (SqlConnection connection = new SqlConnection(GetConnection()))
{
     connection.Open();

     SqlCommand command = new SqlCommand(sql, connection);
     command.CommandType = System.Data.CommandType.Text;
     var result = command.ExecuteScalar();
     connection.Close();
}

This sql query changes the password of a login in database. Notice that it needs the old password to proceed. Altough, if I pass a wrong old password, then it throws a SQLException:

Cannot alter the login 'SEVA', because it does not exist or you do not have permission.

How can I check if the old password is correct before I execute this query, so I can show an error message to the user?

I don't think you can. The best you can do is to try to change the password as you've already done, then catch the exception and report back to the user if it failed. — Steven Rands, Jan 16 '15 at 16:50
Why check before the query? You know what the exception is, just act based on the success or exception being thrown. — KevenDenen, Jan 16 '15 at 16:51
Checkout this previous Stackoverflow posting and review the accepted answer here - http://stackoverflow.com/questions/19308801/update-a-password-if-user-name-and-old-password-match || http://stackoverflow.com/questions/26256351/encrypt-passwords-on-sql-server-2008-using-sha1 — MethodMan, Jan 16 '15 at 16:54
@MethodMan that's a different problem. Here we are talking about Sql Server Users Login and their passwords — Steve, Jan 16 '15 at 16:58
Sorry Steve.. it just sounds similar.. in fact he could implement something like checking to see if the hash matches exactly on the sql end.. then in code if it passes .. then accept the new password and use that to update the database.. I have used SHA encryption to hash passwords.. — MethodMan, Jan 16 '15 at 17:01
I think that you can do this unless I am not understanding when you want this to be done.. It would take 2 separate query's one to check the password for the existing that was passed in and a second one that would update based on the first method passing .. I do something like this similar in a web app and it works just fine on our Oracle DB without passing clear text.. all pass words are strongly encrypted.. — MethodMan, Jan 16 '15 at 17:18

score 1 · Accepted Answer · answered Jan 16 '15 at 17:22

Try this. You need to add something in the catch to tell the user he didn't enter the correct password

        //Update login query
        string sql = "ALTER LOGIN " + login.ToUpper() + " WITH PASSWORD = '" + password + "' OLD_PASSWORD = '" + oldpassword + "'";

        try {
        //Try connection and execute
        using (SqlConnection connection = new SqlConnection(GetConnection()))
        {
             connection.Open();

             SqlCommand command = new SqlCommand(sql, connection);
             command.CommandType = System.Data.CommandType.Text;
             var result = command.ExecuteScalar();
             connection.Close();
        }
    }
    catch(SQLException)
    {
//Do something here to tell the user something went wrong
    }

How to check if old password matches before change it?

1 Answers1