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.
Best Answer
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.