"login failed for user " on SQL Azure from azure hosted web application

Question

I have an Azure ASP.net MVC web application which uses a SQL Azure db. It was working fine for some months (someday for some reason Azure changed the name of the server and I had to set that again but everything works fine), but now it does not work.

First I thought, they may have changed the server name again, but I opened my SSMS and after resetting the password it connected again. So I change that password in my application connection strings and test everything worked, and yes, debug and tests work fine. So I published, but once published the application throws error

Login failed for user 'my_username'

I check on this post Azure SQL Database "Login failed for user" in application, but works fine in SSMS which seemed similar but I can not do that without getting errors.

I changed my connection string from this

Server=tcp:SERVER_NAME.database.windows.net,1433;Database=my_db;User ID=my_username@qtdhcrrxmi;Password={my_password}; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

to this

Server=tcp:SERVER_NAME.database.windows.net,1433;Initial Catalog=my_db;Persist Security Info=False;User ID={my_username};Password={my_password};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

as it is shown in the Azure portal, in the database connection strings. I only removed the attribute

 Data Source=SERVER_NAME.database.windows.net;

I also checked that publish procedure does not change connection strings.

But it still works on debug and on SSMS but not on production.

Answer 1

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.

Answer 2

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.

Answer 3

I came across the same error message but solved it in a different way. My big problem with creating a new database was that when I created a new web app for azure from Visual Studio Solutions, it requests of you to create a new username and password for that database. However, that database is likely already tethered to an existing database server, so directly after choosing a username and password, you have to reset the server’s password to match what you just created.

(Or you can find out what your existing azure server password is and make sure the new one is no different.)

Visual Studio ---> Solutions ---> Web-App Project ---> (right-click) Publish -----> Profile ----> Microsoft Azure App Service ---- New [Start-Here]