All of a sudden getting lots of Wait Operation Time out issues on SQL Azure

Question

Two days ago, with no code changes or changes to the DB, I am not getting a lot (every 5 minutes or so) of errors with The wait operation timed out error with two different underlining full errors on about the pre-login and the other about the post:

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21; handshake=14988; ---> System.ComponentModel.Win32Exception: The wait operation timed out

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=5; handshake=3098; [Login] initialization=0; authentication=0; [Post-Login] complete=7; The duration spent while attempting to connect to this server was - [Pre-Login] initialization=20; handshake=5; [Login] initialization=0; authentication=0; [Post-Login] complete=11003; ---> System.ComponentModel.Win32Exception: The wait operation timed out

I am using Entity Framework and my web site is hosted on an Azure Web App. I have done some digging and most SO questions I find about this are NOT related to Entity Framework but ADO.Net the few posts I found lead me updated from a Basic to Standard (S0) service for the DB and creating a GlobalDBConfig with

public class GlobalDBConfig : DbConfiguration
{
    public GlobalDBConfig()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy(2, TimeSpan.FromSeconds(30)));
    }
}

How can I figure out what else is going wrong and fix it? This is a very simple DB with simple queries and very little traffic to the site (less then 1000 visits a DAY)

Answer 1

We resolved this issue, along with other types of random timeouts on SQL Azure by switching to "contained users". Using server-level logins on SQL Azure can cause issues:

This is not very efficient as in SQL DB master and user can sit on two different SQL servers potentially in two different machines. Also when a server has multiple user databases then master will be the bottleneck in the login process, and under load this may result in high response time for logins. If Microsoft is updating the software on the machine / server then master will be unavailable for a few seconds and all the logins to the user database can fail too at this time (http://www.sqlindepth.com/contained-users-in-sql-azure-db-v12/)

As in your case, I had my doubts because my database was not under heavy load, but switching to contained users made a tremendous difference anyway.

The SQL to create these users is as follows (run this on the database itself, not on the master database as you would for creating server-level logins):

Create user ContainedUser with password = 'Password'
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [ContainedUser]
ALTER ROLE [db_owner] ADD MEMBER [ContainedUser]