"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.

I struggled for 2 days to figure out why I'm getting a "Login failed for user 'null'" error message even though my connection string is correct and I'm able to connect to the DB server via SSMS.

Turns out I tried to remove connection string in Application Settings in my Azure account. AND IT WORKED.