Home » SQL » Page 3

SQL

Fixing Backup Exec with Hisecweb installed

If you run your web servers on Windows under IIS, you’d better install the Hisecweb security template unless you want to find yourself hosting a warez site.

But Hisecweb breaks Backup Exec. So what do you do when upgrading to Apache and Linux isn’t a solution?The problem is that Hisecweb makes the system state (shadow copy components in Windows 2003) and SQL server not show up in the selection list. Not only does it not show up in the selection list, Backup Exec cannot find the resources. So backups fail, and if you have to restore from them, you won’t have the registry or a number of system files, which vastly reduces the value of your backup.

The solution is to tell Backup Exec not to use null sessions on those components, which seem to be one of the many things disabled by Hisecweb. On the server being backed up, go into Services and disable your Backup Exec Remote Agent. Now, fire up Regedit. Navigate to HKLM\Software\Veritas\Backup Exec\Engine\NTFS and locate the key called Restrict Anonymous Support. Set this value to 1. Close the registry editor and restart the Backup Exec Remote Agent service.

SQL Server and the system state or shadow copy components should now show up in the selection list for the server you just changed.

This registry hack can also fix visibility problems when the two machines are on different sides of a firewall.

Using your logs to help track down spammers and trolls

It seems like lately we’ve been talking more on this site about trolls and spam and other troublemakers than about anything else. I might as well document how I went about tracking down two recent incidents to see if they were related.
WordPress and b2 store the IP address the comment came from, as well as the comment and other information. The fastest way to get the IP address, assuming you haven’t already deleted the offensive comment(s), is to go straight to your SQL database.

mysql -p
[enter the root password] use b2database;
select * from b2comments where comment_post_id = 819;

Substitute the number of your post for 819, of course. The poster’s IP address is the sixth field.

If your blogging software records little other than the date and time of the message, you’ll have to rely on your Apache logs. On my server, the logs are at /var/log/apache, stored in files with names like access.log, access.log.1, and access.log.2.gz. They are archived weekly, with anything older than two weeks compressed using gzip.

All of b2’s comments are posted using a file called b2comments.post.php. So one command can turn up all the comments posted on my blog in the past week:

cat /var/log/apache/access.log | grep b2comments.post.php

You can narrow it down by piping it through grep a bit more. For instance, I knew the offending comment was posted on 10 November at 7:38 pm.

cat /var/log/apache/access.log | grep b2comments.post.php | grep 10/Nov/2003

Here’s one of my recent troublemakers:

24.26.166.154 – – [10/Nov/2003:19:38:28 -0600] “POST /b2comments.post.php HTTP/1.1” 302 5 “https://dfarq.homeip.net/index.php?p=819&c=1” “Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20031007 Firebird/0.7”

This line reveals quite a bit: Besides his IP address, it also tells his operating system and web browser.

Armed with his IP address, you can hunt around and see what else your troublemaker’s been up to.

cat /var/log/apache/access.log | grep 24.26.166.154
zcat /var/log/apache.access.log.2.gz | grep 24.26.166.154

The earliest entry you can find for a particular IP address will tell where the person came from. In one recent case, the person started off with an MSN search looking for information about an exotic airplane. In another, it was a Google search looking for the words “Microsoft Works low memory.”

You can infer a few things from where a user originally came from and the operating system and web browser the person is using. Someone running the most recent Mozilla Firebird on Linux and searching with Google is likely a more sophisticated computer user than someone running a common version of Windows and the version of IE that was supplied with it and searching with MSN.

You can find out other things about individual IP addresses, aside from the clues in your logs. Visit ARIN to find out who owns the IP address. Most ARIN records include contact information, if you need to file a complaint.

Visit Geobytes.com IP Locator to map the IP address to a geographic region. I used the IP locator to determine that the guy looking for the airplane was in Brooklyn, and the Microsoft guy was in Minneapolis.

Also according to my Apache logs, the guy in Brooklyn was running IE 6 on Windows XP. The guy in Minneapolis was running Mozilla Firebird 0.7 on Linux. (Ironic, considering he was looking for Microsoft information.) It won’t hold up in a court of law, but the geographic distance and differing usage habits give at least some indication it’s two different people.

My brief experience with a Proliant ML570 server

Last week, I built a Compaq HP Proliant ML570. Quad-CPU, 1.9 GHz Xeon, 2 gigs of RAM, and about 200 gigs’ worth of 10K RPM SCSI storage in two RAID arrays.
Yes, being one of the first people to see a $20,000 computer and being the one who gets to take it apart to install the optional add-ins is my idea of fun.

Opening it up shows this is a real, honest-to-goodness server, not just any old commodity motherboard slapped into a rack-mount chasis or a case with big casters on it. The memory is on a riser card. There are standby slots on the card, so that if as many as two DIMMs fail, the backups take over. The riser cards are hot-pluggable, but of course how the computer handles you hot-plugging the memory depends on the operating system.

Most of the PCI-X slots are also hot-pluggable. They’re 64-bit and run at 100 MHz.

Of course, the hard drives are hot-pluggable as well. Which brings up the RAID stuff. The drives are Ultra320 SCSI. There are two drive cages. The only Ultra320 RAID controller HP is currently offering (remember, this is May 2003 as I write) was a single-bus. Well, there’s a second bus, but it’s external.

If you put in two of these controllers, you get the full bandwidth of the bus but you can’t stripe across the buses, which you will probably want to do for performance, and almost certainly will have to do in the future for expansion. The only two-bus RAID controller HP was offering was an Ultra160. So we bought that. Under the best-possible circumstances, a single 15K RPM drive can’t quite deliver 80 MB per second. So with two of the newest 15K drives on an Ultra160 bus accessing the very front of the disk, you’d still have a little bandwidth to spare.

These particular Fujitsu 10K RPM disks deliver between 40 and 70 MB per second under optimal conditions. So the Ultra160 controller isn’t a huge liability. But we’ll be ordering more of these, and if HP delivers an Ultra320 controller that meets our needs, we’ll get one.

It runs Linux really, really fast. I compiled a kernel in about 4 minutes, without doing anything special to it. Linux was only on it for a few hours though, while I waited for its RAID controller to come in. Then I installed Windows 2000 and SQL Server 2000.

It’s nice. But with nearly 8 GHz worth of CPU power onboard, it had better be.

Confessions of a SQL 7 junkie

My name is Dave, and I’m a Microsoft junkie. So are the people I hang out with every day at work. We’re all junkies. We’re addicted to the glamor drug of Microsoft SQL Server 7.
I’m still trying to recover from the nightmare that is Microsoft SQL Server.

You see, I have a problem. My employer and most of its clients rely heavily on SQL Server. SQL Server is a touchy beast. We have some servers running completely unpatched SQL Server 7, for fear of breaking a client’s application. No, I absolutely will not tell you who my employer is or who those clients are.

That makes us, in Microsoft’s eyes, socialism-loving pinko Commies, since we won’t migrate to SQL 2000. Unfortunately, SQL 2000 isn’t completely compatible with SQL 7. So we’re forced into being pinko Commies.

Part of the reason SQL Slammer hit was because of the touchiness of the service packs and hotfixes, and part of it was the difficulty in installing them. The hotfix that would prevent SQL Slammer requires you to manually copy over 20 files, mercifully spread out over only two directories. But it takes time and it’s easy to make a mistake. So Microsoft released a SQL 2000 patch with a nice, graphical installer. But the pinko Commies like me who still use SQL 7 have to manually copy files.

Now, SQL 7 isn’t vulnerable to SQL Slammer, but it has plenty of security flaws of its own. And there’s one thing that history has taught us about viruses. Every time a new virus hits, a game of one-upmanship ensues. Similar viruses incorporating new twists appear quickly. And eventually a virus combining a multitude of techniques using known exploits appears. A SQL Slammer derivative that hits SQL 7 in one way or another is only a question of time.

Someone asked me why we can’t just leave everything unpatched and beef up security. The problem is that while our firewall is fine and it protects us from the outside, it doesn’t do anything for us on the inside. So the instant some vendor or contractor comes in and plugs an infected laptop into our network–and it’s a question of when, not if–we’re sunk. Can we take measures to keep anyone from plugging outside machines into our network? Yes. We can maintain a list of MAC addresses for inside equipment and configure our servers not to give IP addresses to anything else. But that’s obstructive. The accounting department is already supremely annoyed with us because we have a firewall at all. Getting more oppressive when there’s even just one other option isn’t a good move. People in the United States love freedom and they get annoyed when it’s taken away, even in cases that are completely justifiable like an employer blocking access to porn sites. But in a society where sysadmins have to explain that an employer’s property rights trump any given individual’s right to use work equipment for the purpose of seeing Pamela Anderson naked, one must be picky about what battles one chooses to fight.

In a moment of frustration, after unsuccessfully patching one server and breaking it to the point where SQL wouldn’t run at all anymore, I pointed out how one can apply any and every security patch available for Debian Linux at any instant it comes out with two commands and the total downtime could be measured in seconds, if not fractions of a second. And the likelihood of breaking something is very slight because the Debian security people are anal-retentive about backward compatibility. The person listening didn’t like that statement. There’s a lot more software available for Windows, he said. I wondered aloud, later, what the benefit of building an enterprise on something so fragile would be. Jesus’ parable of building a house on rock rather than on sand came to mind. I didn’t bring it up. I wasn’t sure it would be welcome.

But I think I’ll keep on fighting that battle. Keeping up on Microsoft security patches is becoming a full-time job. I don’t know if we can afford a full-time employee who does nothing but read Microsoft security bulletins and regression-test patches to make sure they can be safely deployed. I also don’t know who would want that job. But we are quickly reaching the point where we are powerless and our lives are becoming unmanageable.

Such is the life of the sysadmin. It’s a little bit of a rush to come into crisis situations, and a lot of my clients know that when they see me, there’s something major going on because they only see me a couple of times a year. In the relatively glamor-less life of a sysadmin, those times are about as glamorous as it gets. And for a time, it can be fun. But when the hours get long and not everyone’s eager to cooperate, it gets pretty draining.

Microsoft’s Slammer pain is good for everybody

SQL Slammer hit where it counts, including HP–historically, one of the biggest Microsoft supporters around–and Microsoft itself.
This is good. Really good.

Microsoft is one of its own biggest customers. Part of this is due to one of the worst cases of not-invented-here syndrome in the industry, and part of it is marketing. If Microsoft can run its enterprise on mostly its software, its argument that you ought to be able to run all of yours on it is much stronger.

When Microsoft feels our pain, that’s good. Problems generally get fixed. Not necessarily the way we want them fixed, but fixed. When Microsoft for whatever reason doesn’t feel our pain, things languish. Witness the development of Windows 9x late in its lifecycle, after Microsoft was able to run everything internally, including laptops, on Windows 2000. While Windows 98SE was fairly good, all things considered, Windows Me was so horrid that one of my magazine editors wrote me and asked me the least painful way to escape it. Windows Me was fast, but it was less stable than 98SE.

What happened? The patches were difficult to install, poorly tested, poorly documented, and it was extremely difficult to know when you needed them. Microsoft’s inability to keep its own servers sufficiently patched illustrates this.

Several things are likely to happen now. People will take non-Microsoft solutions more seriously and, in some cases, deploy them. A not-as-homogenous Internet is good for everybody. Meanwhile, Microsoft will be cleaning up its act, making it easier to ensure that their patches actually work and can be deployed with reasonable ease.

I still think we’ll have disasters like SQL Slammer again. But this is a good step in the right direction.

Update Expert is something for your NT power toolbelt

Now that most of the fun of SQL Slammer has died down, let me present you a tool that might have prevented it: Update Expert.
Basically what it does is query all the NT/2K/XP machines on your network, then query its own database, and tell you what Microsoft patches are available to you. Then it’ll help you download them and push them out. It even figures out the order to install the patches (important) and knows which ones can be installed together to minimize the number of reboots. It’s a lot better than running Windows Update on every PC on your LAN.

I have no connection with St. Bernard Software. My former boss used it and swore by it; when I changed jobs, I introduced it to my new boss. I recommend it because I use it myself.

SQLSlammer takes its toll on the ‘Net

If the ‘Net was slow today, it was because of a new worm, called SQLSlammer, that infected vulnerable Windows servers running Microsoft’s SQL database.
The exploit it used was old, but it was made possible because Microsoft’s cumulative hotfixes not being cumulative, and one of the patches not included, if applied afterward, reverted the server back to its vulnerable state. This was not mentioned clearly in the documentation for the hotfixes. Probably Microsoft didn’t know–until it was too late.

But in some cases it’s not Microsoft’s fault. Try getting a pointy-haired boss to give you 15 minutes’ downtime per server so you can roll necessary security patches across your enterprise. Since many people who ultimately make IT decisions never actually administered a Windows server in their careers, a lot of bad decisions get made and servers stay unpatched, as a matter of policy, either out of fear that a patch that closes a security hole might create a new bug, or that some remote VPN user in Kenya might be trying to work during that proposed scheduled time.

Linux got a bad rap in the security press last year because it allegedly had more security vulnerabilities than Windows did last year–never mind that a vulnerability in, say, BIND would get counted several times because it’s included in every Linux distribution, so whereas a vulnerability in IIS would get counted once against Windows’ total, a vulnerability in BIND might get counted 8 times.

We’ll ignore that. Fine. Linux has a larger number of security problems and vulnerabilities than Windows does. Fact. Undeniable. Fine. Answer this question then: Has any worm affecting Linux ever had the devastating effect that SQLSlammer had? That Nimda had? The most notorious worm that affected Linux was called Slapper. Do you remember it? More than 60% of the servers on the ‘Net run on Apache. A worm affecting Apache should have been huge. It wasn’t.

Statistics are, well, statistics. Just because I can find you a set of numbers that suggests the sky is pink doesn’t make it any less blue.

Why anyone, anywhere, has a Windows server on the ‘Net with anything more than port 80 exposed is beyond me.

Trustworthy Computing? Nice buzzwords. Billy Gates has yet to put any meaning into them.

And incompetence rises. Managers didn’t learn from Nimda, so they won’t learn from this either.

Great combination. What does it mean? History will repeat itself. Something like this will happen again. Probably sooner rather than later.

New stuff and old stuff

New stuff. Hmm. I think I need to adopt a new stylebook
Old stuff. And, thanks to some SQL heroics by Steve DeLassus, a couple hundred vintage posts from about a six-month span in late 2000 and early 2001 are now online here. I liberated them from my ancient site at editthispage.com (which, I guess, was Silicon Underground version 2.0). This is two generations later. As old entries gain attention I’ll give them categories and proper titles. A lot of the stuff’s obsolete but I’m sure it’ll interest someone. Last I checked, the old site was getting a hundred or two hits per day consistently.

New stuff. And speaking of old site incarnations, once I’ve decided exactly what mods I want to install and use, I’ll flip the switch on the new site, based on the latest version of b2. The most obvious improvement for most readers will be the spiffy new calendar. It’ll also bring in trackbacks and pingbacks for interaction with other blogs. It also makes some changes to make search engines happier.

Linkfest Friday…

Let’s start things off with some links. Web development’s been on my mind the last few days. There’s a whole other world I’ve been wanting to explore for a couple of years, and I’ve finally collected the information that’ll let me do it.
Redirecting virus attacks — Your neighbor’s got Nimda? Here’s how to get his IIS server to quit harassing your Apache server. (Suggests redirecting to a bogus address; I’m inclined to redirect either to 127.0.0.1 or www.microsoft.com, personally.)

DJG’s help setting up MySQL. Apache, MySQL and PHP are a fabulous combination, but bootstrapping it can be a painful process. People talk about writing a sendmail.cf file as their loss of innocence, but I’ve written one of those and I’ve tried to set up the LAMP quartet. The sendmail.cf file was easier because there’s a whole lot more written about it.

Short version: Use Debian. Forget all the other distributions, because they’ll install the pieces, but rarely do they put the conduits in place for the three pieces to talk. It’s much easier to just download and compile the source. If that doesn’t sound like fun to you, use Debian and save some heartache. If you’re stuck with the distro you have, download ApacheToolbox and use it. You’ll probably have to configure your C/C++ compiler and development libraries. That’s not as bad as it sounds, but I’m biased. I’ve compiled entire distributions by hand–to the point that I’ve taken Linux From Scratch, decided I didn’t like some of the components they used because they were too bloated for me, and replaced them with slimmer alternatives. (The result mostly worked. Mostly.) You’ve gotta be a bit of a gearhead to take that approach.

Debian’s easier. Let’s follow that. Use this command sequence:

apt-get install apache
apt-get install php4-mysql
apt-get install mysql-server

Next, edit /etc/apache/httpd.conf. There’s a commented-out line in there that loads the php4 module. Uncomment that. Just search for php. It’ll be the third or fourth instance. Also, search for index.html. To that line, add the argument index.php. If you make index.php the first argument, access to PHP pages will be slightly faster. Pull out any filetypes you’re not using–if you’ll never make an index page called anything but index.html or index.php, pull the others and Apache will perform better.

Got that? Apache’s configured. Yes, the php installation could make those changes for you. It doesn’t. I’m not sure why. But trust me, this is a whole lot less painful than it is under Red Hat.

But you’re not ready to go just yet. If you try to go now, MySQL will just deny everything. Read this to get you the rest of the way.

Once you’ve got that in place, there are literally thousands of PHP and PHP/MySQL apps and applets out there. If you can imagine it, you can build it. If HTML is a 2D world, PHP and MySQL are the third and fourth dimension.

Am I going to be playing in that world? You’d better believe it. How soon? It depends on how quickly I can get my content whipped into shape for importing.

This is the holy grail. My first editing job was doing markup for the Digital Missourian, which the faculty at the University of Missouri School of Journalism believe was the first electronic newspaper (it came into being in 1986 or so). By the time I was working there in the late summer of 1995, it had been on the ‘Net for several years. About eight of us sat in a room that was originally a big storage closet, hunched in front of 486s, pulling stories off the copydesk, adding HTML markup, and FTPing them to a big Unix cluster on the MU campus. We ran a programmable word processor called DeScribe, and we worked out some macros to help speed along the markup.

No big operation works that way anymore. There aren’t enough college students in the world. You feed your content to a database, be it Oracle or IBM DB2 or Microsoft SQL Server or MySQL or PostgresSQL. Rather than coding in straight HTML, you use a scripting language–be it PHP or ASP–that queries the database, pulls the content, applies a template, and generates the HTML on the fly. The story goes from the copy editor’s desk to the Web with no human intervention.

There are distinct advantages to this approach even for a small-time operation like me. Putting the content in a database gives you much more versatility. Some people want overdesigned Web sites. Some want something middle-ground, like this one. Others want black text on a gray background like we had in 1994. You can offer selectable formats to them. You can offer printer-friendly pages. You can even generate PDFs on the fly if you want–something some sites are doing now in an effort to gain revenue. If you have content from various sources, you can slice and dice and combine it in any imaginable way.

I can’t wait.

How Linux could own the education market

How Linux could own the education market. I spent some time yesterday evening working on computers. They were contrasts to the extreme: One, a brand-spankin’ new 1 GHz AMD Duron system with 512MB of RAM and 80 GB of 7200-rpm storage (IDE, unfortunately–but for $800, what do you want?). The other was an elderly AST 486SX/25 running Windows 3.1 belonging to a local teacher who goes to my church.
She teaches kindergarten, and the AST used to be her home computer. When she bought a Compaq Presario a couple of years ago, she took the AST to school. It’s more useful there than in her basement, and there’d be no computer in her classroom if it weren’t for that.

I don’t understand why that is. As much as my sister jokes about it, we don’t exactly live in the ghetto. The school district has money, but it isn’t spending it on computers. Whether that’s a good or bad thing depends on your point of view. The majority of people living in Oakville probably own home computers, so this probably isn’t contributing to the technology gap. But I wonder sometimes how things might have been if I’d been exposed to computers a few years earlier.

I was shocked how much I remembered about Windows 3.1. And I was able to figure out how to get her CD-ROM drive to play music CDs. Don’t ask me how; this was the first I’d messed with Windows 3.1 since 1994 and I’d prefer it stay that way–I was so impressed by Windows 3.1 that I’m one of the 12 people who actually went out and paid money for OS/2. I own actual, retail-box copies of OS/2 2.1, 3.0, and 4.0. And I remember distinctly thinking that her computer has enough memory to run OS/2 at least as well as it runs Windows 3.1…

I also remember distinctly thinking that my employer pays someone $15 a pound to haul better computers than hers away several times a year. We regard 486s as junk; low-end Pentiums may also go out, depending on whether the right person finds out about them beforehand. Usually they work just fine–the problem isn’t the computers, it’s people trying to run Internet Exploiter 6 and Office 2000 on them. They’d run Windows 95 and Office 95 perfectly fine.

But a lot of times we can’t give these old computers away because the licenses for the software that originally came with them are long gone. Old computers are useless without software, so no one would want them anyway.

Now, let me tell you something about kids. Kids don’t care much about the computers they use. As long as there’s software on them, they’ll use them. When I was a kid 20 years ago, I used Radio Shack TRS-80 computers at school. The next year, my family moved, and my new school had Commodore 64s. I couldn’t tell much difference. My next-door neighbor had a Radio Shack Color Computer. They were computers. The Commodores had better graphics, but from a usability standpoint, the biggest difference was where the cartridge slot was so you could change programs. Later on I took a summer class at the local junior college, learning about Apple IIs and IBM PCs. I adjusted smoothly. So did all the other kids in the class. Software was software.

Kids don’t care if the computer they’re using runs Windows or Mac OS or Linux. All they care about is whether there are cool programs to run.

So, businesses throw useless computers away, or they give useless computers to schools so they don’t have to pay someone to haul them away. And schools don’t generally know what to do with obsolete computers that lack software.

Linux won’t run fabulously on old 486s, but Debian with a lightweight window manager like IceWM will run OK. (Let’s face it, Windows 3.1 doesn’t run fabulously on them either–it crashes if you breathe wrong.) I know of a project to clone Oregon Trail on Linux. Great start. How about Sea Route to India? I remember playing that on C-64s at school. It may have been a type-in out of a magazine–I don’t remember where exactly it came from. In these violent times, Artillery might be too controversial, but it taught us early on about angles and forces. Artillery was an ancestor to games like Scorched Earth, but without the heavy-duty nukes. Close wasn’t good enough to win in Artillery. You had to be exact. And no blowing up the mountains between you and your opponents either. You had to figure out how to get over them.

But what about doing homework? By the time I was in the sixth grade, they were teaching us how to use word processors and databases and spreadsheets. AbiWord is a fabulous lightweight word processor. It gives you fonts and spell-checking and good page formatting. (I learned word processing on Bank Street Writer. AbiWord is a far, far cry from that. Frankly, I’d rather write a paper with vi than with Bank Street Writer.) Besides being feature-rich, AbiWord’s been lightning fast on every computer I’ve tried it on. Gnumeric is a nice, fast, capable spreadsheet. I don’t know of a free-form database, but I haven’t looked for one lately either. (I don’t think we need to be trying to teach our 6th graders SQL.)

But what about for younger kids? I remember a program called The Factory. The object was you combined chemicals to make monsters. Different chemicals made different monsters. I seem to remember you played around to see what chemicals would make which heads and torsos and arms. Then the computer started showing you monsters and you had to figure out what chemicals to give it to match them. I also remember a program called Snooper Troops. I don’t remember much else about it, other than it was a mystery and you went around looking for clues, and one of my classmates accidentally formatted the disk one day before any of us had managed to solve it. We couldn’t get the disk replaced, because it was out of print.

And Spinnaker had all sorts of simple titles for younger kids that let them tell stories and other stuff. It seemed cool at the time. But that was almost 20 years ago, so about all I remember was that sailboat logo and some corny theme music.

The other thing about those old days was that the majority of these programs were written in Basic. An ambitious teacher could modify them, to make them easier or harder, or improve the graphics a little. As we got older and learned to program, some of us would try our hand at making changes. You can’t do that anymore with Windows or Macintosh educational titles. Open source can bring all that back too, provided the programs are written in languages like Perl or Python. And it can give cash-strapped schools a way to get computers where kids can use them.

Now I’m wondering what it would take to write something like The Factory in Python…