A friend of mine (PADI instructor and avid kayak diver) and I tried to pilot a kayak diving specialty at UnitedDivers this summer. They happen to keep 3 kayaks which are never used by anybody. We had the owner's blessing, but he didn't cooperate much, since he was very sceptical about the demand for this specialty. All we ended up doing was testing the kayaks at Charles river and in Rockport on a few occasions. Good boats - too bad nobody is using them.
We currently have a plan to launch the specialty next summer. If UDI don't want to do this, we will find another store, which would be more open to the potential of this sport. If anybody has any thought or would like to help - please let me know.
As for north shore kayak diving sites -- there are tons of them. For example, the wall between Hallibut point and Folley is a great diving spot, which is not easily reachable by surface swimming. The Chester Polling is about half a mile from shore (about 20 minute paddling), though I wouldn't try this particular dive from a kayak without tons of equippment, training and surface support.
From Back beach one can reach the Rockport breakwater in less than an hour away. You can park there and use the boulders to gear up! Charles heigh is also close by. I have done this one by kayak - definitely worth the paddling.
There are probably 101 ways you can use a kayak on the north shore.
I am definitely up to doing kayak dives. If anybody plans on going, let me know and I might join you with my kayak.
Kayak diving is an unbeatable way to expand your diving experience!