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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: