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.

Cheers,

-Argenis

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).

My friend and SQL Server MVP Jorge Segarra [blog|twitter] correctly pointed out shortly after that NT AUTHORITY\SYSTEM is not a member of the sysadmin server role in SQL Server 2012, codename Denali. And as of Release Candidate 0 for this version, this holds true.

What also holds true as of RC0 is that the Service SID for a number of services (at least three, the SQL Engine itself, SQL VSS Writer and Winmgmt) are members of the sysadmin role. And so in this post I’d like to demonstrate that it is possible to exploit one of these services' level of access to hop onto a 2012 (or 2014) instance as sysadmin.

The target: a named SQL instance called “DENALI_RC0” on one of my desktop PCs. Having dropped my login on SQL, when I try to logon to the instance I get the usual message:

I picked a service to become “the victim”. The SQL VSS Writer service seemed to be a good candidate: innocuous enough. If I stop it and restart it, no big deal.

I launched regedit and browsed to HKLM\SYSTEM\CurrentControlSet\services\SQLWriter - this is what I saw:

Now being an Administrator of this PC as I am, I went ahead and renamed sqlwriter.exe to sqlwriter.exe.orig, and put a copy of SQLCMD.EXE on C:\Program Files\Microsoft SQL Server\90\Shared.

Then I renamed SQLCMD.EXE to sqlwriter.exe.

Obviously kicking off the SQL VSS Writer service was not going to do anything – just error out:

And now I kick off the sqlwriter service again, expecting it to error out…but with a nice side effect.

Sure enough, launched SSMS 2012 and was able to login. And guess what, my login has sysadmin privileges.

And so I’m sure some of you have already yelled “SECURITY HOLE!!!!” by now – yeah, to a degree…but remember kids, if you’re a local Administrator on the box, you already own the box. Very little applications like SQL Server can do to protect themselves from a “rogue” Admin. Maybe a few adjustments to the security model for Windows’ SCM (Service Control Manager) are needed here, but I’ll let you decide on that.

UPDATE FOR SQL EXPRESS USERS: If you need to leverage this trick on a SQL Server Express install, you can use the "Winmgmt" service - Windows Management Instrumentation, aka WMI service.

Yesterday a Developer at work asked for a copy of an SSIS package in Production so he could work on it (please, dear Reader – withhold judgment on Source Control – I know!). I logged on to the SSIS instance, and when I went to export the package…

Oops. I didn’t have that password. The DBA who uploaded the package to Production is long gone; my fellow DBA had no idea either - and the Devs returned a cricket sound when queried.

So I posed the obligatory question on #SQLHelp and a bunch of folks jumped in – some to help and some to make fun of me (thanks, @SQLSoldier @crummel4 @maryarcia and @sqljoe). I tried their suggestions to no avail…even ran some queries to see if I could figure out how to extract the package XML from the system tables in msdb:

SELECTCAST(CAST(p.packagedata AS varbinary(max)) ASvarchar(max))

FROM msdb.dbo.sysssispackages p

WHERE p.name = 'LePackage'

This just returned a bunch of XML with encrypted data on it:

I knew there was a job in SQL Agent scheduled to execute the package, and when I tried to look at details on the job step I got the following:

Not very helpful.

The password had to be saved somewhere, but where??

All of a sudden I remembered that there was a system table I hadn’t queried yet:

Yesterday a member of the SQL community who I deeply admire sent me a DM on Twitter asking whether I had done a SQLFamily post for Thomas LaRock’s (blog|@SQLRockstar) Meme Monday for November. I replied that I did not, and I regretted not having done so. A subtle DM followed my response: “Get on it, you have all week”. And indeed I must. So here’s an attempt to express some of my feelings on a community that has catapulted my career like nothing else before I embraced it.

Nanos Gigantium Humeris Insidentes

I stand on the shoulders of giants. My SQLFamily has given me support at all levels. Professionally and personally. There is never a lack of will to help and provide advice to others in this community. And I do my best to help. On #SQLHelp on Twitter, via email, or even on the phone. I expect no retribution, because I know that when and if I do run into problems, my SQLFamily will be there for me.

I have met some of the most humble, dedicated and most professional people in the SQL community. And some of them have pretty big titles: MVPs, MCMs, Regional Mentors, and even leaders of PASS, SQLCAT members, and even PMs and Devs on the SQL Server team.

All are welcome, and that includes YOU!

I have also met some people that are rather reserved and don’t participate as much in the community, for whatever reason. Be as it may, let it be know to all that we are a very welcoming community – heck, some of my closest friends and people I can count on in the community have completely opposite political views. We share one goal: to get better and help others get better.

Even if you are a lurker – my hope is that one day you’ll decide to give back some of what you have learned.

You have to take it to the next level

On one of my previous jobs as an IT Supervisor I used to tell my team all the time about the benefits of continuous education and self-driven learning. Shortly after I left that job, the company went bankrupt and some of my staff got laid off – some without any severance pay whatsoever. I eventually found out that some of them had a really hard time finding another job, because their skills were simply outdated. They had become stale professionals. Don’t be one of them.

If you don’t take advantage of these learning resources, somebody else will – and that person has an advantage over you when applying for that awesome job position that got opened. There’s a severe shortage of good DBAs and DB Devs out there. What’s your excuse for not being excellent?

Even if your knowledge of SQL Server is at the beginner level, really – you have no excuse to get better. Just go to SQLUniversity and learn from there. Don’t get stale!

Thank You

To all of you in the SQL community who put so much time and energy into helping others, my deepest gratitude to you. I can’t wait to meet you all again at the next event and share our SQL stories over a pint of beer (or a shot of Jaeger)

Quick post - I got news that I will be speaking at the PASS Summit 2011. What a huge honor. As I understand it, I am the first Venezuelan ever to speak at PASS. I'm super excited!

The Lightning Talk is called "Quick Tips to Enhance tempdb Performance", and I have to practice this talk a lot. I'll have exactly 5 minutes to deliver it, and discussions about tempdb performance can go on for hours. So I need to keep it concise and to the point.

We had an email thread at work where someone was asking for assistance with an unknown sa password on a SQL instance on a new laptop. “No big deal” – I said to myself – “You can reset the sa password by starting SQL in single-user mode” (If you haven’t heard about this, have a look here).

And then I started thinking – but what if this happen to be a production server with tons of users and suddenly we found ourselves locked out of SQL Server? Starting with SQL Server 2008, the BUILTIN\Administrators group is not made a member of the sysadmin server role unless you add it explicitly. You’d have to bring the server down to reset the sa password. Downtime is not good eats.

Yeah, well – don’t get a false sense of security. Members of the local Administrators group can still login to SQL Server. With full sysadmin privileges.

How? By impersonating the NT AUTHORITY\SYSTEM login that is created by Setup and granted sysadmin privileges by default. You can easily do this with PsExec (using the –s switch) or creating a Windows Scheduled Task running under the SYSTEM account.

Let me show you what I mean:

By default, things look like this on SSMS on a default installation (this particular instance just happens to be named, other than that it’s all default values for setup – MAGMA is the name of my test VM and SQLPS is the name of the domain)

As you see, the Administrators group is not listed as a login. Which means that if you try to login using the Administrator account, you’ll get a nice error window that says:

I happen to have a copy of PsExec on my C:\bin directory, so I will use it to illustrate this example. Let’s kickoff a cmd window (running as Administrator) that will run PsExec and launch another cmd window running as cmd – the parameters I used for PsExec are: Interactive (-i) and Run as System (-s):

I ran “whoami” on the child cmd window to show who’s running it. That was easy, eh?

Now, I just launch sqlcmd and run a couple of statements:

And voilà – I’ve created a new login for the Administrator user and made it a member of the sysadmin server role, all while running under NT AUTHORITY\SYSTEM.

So what is Microsoft’s guidance in regards to this NT AUTHORITY\SYSTEM login? To leave it as is – here is the KB article that states it. It was written for SQL Server 2005 and it hasn’t been updated since. I asked some of my contacts at CSS and they confirmed that the article is still considered valid for 2008 and 2008 R2. And that would be my humble recommendation as well. On a heavily secured installation though, you will definitely want to remove that login and perform careful testing of the side-effects of removing it.

Cheers!

Edit: As Jorge Segarra pointed out below, NT AUTHORITY\SYSTEM is no longer provisioned as member of the sysadmin server role by setup in SQL 11. I confirmed the behavior on a Denali CTP3 instance I setup from scratch.

We recently worked on a transactional replication setup that involved a very active VLDB and a subscriber being located on a different datacenter. What made it even more interesting is that the WAN link was not particularly fast. In this post, I would like to mention a few of the challenges we faced while and how we got past them, in the hopes that our experience can help you in your future endeavours.

The Problem with Slow Distribution Servers

If your distribution server is slow, your replication performance will tank. You will get behind on transactions and might not ever catch up. In our scenario we had a distribution server that was outdated. The server was running Windows Server 2003 and SQL Server 2005. In our case, this was the biggest issue. After we moved to a new distribution server that was running Windows Server 2008 R2 and SQL Server 2008 R2, our performance increased greatly. One of the biggest benefits in moving to Windows Server 2008 R2 is the set of enhacements made to the TCP/IP stack - particularly send/receive TCP windows. For more information, see this article on Technet.

Careful with WAN Accelerators

While WAN accelerators can be fantastic in a myriad of scenarios, in our testing we noticed that the device wasn't really optimizing replication traffic - and it was actually causing latency. With the help of our Networking team, we made sure that traffic from the distributor to the subscriber was skipped by the WAN accelerator. Obviously, your mileage will vary - so test accordingly.

Use Pull Subscriptions

Our initial setup was done using a Push subscription. This was a big mistake. While Push subscriptions are easier to setup and maintain, their performance across WAN links is plain dismal. The MSDN team at Microsoft put out a great White Paper on Geo-Replication Performance which is, in my opinion, required reading for replication to other datacenters. We saw huge performance gains when we switched to Pull. Orders of magnitude faster. Put simply - never use Push subscriptions across WAN links.

One of the design decisions made in our scenario that I would like to point out: we intentionally kept the distribution database near the publisher (i.e., on the same datacenter) - the reason behind this is simple: if your level of confidence in your WAN link isn't that high, the concern becomes the Log Reader agent and getting the Transaction Log to clear reliably and constantly.

Initialize the Subscriber from a Backup

With a WAN link and high latency involved, initialization of the subscriber from a backup is your best bet. We saved ourselves a lot of headaches by doing it. Creating and transferring a snapshot of a VLDB is out of the picture when you're concerned with WAN latency. In our scenario, the publisher was running SQL Server 2005 and backups were being taken using LiteSpeed. We transferred the most recent full backup to the remote datacenter using robocopy (could have used FTP also) plus the latest differential taken after changing the properties of the publication to allow initialization from backup. Restored at the subscriber using LiteSpeed tools, and then used the Extractor utility to create native-format backup files to initialize from backup. This is because you cannot initialize from a LiteSpeed backup, as SQL needs to read LSN information from the backup file and it uses a system stored procedure for that purpose. Here are some tips: you only need the first backup file created by Extractor to initialize. Also, you don't have to initialize with a differential backup - you can use a T-Log backup just as well.

Thanks to Adam Machanic (Blog | Twitter) I now have a new home for a blog. SQLPS.com is no more.

It is truly an honor to be able to blog here in SQLblog.

Expect posts ranging from very technical to not technical at all. I hope with my posts I can give back a little to the SQL Server community who has helped me grow my career so much. Now that I have opened the doors to this blog I ask you: Is there anything you would like to see me discuss in this space?