Azure WebJob can't connect to Azure SQL

Question

I have a console app running a continuous WebJob. The web app where the Job lives in can successfully connect to an Azure Hosted SQL Database, but the WebJob using Entity Framework Core can't. It always times out with

Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException : 
Maximum number of retries (6) exceeded while executing database operation with 'SqlServerRetryingExecutionStrategy'. See inner exception for the most recent failure. ---> System.Data.SqlClient.SqlException : Database 'xxx' on server 'yyy' is not currently available. Please retry the connection later.

I logged the Conn String and it seems correct. Also the exception stating database 'xxx' and server 'yyy' indicate that the conn string is correct.

Am I missing any setting here?

Janley Zhang Janley Zhang · Accepted Answer · 2018-02-28T09:46:19

"Database on server is not currently available. Please retry the connection later."

There are two types of Azure SQL Database connectivity issues: transient errors and persistent errors. According to your error message, it seems to belong to transient errors. Database reconfiguration and database resource limits all could cause this error. In this article, we could know some steps to resolve transient connectivity issues.

Besides, you could check your connection string, firewall settings, connection code or any other possible causes.

Connection string in app.config file:

<connectionStrings>
    <!-- The format of the connection string is "DefaultEndpointsProtocol=https;AccountName=NAME;AccountKey=KEY" -->
    <!-- For local execution, the value can be set either in this config file or through environment variables -->
    <add name="AzureWebJobsDashboard" connectionString=" storage connection string" />
    <add name="AzureWebJobsStorage" connectionString=" storage connection string" />
  <add name="ConnectionStringName" providerName="System.Data.SqlClient" connectionString="Data Source=tcp:[your azure sql server name],1433;Initial Catalog=[sql database name];Integrated Security=False;User Id=[user name];Password=[password];Encrypt=True;TrustServerCertificate=False;MultipleActiveResultSets=True" />
  </connectionStrings>

Code in Context:

 class MyContext: DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString);
        }
        public DbSet<Student> Students { get; set; }
    }

Code in Program:

  MyContext context = new MyContext();
  var count = context.Students.ToList().Count();
  Console.WriteLine("rows number:" + count);

The firewall in Azure Sql Database:

Azure WebJob can't connect to Azure SQL

1 Answers