Changing sa password with Delphi

[kabiri 3]

I change the SQL Server password using the following command in Delphi,
The connection string is to the master database and I am using Windows authentication.

 

Quote

    try
      qry.Close;
      qry.SQL.Clear;
      qry.SQL.Add('ALTER LOGIN sa WITH PASSWORD = '+QuotedStr('My@Pass159753'));
      qry.ExecSQL;
    except on E: Exception do
      ShowMessage('Error : '+E.Message);
    end;
 

But I can no longer log in to SQL Server using the sa user.

When I execute this command in Management Studio, the password is changed and I can log in with sa.

Quote

ALTER LOGIN sa WITH PASSWORD = 'My@Pass159753'

Delphi 11 + FireDAC + SqlServer 2014 sp2 express

 

----------Edit-----------

 

I wrote the same code with ADO and it works perfectly.

 

 

-----Edit-------

Alright, I've noticed that this issue occurs when a specific pattern is used.

Edited May 4, 2024 by kabiri

[Brian Evans 111]

Your password strings as posted are infested with zero width non-breaking spaces at differing locations. 

[kabiri 3]

10 minutes ago, Brian Evans said:

Your password strings as posted are infested with zero width non-breaking spaces at differing locations. 

I didn't write my original password here and changed it.
Then I realized that FD might not work properly if the password follows a specific pattern.

[Brian Evans 111]

You could pass the password as a parameter to avoid any FireDAC string/macro processing. Use one of the ExecSQL overloads to get rid of the housekeeping lines.

  qry.ExecSQL('ALTER LOGIN sa WITH PASSWORD = :PASSWORD',['newPASSWD'],[ftString]);

 

Edited May 4, 2024 by Brian Evans

[kabiri 3]

17 hours ago, Brian Evans said:

You could pass the password as a parameter to avoid any FireDAC string/macro processing. Use one of the ExecSQL overloads to get rid of the housekeeping lines.

  qry.ExecSQL('ALTER LOGIN sa WITH PASSWORD = :PASSWORD',['newPASSWD'],[ftString]);

 

Not work
 

Project ChangeSQlPass.exe raised exception class EMSSQLNativeException with message '[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '@P1'.'.

 

[Brian Evans 111]

Darn seems SQL Server doesn't allow the password to be parameterized.  An odd quirk. 

 

Two options I can think of : passing parameterized values into a chunk of SQL that then creates dynamic SQL and runs that (ex: answer to c# - How to change a sql login password with variables - Stack Overflow) or turning preprocessing off in FireDAC ( qry.ResourceOptions.PreprocessCmdText := false; ) for the query.