I'd be lying if I said sex scenes weren't important. However, I think what many people fail to realize is that if you've played one game, chances are you've played numerous games. How do you entertain someone who's played dozens, or even hundreds of other games? Do you really expect a game developer to be able to create a unique and engaging scene that we've never seen before?
So, to answer the initial question, story is important. More important than sex scenes. However, it isn't the only factor at play. I believe there are three major components which make a good adult game. The first two being plot and characters, as this is fairly obvious for any form of entertainment. However, I could also argue that characters are more important to adult games than other mediums.
The final factor is "concept". On a basic fundamental level, adult games are effectively "fantasy". If plot refers to the story, than "concept" refers to the general idea of the fantasy. What makes a good concept is extremely subjective, arguably more-so than plot and characters. While the majority of people have a general idea of what makes a good character or story, it comes down to the individual on what makes a good "concept".
So, while I believe plot to be extremely important, it isn't the only factor. You can have a game with a shitty or nonexistent story, but have great characters and a decent concept and still make a great game. If you have boring characters but great plot, it's the same thing. It all comes down to having at least one of the three. As long as you have this, I believe a game will find it's audience.