How to reset lost sa password in SQL express

http://www.vladan.fr/how-to-reset-lost-sa-password-in-sql-express/

You’ll have to first change the authentication mode from SQL server authentication into Mixed mode > Then enable the sa user name > Do a reset of the sa password (assigning new password to the sa user).

How to reset lost sa password in SQL express – the steps:

First – it’s necessary to change the authentication mode into Mixed mode. We’ll use a registry hack for that as this is the only (I know) way to do that.

Open the registry editor and go to here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer

Change the value of LoginMode from 1 to 2

How to change login mode in SQL ExpressHow to change login mode in SQL Express

Second – Now you can Enable the sa user account and do a reset of a password.

  • You’ll have to first start services.msc console and stop the SQLEXPRESS service
  • Enter -m into the “Start Parameters” field
  • Start the service
  • Open command prompt and enter this command:

osql -S YOUR_SERVER_NAME\SQLEXPRESS -E

so in my case it was a mirage server VM which netbios name was mirage. So I entered:

osql -S MIRAGE\SQLEXPRESS -E

It depends of the name of your system…..so dont put the name ‘YOUR_SERVER_NAME’… -:) I won’t work..

Third – there will be a prompt with numbers in front. Go and enter exactly this (except the new_password)

1> alter login sa enable
2> go
1> sp_password NULL,’new_password’,’sa’
2> go
1> quit

How to reset SQL Express sa passwordHow to reset SQL Express sa password

note in this example the password is Totogogo007*

  • Stop the SQLEXPRESS service once again
  • Remove the -m from the start parameters field
  • Start the service

How to reset lost sa password in SQL expressHow to reset lost sa password in SQL express

You’re done! Now you should be able to login into SQL server management studio by using the sa user account and the new password…..

During the installation of SQL server database (express or standard) the DB engine is set to either Windows authentication mode or SQL server and Windows authentication mode.

Source: Partly from MSDN

Update: There is another option to gain an access to the SQL server (without restarting the DB). Thanks to Genadi for the comment and source.

Step 1: login with an acccount which is in the local administrator group

Step 2: Use PsExec to start the SQL server management studio as NT AUTHORITY\SYSTEM account which has an access to SQL server

From the source:

Source here

Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, “Run as Administrator”). Run the following command, adjusting for your actual path to Management Studio, which may be different:

PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe

This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).

While I haven’t tested this personally, it seems that the NT AUTHORITY\SYSTEM account does the trick

also read this:
https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

No Comments

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *