Plenty of areas around to photograph the trains, and most are from spaces open to the public. Views can be had of various equipment (not so much freight though) from the New Haven-Union Station platforms. Another spot is about 2 miles north from State Street platforms. Cedar Hill in North Haven is a large CSX/CT Southern/P&W freight yard which can be viewed from the area around Universal Drive in North Haven (near Circuit City and Home Depot). Any commuter platform between Stamford and New Haven is also good - you'll be treated to a ton of passenger traffic in a short amount of time, and an occasional freight.
I've never had any problem taking pictures from any of these areas. And that has even been in the post 9/11 world. One engineer joked with me at Stamford and said that he wanted copies of my pictures for himself
As long as you're in public areas and don't trespass or go where you're not supposed to, you'll be fine. If you're into historic equipment, check out the various railroad museums/tourist operations in the area: the Naugatuck Railroad in Thomaston, Valley Railroad/Essex Steam Train in Essex, Connecticut Eastern RR Museum in Willimantic, or the Danbury Railway Museum in Danbury.
As for big time freight activity, there isn't much in Connecticut, not like in our neighboring states.
Hope this helps!
-Jay Hogan