- SQL Server Configuration Manager
- Microsoft SQL Server Management Studio 2008
- Administrator Login on the computer
1. Log on to the computer as an Administrator (or Any user with administrator privileges)
2. Open "SQL Server Configuration Manager"
3. Click "SQL Server Services" on the left pane
4. Stop "SQL Server" and "SQL Server Agent" instance on the right pane if it is running
5. Run the SQL Express in single-user mode by right clicking on "SQL Server" instance -> Properties.
5.1. Click Advanced Tab, and look for "Startup Parameters". Change the "Startup Parameters" so that the new value will be -m; (without the <>)
example:
from: -dc:\Program Files\Microsoft SQL.............(til end of string)
to: -m;-dc:\Program Files\Microsoft SQL.............(til end of string)
6. Start the SQL Server
7. Open your MS SQL Server Management Studio and log on to the SQL server with "Windows Authentication" as the authentication mode. Since we have the SQL Server running on single user mode, and you are logged on to the computer with Administrator privileges, you will have a "sysadmin" access to the database.
8. Expand the "Security" node on MS SQL Server Management Studio on the left pane
8.1 Expand the "Logins" node
8.2 Double-click the 'sa' login
9. Change the password by entering a complex password if "Enforce password policy" is ticked, otherwise, just enter any password.
10. Make sure that "sa" Account is "enabled" by clicking on Status on the left pane. Set the radio box under "Login" to "Enabled"
11. Click "OK"
12. Back on the main window of MS SQL Server Management Studio, verify if SQL Server Authentication is used by right clicking on the top most node in the left pane (usually ".\SQLEXPRESS (SQL Server )") and choosing properties.
12.1 Click "Security" in the left pane and ensure that "SQL Server and Windows Authentication mode" is the one selected under "Server authentication"
13. Click "OK"
14. Disconnect from MS SQL Server Management Studio
15. Open "Sql Server Configuration Manager" again and stop the SQL Server instance.
16. Right-click on SQL Server instance and click on "Advanced" tab. Again look for "Startup Parameters" and remove the "-m;" that you added earlier.
17. Click "OK" and start the SQL Server Instance again
You should now be able to log on as "sa" using the new password that you have set in step 9.
