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.
Posted on September 10, 2012, in Programming & Development and tagged account, auth, cannot login, cmd, cmd prompt, command, cred, creds, lock, locked out, login, mssql, prompt, server, sql, sql 2000, sql 2005, sql 2008, sql server. Bookmark the permalink. Leave a comment.