This month Marlon Ribunal is hosting T-SQL Tuesday #202, and he's asking about that one SQL Server outage you'll never forget. I've had a few, but this one beats them all.
Saturday, January 25th, 2003. My cell phone rang at about 3AM. It was Microsoft calling to tell me what was happening inside my servers.
We had VIP support at the time, which is why I heard it from Redmond before I heard it from my own building. I called our head of IT expecting to wake him -- but he was already on site with his Team, trying to work out what the hell was going on.
What Was Actually Happening
SQL Slammer. First detected on the internet at 05:30 GMT that morning, which put my phone call roughly about three hours into it.
It was a single 404-byte UDP packet aimed at port 1434, and it sent so many copies of itself that servers and routers buckled and network traffic slowed to a crawl. No payload, no user interaction. Just pure replication exploiting a buffer overflow in Microsoft SQL Server 2000 and MSDE database products. Because the worm lived entirely in memory, a restart wiped it, but an unpatched reboot invited it right back in.
The sheer math made it the fastest-spreading worm in history. It doubled its infected host count every 8.5 seconds, tearing through 75,000 victims in under ten minutes.
The tragedy? The patch had been live since July 24, 2002 (MS02-039). It had already been sitting on the shelf for six months on the morning the world woke up to it.
How It Got In
Our firewall held. Port 1434 was not open to the internet and nothing came through the perimeter.
It came in over the VPN. One of our senior developers had a connection open, doing legitimate work, the way he did most weekends. That connection was authenticated, sanctioned, and exactly what we had built the VPN to allow.
Once it was inside, there was nothing between hosts to slow it down. It started with one or two SQL Servers and then it had the entire network.
The Routers Went First
The sheer volume of UDP scan traffic was overwhelming, and the routers went down under it. Not degraded -- our network lead's words were that they had halted to a stop. We couldn't even log in to manage them.
That's a particular kind of bad. The equipment you would use to contain a network problem is on the far side of the network problem. Everything we would normally reach for was unreachable.
From where we sat, the systems went down one by one as the internal network collapsed around them -- until both sites were completely down.
Both Sites
Chicago was primary and New York was the DR site. Financial services, so Monday's market open was not a time that could be negotiated.
We lost both sites.
That is the part I have never forgotten. Geographic redundancy is built for a site going away -- fire, flood, power, a backhoe through a fiber run. It is not built for something that gets into the network and reaches both your primary and DR sites in the same instant. New York wasn't a fallback that weekend. It was a second casualty.
The Recovery
We shut everything down. The IT Team took the routers offline and disconnected the edge switches and distribution routers from the core, to give us a network quiet enough to work in.
Then we recovered from the servers outward. One at a time. More than 400 physical servers across the two sites.
There was no clever fix available. The worm was memory-resident, so the machine came back clean, and it would be reinfected in seconds if we put it back on a network that still had infected hosts. The only way through was to do them in order, keep the clean ones isolated from the dirty ones, and not lose track of which was which at any point during recovery.
The IT Team and the DBA Team worked it together, Saturday into Sunday into Monday. Full recovery by about 4AM Monday morning. We opened when the market did and nobody outside the building knew.
Getting the Servers Back Was the Easy Half
Recovering 400+ servers was the part with a clear finish line. You know when an operating system is back online. The harder problem was everything that had to be true before we could begin trading at Monday's open.
In many cases the state was simply gone. Trader permissions and account data had to be repopulated from backups so that we could account for open orders -- and open orders don't close just because your data center did.
Then came reconciliation with the exchanges, verifying current state before we could open clean. That is not just a database task. It is a full business process, and it had to be done end-to-end before recovery was complete.
The Ashtray
Sometime during that weekend the owner of our company came down to L2 and handed me an ashtray, so I could smoke while working the recovery. Twenty-three years later, that is still the detail I remember first.
What I Took From It
The patch had existed for six months. That is the sentence everyone quotes about Slammer, and it is true, but it was never the interesting part to me.
The interesting part is that our perimeter did its job and it did not matter. Port 1434 was closed to the internet, and the worm still came in over an approved VPN connection opened by one of our most senior developers doing legitimate work on a Saturday. Nobody did anything wrong.
That still happens, and it does not need a worm. I wrote about a SQL Server CVE on Wednesday that lives in SSMS rather than in the database engine. The vulnerable machine is the workstation somebody administers from, and your SQL Server patch inventory never looks at it -- for the same reason a developer's laptop wasn't on anyone's list in 2003.
More to Read
T-SQL Tuesday #202 Invitation -- That One SQL Server Outage You'll Never Forget
MSFT Security Bulletin MS02-039
F-Secure -- Worm:W32/Slammer
SQL Slammer
sqlfingers -- This SQL Server CVE Isn't on Your SQL Server
No comments:
Post a Comment