Locking oneself out of SQL Server and the good old registry diff trick

Probably one of the oldest tricks in the book, but definitely one that should not be forgotten, is the Good Old Registry Diff Trick. You see, the other day I accidentally locked myself out of my local SQL Server by making a classic mistake: I deleted my own login!

Well, actually I had created a SQL login that I planned on using after having deleted the login used by my Windows account, but I had forgotten til enable the “Mixed mode authentication” setting, and thus only Windows authentication was allowed.

A little bit of Googling revealed a blog post that mentioned a “LoginMode” key somewhere in the machine’s registry, but my registry did not contain the branch mentioned in the blog post.

Good Old Registry Diff Trick to the rescue!

I went into another machine with a functional SQL Server that I could access, and made sure that only Windows authentication was allowed. Then I used Regedit to export the entire contents of the HKEY_LOCAL_MACHINE hive into a file called “before.reg”. Then I went in and changed authentication mode to “Mixed mode”, and repeated the export into another file, “after.reg”.

Then I loaded a diff tool with the following two files, giving me this:

which revealed that the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer] branch had the “LoginMode” key, which I could then change from “1″ to “2″ in order to allow “Mixed mode authentication” on my almost-impenetrable SQL Server, allowing me to log in.

Thank you, Good Old Registry Diff Trick.