There are lots of freestanding two-person tents out there. Trouble is, all of them have to be pegged (or staked" as we colonials say) to keep them secure in high wind. But at least that way you can pick and choose your peg points, or even use rocks and nearby trees. So it is easier to pitch a freestanding tent on rocky sites.
Are you after an American or European tent? If the former, Marmots Aeolos 2P (US$335) offers sturdy, lightweight (five pounds, 12 ounces) protection that would serve you well kayaking or backpacking. Ventilation is good but no so much that youll freeze on a chilly night. And its very weatherproof. I also like Black Diamonds Lighthouse ($389), a single-wall tent that uses a fabric called Epic, which has a silicon treatment that makes it very water-repellent and just breathable enough to get interior moisture out of the tent. Ive heard reports from people who sat in one in the pouring rain for days and kept dry. My own Lighthouse has done fine in light rain. And I love its light weight (three pounds, 12 ounces).
European tents tend to put more of a premium on rain protection. A good example is the Vaude Odyssee (US$345), which has a very full rain fly and weighs just over four pounds. And, I cant resist recommending the Hilleberg Nallo 2 (US$495). Its a tunnel tent, meaning you need to find some way to secure the ends. But its super light (four pounds), extremely sturdy, and impervious to moisture. Really a good foul-weather tent.
The 2008 Summer Outside Buyers Guide is now online. From riding to trail-running to camping, get reviews of nearly 400 gear must-haves.