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.