Since it looks like everything I blog about lately is showing how to get onto SQL instances to which you don’t have formal credentials, I figured what the heck – let’s do a post on SQL 2000.
Earlier on today Saurabh Sapra [twitter] sent a tweet to SQL Server MVP Thomas LaRock [blog|twitter]:
To which Tom replied:
I was flattered. But I had no posts on that subject. So in turn, I replied:
It turns out that it is straightforward. And I will show you how to do just that – recover the SA password of a SQL 2000 instance. It’s easier than you think. As a matter of fact, I’m going to show you how to recover the password for any SQL login that has a current session established against a SQL 2000 instance.
Caveat – and this is important – you cannot recover the password if the user does not have a session established. I.E., if it is not logged on to SQL. I haven’t heard of a way to recover an SA password if there isn’t a connection established to SQL. If you know how to do this, please drop me a line.
Let’s dive right into it.
On Enterprise Manager, I registered the local instance using the SA login – then dropped the BUILTIN\Administrators login.
Nothing shocking there – just showing you that there is at least one active connection to SQL (Enterprise Manager itself) and that there are no other SQL logins on the box.
Next, I opened up a CMD window, and created a memory dump of the sqlservr.exe process. I can do this because I am a local Windows Administrator on this box. To achieve this I used the SysInternals’ ProcDump utility. Then I took the memory dump and parsed it through strings.exe, another SysInternals utility, essentially filtering from the memory dump anything that doesn’t look like a string.
Now I opened dump.txt in Notepad, and searched for the host name of my SQL 2000 box immediately followed by sa. In this case my VM is named “HURRICANE”, so I searched for “HURRICANEsa”
And there you have it. The password is found between the string “HURRICANEsa” and the string “MS SQLEM(LOCAL)ODBC”. The SA password for this instance is “Password@99!”.
Obviously your memory dump will look different than mine, given that your SQL client string will probably be different than the one Enterprise Manager uses – which I leveraged for this example. Also, my SQL 2000 instance had just been restarted, so I had less ‘stuff’ in memory than you probably will. You might even get a false positive. My point: YMMV.
As I mentioned above, this technique also works for any SQL login with an established connection, regardless of its privileges on the server. Moreover, this technique also works on SQL Server 2005 and (I believe – haven’t tested it) some builds of 2008 and 2008 R2. It wasn’t too long ago that the SQL team at Microsoft started to finally clear this area of memory after a session’s credentials have been verified.
Hope this was useful.
UPDATE: A couple of folks asked me which build I used to test this. I originally tested it on SP4a, and later confirmed with a more recent build, 8.00.2187 (but not the latest one that’s available only to customers with support agreements).