Delphi 7 - ADOConnection unable to connect due to SQL "must change password" flag

Question

I am having a problem that occurred in a specific circumstances in my Delphi 7 application.

I have ADOConnection that goes to my MS SQL server with some username and pass - SQL authentication. The problem is that MS SQL login was created with "User must change pass at next login" flag that makes ADO Connection impossible to connect with error message "18488 - Login failed for user '%.*ls'. Reason: The password of the account must be changed."

Normally, in MS SQL Management Studio a change password prompt is shown, and user is able to enter new password. The question is what should I do to force password change on this user in my application? I am able to catch error number and prompt for login change, but what then? There is no flag in a connection string that I could use to change pass/reset to new (like Old Password and New Password). What should I do then?

Can anyone help?

Answer 1

When you trap the 18488 error, You need to show your own "change password" dialog, and use connection string properties "Old Password"/"Password" via SQL Native Client as your provider (i.e. Provider=SQLNCLI10 or SQLNCLI.1).

Here is a small code I used to test this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  // 12345 is the "old password"
  ADOConnection1.ConnectionString := 'Provider=SQLOLEDB.1;Password=12345;User ID=test;Initial Catalog=test;Data Source=127.0.0.1;Persist Security Info=True;';
  try
    ADOConnection1.Open;
  except
    if Assigned(ADOConnection1.Errors) and (ADOConnection1.Errors.Count > 0) and
      (ADOConnection1.Errors.Item[0].NativeError = 18488) then
    begin
      // show your "change password" dialog... new password is 67890
      ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Old Password=12345;Password=67890;User ID=test;Initial Catalog=test;Data Source=127.0.0.1;Persist Security Info=True;';
      ADOConnection1.Open; // this will login and change the password

      // OPTIONAL (unless you use SQLNCLI10.1 anyway)
      // you may close the connection and re-open with your original provider and new password
      ADOConnection1.Close;
      ADOConnection1.ConnectionString := 'Provider=SQLOLEDB.1;Password=67890;User ID=test;Initial Catalog=test;Data Source=127.0.0.1;Persist Security Info=True;';
      ADOConnection1.Open;
    end
    else
      raise;          
  end;
  ShowMessage('Login OK');
end;

My answer is based on these readings:

• How DO you change your SQL Login password?
• SQL Server Native Client: Connection strings and OLE DB
• Changing Passwords Programmatically

This is the official way of changing passwords from the client side when you enforce password expiration and use "User must change password at next login" option on the SQL server.

---

If installing the SQL Server Native Client on the users machine is an issue, I can think of few more options:

1. Create a web-service (on your servers) that will be responsible for changing the user's password provided old/new passwords and returning status back to your client.
2. Connect as "super user" (such as sa), and alter the user's/login password. meaning you will need to hold that username/password on the client machine (bad idea IMHO from security point of view but might be workable). - not tested
3. do NOT create SQL logins with "User must change password at next login". which is my favorite solution.

Answer 2

If you are happy that you can trap this particular message, you can then show your own form, explaining that the SQL login password must be changed and ask the user for a new password. Then use the ALTER LOGIN command to change the password.

For safety, I would use the OLD_PASSWORD option to ensure that this user knew the old password and therefore has the facility to set the new one.

If you do not want the user to set it, then set it yourself.

Note that your user must have the ALTER ANY LOGIN permission set in order to do this.