Sql Azure Connection Failed

Question

An application hosted in Azure is raising this exception when trying to open connection with Sql Database:

An exception has been raised that is likely due to transient failure. If you are connecting to Sql Azure database, consider using SqlAzureStrategy.

I tried to turn firewall off, but it didn't work.

I'm able to connet to sql database tthrough sql management studio, but the web app isn't.

It uses EF6 to connect with database. Here is the peace of code, which raise error:

var usuario = Db.Usuario.Where(x => x.Login == login).FirstOrDefault();
if (usuario != null && usuario.ProfissionalID.HasValue)
{
  usuario.Profissional = Db.Profissional.Where(x => x.ProfissionalID == usuario.ProfissionalID).FirstOrDefault();
}
return usuario;

I saw in Microsoft Article, a recomendation to use SqlAzureStrategy to try reconnect when first connection attempt fails:

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

But it doesn't seem to be the solution of my problem, because when I implemented this, I got this error message:

Maximum number of retries (1) exceeded while executing database operations with 'SqlAzureExecutionStrategy'.

My connection string: <add name="Entities" connectionString="metadata=res://*/Context.ControleHorasCont‌ext.csdl|res://*/Con‌text.ControleHorasCo‌ntext.ssdl|res://*/C‌ontext.ControleHoras‌Context.msl;provider‌=System.Data.SqlClie‌nt;provider connection string="data source=XXX;initial catalog=XXX;persist security info=True;user id=XXX;password=XXX;MultipleActiveResultSets=True;App=Entity‌Framework"" providerName="System.Data.EntityClient" /> — Otavio Camargo
What do you mean you tried turning the firewall off? Do you mean you added your IP address to the exceptions? Is this app running in Azure when you get the error? — juunas

Martyn C Martyn C · Accepted Answer · 2016-11-04T19:57:09

I don't believe this is a database issue otherwise you would see a very different error which actually states the IP address is not allowed through the firewall.

Instead I would try looking at this post from Markus Wagner where he talks about the maxRetryCount and maxDelay parameters. For me that would be a good starting point to ensure your code is properly engineered towards working with these classes.

Secondly a number of best practices are published around working with the Azure SQL service. I would be looking to make sure you meet all of these best practices and look to tune where possible. I would be looking at:

Minimising network latency
Reducing network usage by introducing application level caching
Managing the network connection, keeping it open as short as possible
Tune your SQL
Implement retry logic

Sql Azure Connection Failed

1 Answers