"login failed for user " on SQL Azure from azure hosted web application
The problem turned out to be in the connection strings. There are some things you need to consider.

First of all there is an option in the publish wizard in Visual Studio in the Settings tab called Use this connection string at runtime (update destination web.config), that for some reason, checked or not, does not seem to work for me. But this may be an option in the first place.



Another place were a connection string is set is in Azure management portal. In your web application settings, in connection strings, you can check which connection string is effectively used by your application. In my case, regardless of the publish wizard settings I used, this connection string was wrong. I just deleted and created it again using my database username and password and everything works again.