The Connect agent requires outbound port 443 (https). The agent installer should take care of this for you. More info on this here.
The VPN tunnel itself is IPSec. More details here.
All traffic over the Connect tunnel is IPv6.
To configure the network, you basically install the Connect agent on each local (on-premises) machine. Then, in the portal, you configure your Connect Group. This is a nice security feature: Even if someone somehow acquired your unique Azure Connect installer (which has keys embedded in it), it would be useless: You need to explicitly add nodes to your Connect Group.
Going one step further: Let's say you only wanted your Worker Role to have access to a local SQL Server database, not the Web Role. You can do that: Just add the Worker Role, and only that role's instances would have connectivity to the on-premises SQL Server.