This McCool Travel article lists fun places to kayak in US, a few of which really surprised me! Pretty much any place with water is a great place to kayak.