Blog Archives
Cannot login logon failed to SQL Server Windows Auth
Locked out of SQL Server? Accidentally right click on your account in Management Studio and hit delete? Or did Sys Admins force you onto the domain and delete your local superman account that you installed SQL under? Oops! No problem! (If You’re a local admin).
Check out these simple steps.
First, run a cmd prompt as administrator. Enter the following. This will start sql in single user mode, enable the built in sa account, and reset the password for it.
net stop mssqlserver net start mssqlserver /c /m /t3604 sqlcmd -E alter login sa with password='1234' go alter login sa enable go exit net stop mssqlserver
Now open your registry (regedt32) and browse to:
HKLM\Software\Microsoft\Microsoft SQL Server\(Find Your Instance)\MSSQLServer\LoginMode
Change the value to 2. Then go back to command prompt and enter:
net start mssqlserver
Voila! You’re back in business. Fire up management studio and enter your instance name and the sa credentials you created, don’t forget to select sql server authentication.
Once you’re in, Microsoft recommends changing back to Windows Auth (adding your domain/local account). This is up to you. Enjoy.
Keep Batch File Open to enter more commands
Typically the solution you will see to this question upon googling is to add “pause” to the end of your batch file.
This will keep the batch open, but it will not allow you to enter additional commands and after hitting a key will close the window immediately.
Alternatively, if you would like to keep the window open, such as creating a shortcut to a command line app, you can use cmd.exe /K.
G:\PathToMyCommandLineExeApp G: MyApp.exe /? cmd.exe /K cmd.exe
Place the above code snippet into batch file of your choosing, such as start menu folder, and upon launch it will not change into the directory and path where your command line app is located, execute it displaying it’s parameters (/? is app specific) and then run cmd.exe with \K option which allows the window to stay open per MSDN (see reference below).
Enjoy.
References
MSDN, http://support.microsoft.com/kb/830473
