Knowing this, I've set the "Connection Timeout" correctly on the connection string, to a big enough number, so my connection won't timeout during the execution of the query.
Setting this option seem to be ineffective in some cases, as my code kept getting kicked with a "Connection Timeout" exception. After over 30 minutes of digging around I finally found the culprit. It seems that SqlCommand objects doesn't inherit the timeout configuration from the connection string, rather they have their own 30 seconds timeout default. So in order to overcome this, one should set "command.CommandTimeout=...".
Still didn't find any explanation for this behavior that would convince me it is rational.