Getting Failed connections after storing ConnectionString in Azure Web App Settings as opposed to web.config

Question

Over Christmas I changed our web application (MVC,EF6, SQL Azure) to use a connectionstring setting from Azure's web application property settings rather than in the web.config. This was to serve 2 purposes:

1) More secure

2) Help prevent accidental repointing of apps to incorrect DB.

Since doing this I have discovered 40% of our connections are failing. Now this might be co-incidental with an Azure issue, as when doing this on another SQL Azure server I use, I could not repeat this.

The ConnectionString data that I have entered into Azure is:

ApplicationCS

Data Source=tcp:live1-server.database.windows.net,1433;Initial Catalog=Live-DB;User ID=user@live1-server;Password=12345

EntitiesCS

metadata=res://AppNamespace/app.csdl|res://AppNamespace/app.ssdl|res://AppNamespace/app.msl;provider=System.Data.SqlClient;provider connection string="Data Source=tcp:live1-server.database.windows.net,1433;Initial Catalog=Live-DB;User ID=user@live1-server;Password=12345"

Elmah

Data Source=tcp:live2-server.database.windows.net,1433;Initial Catalog=ElmahDB;User [email protected];Password=9999

I have changed pertinant details for obvious security reasons.

So my question:

1) Has anyone experienced failed connections when using Azure to store Connection Strings for Web Apps.

2) Do my details look correct. I assume they do, since the application will connect, but not all of the time.

This issue may be co-incidental, but to have a "failing connection" issue on a Azure DB Server from the 23rd until now seems a long time if it is caused by an Azure infrastructural issue.

Thanks in advance.

EDIT

I have analysed the event_log via:

SELECT      
        [Date From] = EL.[start_time],
        [Date To] = EL.[end_time],
        [Database Name] = EL.[database_name],
        [Event Type] = EL.[event_type],
        [Event Sub Type] = EL.[event_subtype_desc],
        [Description] = EL.[description],
        EL.event_count,
        [Additional Data] = EL.additional_data
        FROM sys.event_log EL
        WHERE EL.event_type != 'connection_successful'
        AND EL.event_subtype_desc != 'idle_connection_timeout'
        ORDER BY [Date From] DESC

I notice that I am getting a lot of "login_failed_for_user" errors here although my web app seems to work fine, although more slowly. These errors started after I changed the SQL Azure password using the Azure Portal. I cannot tell from the "event_log" what is causing these failures. All the scheduled processes work fine and anyway these are only overnight. I am getting these errors every 5 mins or so. The new password is much stronger ie 20 characters and random. Would this be causing SQL Azure a problem. The intermittent nature of this error is strange.

You didn't specified the error..but you can look at sys.event_log DMV and know causes for failures,by correlating timestamps with failed time stamps — TheGameiswar
The error is "login_failed_for_user". I do not really understand your comment "correlating timestamps with failed time stamps". Thanks for your help thus far. Incidentally I think I may have tracked the caused by a sudden idea which is looking to be correct. Basically I had a deployment slot which was pointing to the same database, but was unused, but had an application setting of "Always On" set to "on" which causes Azure to ping the site which was causing these attempted DB accesses. I stopped this slot, and the event_log is looking good so far. — SamJolly
i meant to say,correlate your login failures timings with time stamps from DMV — TheGameiswar
scroll down to event aggregation section :msdn.microsoft.com/en-us/library/dn270018.aspx — TheGameiswar

David Ebbo David Ebbo · Accepted Answer · 2017-01-04T18:37:56

This does sound coincidental. Either the connection string is bad, and everything should fail, or it's good and it should work identically whether you hard code it in web.config or use Azure settings.

Putting it in Azure settings causes it to get injected into the .NET config system at runtime. At that point, the two cases are basically indistinguishable.

Getting Failed connections after storing ConnectionString in Azure Web App Settings as opposed to web.config

2 Answers