Azure App to Azure Database "Connection Failed" with Entity Framework

Question

I have a C# .Net Web API deployed to an Azure App Service, I also have an Azure SQL Database.

In the API I am using Entity Framework to insert into the database, but I keep getting the error message: "The underlying provider failed on open".

(When running the API locally (in debug mode) connecting to a local database it works fine). Could this be a permissions/firewall configuration problem with the Azure database, or something else?

I have added my current IP address in the "Azure Set Server Firewall", do I need to add the Azure Web API's IP address to the database firewall settings?

This is my API:

public class ProfileController : ApiController
{
    [EnableCors(origins: "*", headers: "*", methods: "*")]
    [WebMethod]
    [HttpPost]
    public HttpResponseMessage PostProfile([FromBody] Profile details)
    {
        var context = new XXXDBEntities();
        var query = from c in context.Users
                    where c.Email.Equals(details.email, StringComparison.CurrentCultureIgnoreCase)
                    select c;
        var emailFound = query.Count();


        if (emailFound != 0)
        {
            return Request.CreateResponse(HttpStatusCode.OK, "There is already an account associated with this email address");
        }
        else
        {
            Guid token = Guid.NewGuid();
            Users newRow = new Users();
            newRow.Token = token;
            newRow.FirstName = details.firstName;
            newRow.LastName = details.lastName;
            newRow.Email = details.email;
            newRow.Password = details.password;
            context.Users.Add(newRow);
            context.SaveChanges();
            return Request.CreateResponse(HttpStatusCode.OK, token);
        }

    }

This is my connection string: This is the default format for connection strings in .Net Entity Framework, I have only added username, password and changed the data source and catalog fields. Is this correct?

<add name="XXXDBEntities" connectionString="metadata=res://*/XXXDB.csdl|res://*/XXXDB.ssdl|res://*/XXXDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=tcp:XXX.database.windows.net,1433;initial catalog=XXXDB;integrated security=True;User ID=XXXXX;Password=XXXXX;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Are you able to run locally but still connect successfully to your Azure SQL instance? — Rob Reagan
Why do you have integrated security=True in the connection string when you specify a username and password? — juunas
Thanks! I removed the "integrated security= true" and added "persist security info=True". It now connects successfully :) — Tess
@juunas you should have posted this as the answer so Tess could mark the question as having been answered — Mick

Jambor - MSFT Jambor - MSFT · Accepted Answer · 2017-03-08T06:19:44

I have checked your connection string, it seems to be right. So I think your issue may be caused by the model of the SQL Azure is changed, but your project does not update it. Here is the same issue I reproduced on my side:

I would suggest you update your model.

do I need to add the Azure Web API's IP address to the database firewall settings?

We can set Allow access to Azure services as ON in SQL Azure firewall settings. So that we need not to add Azure web API's address.

Azure App to Azure Database "Connection Failed" with Entity Framework

2 Answers