First of all, having web.config contents unencrypted means anyone who can access the service package can access the web.config contents - and that can be more people than you want. For example, if you have an automatic build anyone in your company who sees the build results will see that data. You decide whether that's acceptable.
Next, don't forget that there're vulnerabilities in software. It might happen that at some point a vulnerability is found in IIS that allows for easy download of web.config.
Next, if you ever happen to have customErrors turned off and you have something misconfigured in web.config it might happen that whoever does an HTTP request to your web role will see an error message from IIS saying that this and that is misconfigured in web.config and showing part of web.config where the misconfiguration happened. That might happen to expose your secret - here's an example of similar exposure. Not very probable, but technically possible.
Finally, with all respect to all cloud providers you don't really control how data is being handled at datacenters. They might throw away an undestroyed disk or some employee may be corrupt and your service package may leak. Not very probable, but technically possible. If you ask questions like this one (and that's a very good question to ask) you should account for this risk too.
So as usual there's no absolute security. All you can is just raise the bar. Storing connection string in encrypted form properly certainly raises the bar.
You might also be interested in answers to this related question.
