Certainly the most memorable experience in a kayak was a couple of years ago when I suddenly found myself in the midst of about a hundred monster sea lions that didn't act like they were too happy to see me.
I've had a few times when the water conditions got kind of daunting, but the kayak has always turned out to be more confident than the paddler.
That would freak me out for sure.
I think the scariest for me was getting caught out in Florida Bay during a VERY severe thunderstorm. I never felt so small and so exposed to instant death as I did that day.
I can also remember doing a night paddle on the Peace River in Central Florida and not having enough watter to paddle in. While dragging the kayak down the river in the black of night my head lamp illuminated dozens and dozens of reflected red eyes.