A good sex story is all about hot people doin' it in awesome ways. Boy meets girl. They fuck, etc.
But a great sex story is all about what's stopping the boy from doing the girl, or vice versa. Boy meets girl. Boy wants to do girl. But there's a problem! The problems, the speed bumps on the road to love, the things the characters must overcome to be together are what makes a story interesting.
If a character doesn't have any problems on the way to the victory, there's no tension, and the whole thing is boring. Trust me. We've all seen the descriptions of tits and asses, dicks and pretty faces a thousand-thousand times. Most people just skip those when you write them. And the insert Tab A into Slot B stuff just isn't gonna zing us either, at least not without build-up.
If you want to write something interesting, if you want the reader to care, throw problems at your characters. Do everything you can to stop them from having sex!
Here's a list of all the problems your characters might have to overcome on their way to sexual bliss. Always add at least one in every story. This list is all about inspiring ideas in that naughty little head of yours. Use these as seeds and see what you come up with.
In this scenario, one or both participants are in a position or situation that makes having sex wrong, unethical, or just plain risky.
Student/teacher
Boss/employee
Minister/parishioner
Cop/criminal
Doctor, nurse/patient
Babysitter/kid's parent
A friend's significant other (knowing)
Incest
Adultery
Interracial couples in darker times
Space alien/Earth girl
The list goes on and on. The possibilities are nearly endless. In fact, some of these relationships are so taboo, that couples often use them in role-playing when they want to "spice things up" in the bedroom.
The important thing is, these two people should absolutely not be fucking for some very important societal reasons. And the more you emphasize those reasons, the more you build up what the consequences are, the hotter the sex will seem when they finally give in.
More importantly, the more risk you build up in the scenario, the more amazing the lover themselves will seem. A rich and successful character isn't gonna risk everything they've build just for some dumpy little nobody. It would have to be someone glorious, and the experience itself would have to be mind-blowing in order to make it worth the risk.
In this scenario, it could be anybody, from a bored married couple to an international superspy and a sexy double agent. The point here is where they do it. A risky location adds an element of danger to the event that gets the adrenaline going and cranks the sensory alertness to maximum. The climaxes here are mind-blowing. In fact, some people actually get addicted to this kind of sex.
The risky location scenarios are divided into two categories. There are locations that are dangerous or scary in and of themselves.
on a cliffside halfway up a mountain
in a tiger cage at the zoo
on a rooftop with a thunderstorm rolling in
in a haunted house or cemetery
on a raft after a shipwreck with sharks circling
in a phonebooth during a city-wide riot
during an apocalypse of some sort
As you can see, the environment itself creates the danger in these scenarios, and you'd have to be really fucking turned on to even think about sex in these situations. How bad-ass would the characters have to be to even try it?
And then there's locations that are dangerous because somebody could come along at any moment and catch the characters in the act.
in a bathroom in a public place
in the backseat of a car
in a classroom
on a balcony with the whole city potentially watching
backstage during a live performance
in the broom closet at work
on the living room couch with parents due home any minute
Here, the danger is in some authority figure walking in on the characters, or just somebody who could potentially cause trouble. But you have to make sure the characters have something very important to lose, if they're caught. You have to establish how much trouble they could get in. Even if they themselves don't even realize it, the reader should know.
In this scenario, a character is considering having sex with somebody who's just generally bad news or potential trouble. The main character may or may not know the character is trouble, but the reader should know, or at least sense that they are.
a total stranger
a friend's significant other (unknowing)
the story's main antagonist character
a player or abuser type
someone using sex for an ulterior motive
character with a sketchy past
a rapist, murderer, or kidnapper
This is not the same as the inappropriate relationship scenarios. The couples here are perfectly free to bang each other all they want with no moral or ethical consequences. The danger here is that the partner themselves poses some risk. And it's a risk the reader is all too aware of, and they can't stop reading until they find out what happens.
If the character is aware of the danger, then it's the sex itself that's the turn-on. She met a random stranger on a train and decided to fuck him right there and then. The fact that she's just gotta have it in spite of the risks is what's hot here.
If the character is unaware of the danger, then it's the situation that's exciting. The sex is secondary. The reader will be more interested in whether or not the shit hits the fan, than in how amazing the orgasm was. They'll keep reading either way, but don't get bogged down in the wrong details.
Another reason to potentially say no to sex is an activity or position that's "wrong" in itself. But in this situation, you build up the idea that the character is uncomfortable with or afraid of the activity in question. There just won't be much tension around certain things with a character who's "open to anything".
The list of taboo exploits can be anything from innocent play to hardcore raunchiness. It all depends on what your characters feel is "too much" and why.
Nudity
Touching, feeling, petting, masturbation
Sex at all, loss of virginity
Unprotected/unsafe sex
Blowjobs/oral sex
Dirty talk
Anal
Sex toys
Threesome
Group sex
Partner swapping
BDSM
Gangbangs
In all these situations, the more taboo and forbidden you can make these things seem to one or both characters, the more exciting they'll be when they're finally indulged in. With the right about of buildup, even a girl showing a boy her naked boobs can seem orgasmic. You should establish what's going on inside the characters that makes these things seem so risky and wrong.
In some case, you can add tension by having a character who's only pretending to be averse to these things, playing an innocent role, and slowly, hesitantly giving in. In these scenarios, it's their partner who gets the thrills. The reader gets the intrigue of seeing if the character can pull off the innocent act.
The last and most common problem to overcome on the road to amazing sex is the situations where, for whatever reason, one of the partners is just not interested. Then the whole story becomes a game of seduction. The reason for the disinterest can be any of the above four categories, but here, we're assuming none of the others apply, and the desired partner is "just not into you".
Not their type
Friendzoned
Unattractive/no chemistry
Recent breakup
Scared of intimacy
Saving it for someone special/specific
In love with someone else/not in love with you
These stories can be the most intense of all, depending on the situation. Here the tension lies in the question of whether the character will successfully seduce the other in the end, and how they pull it off. The bigger the challenge you give them, the better the pay-off will be.
These are also usually the best stories of all because one or both characters has to change in some way before they win in the end, and the change is rarely something easy or flippant. To overcome these more internal hang-ups, characters have to change in some deeper and more profound way. And if you do this right, the story becomes so much more than a stroke piece. It becomes a lesson in life.
I'd go so far as to say that even if you use one of the first four situations in this list, you should also add this fifth one as a secondary hurdle for the characters to overcome. It'll just make for a more rewarding journey for the reader.
In all these situations, the important thing is to establish very clearly that there is a cost to the victory in the end. Something could go badly wrong for one or both participants. Lives could possibly be ruined.
And then you establish that, in spite of the risks, that person is just so incredible that they're worth the cost, and then some.
And, as in any story, the most important thing of all is that the reader actually cares what happens to the characters. If they don't, any cost and reward you try to impose will be irrelevant anyway.
It's not who's having sex and how amazing it is that makes a story great. It's about the challenges, the obstacles, the frustrated desire. Before you begin your next story, ask the fundamental question: what's stopping these people from having sex?