As long as I've been using ASP.NET (which has been some number of years now) I've never liked the pattern of storing connections strings (nor most application settings) in Web.config.
Since configuration-specific config transforms were introduced, this situation has been vastly improved upon, but it's still not ideal. Of course places like AppHarbor might provide workarounds, but they're still just that - workarounds (as I feel are the
What could help you in Orchard is the MultiTenancy module. Each site you set up will create a new folder in App_Data/Sites, each with its own settings.txt. So you can have a separate "live" config specifying your actual domain name for the host. This means
you can manage settings for multiple sites without having to rely on any external system such as the one provided by AppHarbor. I haven't really done much experimentation with the MultiTenancy feature and there are potential problems (for instance, just importing
your dev database won't work since content and settings seem to be tied to the tenant name).
I do think it would be nice if Orchard supported reading a connection string from Web.config to support both scenarios; however this wouldn't translate very well to the multi-tenant setup.