Backtrack:  
 
by lunarg on December 11th 2024, at 09:36

Provided you still have administrative access to the server itself, and the server is running in mixed mode (i.e. you can actually use local SQL logins), you can perform the steps below to reset the password. Note that this will result in some downtime as the SQL Server service will have to be restarted in single user mode.

First, determine the service name (can be done via services.msc): if it is the default instance, it's usually called MSSQLSERVER. If an instance name was specified, it is usually MSSQLSERVER$instance-name. E.g. if the instance is called MYSWEETAPP, the service name would be MSSQLSERVER$MYSWEETAPP.

Open an administrative (elevated) command prompt or Powershell:

  1. First, stop and start the service into single user mode:
    net stop MSSQLSERVER
    net start MSSQLSERVER /f /mSQLCMD
    The parameter above will only allow SQLCMD (the command-line client) to connect. Because it is single user mode, no authentication is performed.
  2. Next up, you can now run the following command to alter the password of the sa (or any other account). Note that if the server is configured to enforce complex passwords, it is a requirement in single user mode as well.
    sqlcmd.exe -E -S $sql_server_instance -Q "ALTER LOGIN sa WITH PASSWORD = 'some-strong-password';"
    Note that if the command was successfully executed, no output will be shown.
  3. Finally, you can stop and restart the SQL Server service in normal mode again:
    net stop MSSQLSERVER
    net start MSSQLSERVER
  4. You should now be able to log in with the sa account and the new password.