Also, why has it spawned an award for bad writing?
It's an unsubtle cheat.
(That doesn't necessarily mean it's not good. Bear with me.)
The author wants to get across that Really Important Things are Happening. He wants to hook you with the beginning of his book. How does he manage to impart the tremendous significance the reader should be seeing right from the start?
Answer: by giving a dramatic, atmospheric, visual. That'll be enough to give readers a striking opening, to get across that Big Stuff Is Happening.
It's a cheat, in this case, because the visual really has nothing to do with the story. It's powerful, but irrelevant - the author is invoking the visual just to draw you in, not because it fits the story or is actually significant.
And it's unsubtle because he hits you over the head with it. "It was a dark and stormy night" - that's just a hair away from writing "It was a dramatic, suspenseful night," which I think you'll agree would be absurd. The author's trying to tell you the story is significant, and doing so quite bluntly, instead of actually demonstrating its interest and significance.
Now, in moderation, those would be legitimate cheats. You can definitely get away with that kind of stuff; often it's to your advantage, as long as the reader doesn't notice outright what you've done. The difficulty is, "Dark and stormy night" is both such an egregious example, and so oft-quoted and overused, that it completely loses its effectiveness on the one hand, and calls fatal attention to its flaws on the other. It's both a cliche (because a stormy night is an effective image) and a useless one (as opposed to cliches which have true power because their core idea is so significant).
Others have responded regarding the contest. I don't think that the tiny snippet is the inspiration for the contest; the full paragraph and Bulwer-Lytton's prose in general are to blame for that.
The opening sentence is bad for a number of reasons. First, aren't all nights dark? Why say that? But it is not so bad that a good writer couldn't have recovered from it and gone on to write a good book. This author did not, however. His works are regarded by most critics as terrible. Pretty much no one but a grad student in English reads a novel like Paul Clifford today.
The contest you refer to is the Bulwer-Lytton contest.
The Bulwer-Lytton Fiction Contest (BLFC) is a tongue-in-cheek contest that takes place annually and is sponsored by the English Department of San Jose State University in San Jose, California. Entrants are invited "to compose the opening sentence to the worst of all possible novels" – that is, deliberately bad. According to the official rules, the prize for winning the contest is "a pittance",1 or $250.
The contest was started in 1982 by Professor Scott E. Rice of the English Department at San Jose State University and is named for English novelist and playwright Edward George Bulwer-Lytton, author of the much-quoted first line "It was a dark and stormy night". This opening, from the 1830 novel Paul Clifford, continues floridly:
"It was a dark and stormy night; the rain fell in torrents, except at occasional intervals, when it was checked by a violent gust of wind which swept up the streets (for it is in London that our scene lies), rattling along the housetops, and fiercely agitating the scanty flame of the lamps that struggled against the darkness."
Anything that is so universally mocked you would probably be wise to avoid, unless you are entering such a contest yourself.
Although the traditional pointer is to Edward Bulwer-Lytton's purple prose, it may be better known in Britain as a school playground story for the unimaginative which starts something like
It was a dark and stormy night and the Captain said to the Mate, "Tell us a story, Mate." So the Mate began, "It was a dark and stormy night and the Captain said to the Mate, 'Tell us a story, Mate.' So the Mate began, 'It was a dark and stormy night ...
but sadly I do not have enough space here to finish it.
It's the opening of the novel Paul Clifford by Edward Bulwer-Lytton, which has become a textbook example of purple prose, an overly extravagant writing style.
The phrase is used repeatedly by Snoopy in his efforts to start a novel, and the final result is naturally an example of purple prose.
One problem is that the sentence is heavy-handed and melodramatic. The dark is a metaphor. The storm is a metaphor. Yeah, yeah, we get it. We're in for a dark and stormy story.
Madeleine L'Engle begins A Wrinke in Time with It was a dark and stormy night. I haven't read it, but I suspect she uses the sentence playfully, deliberately tapping into its long association with heavy-handed melodrama.