CODEWITHSUNDEEP
asp.netentity-frameworkazureazure-sql-databaseazure-web-app-service
Jacob Foshee
Jacob Foshee

Reputation: 2773

Setting EF Connection String in Azure Web App

We have an ASP .NET (MVC) app and are using Entity Framework 6 to connect to our databases. The DbContext is constructed in a standard way and it loads the connection string on our behalf. The generated code looks like this:

public partial class MyContext : DbContext
{
    public MyContext(string connectionName)
        : base("name=" + connectionName)
    {
    }
}

We set the connection string in a local web.config also in a standard way:

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" 
         connectionString="metadata=...;provider connection string=&quot;...&quot;" 
         providerName="System.Data.EntityClient" />

When we publish the app to Azure we navigate to the Azure Portal, then to the Web App's Settings, then to the list of Connection Strings. There we add the EF connection string that we had used locally. When we restart and visit the app we get a run-time error depending on the type of connection string we choose.

For a Custom type we get the following run-time error:

Keyword not supported: 'data source'.

For SQL Server or SQL Database we get the following run-time error:

Keyword not supported: 'metadata'.

This really seems like a straightforward story so we are wondering what is going wrong.

Upvotes: 24

Views: 11342

Answers (4)

Nick.Mc
Nick.Mc

Reputation: 19235

Just recording my own experiences in addition to answers already recorded here

This was my final connection string (on mutliple lines for clarity)

metadata=res://*/Models.mBT.csdl|res://*/Models.mBT.ssdl|res://*/Models.mBT.msl;
provider=System.Data.SqlClient;
provider connection string="
  Data Source=tcp:myazureserver.database.windows.net,1433;
  Initial Catalog=databasename;
  User ID=z@myazureserver;
  Password=xyz"

To convert from a "normal" connection string to one that is accepted by EF:

  • The connection string type in the application settings has to be "other" not "SQL Azure"
  • The connection string value automatically replaces anything published in web.config
  • The metadata name Models.mBT.csdl (and the other two) comes from this:

Firstly, mBT is the name of my .edmx file

With regards to the Model. bit, see the answer from @leqid here: MetadataException: Unable to load the specified metadata resource

You can inspect your obj directory and see these three metadata files are in a subfolder called Models, so you need to prepend with Models.

Upvotes: 5

Maayan Hope
Maayan Hope

Reputation: 1611

If you have this line in web.connfig

<add name="Entities" connectionString="metadata=res://*/TestDB.csdl|res://*/TestDB.ssdl|res://*/TestDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=XXXXXXXX.database.windows.net,1433;Initial Catalog=YourDB;User ID=YourUser;Password=XXXXXX&quot;" providerName="System.Data.EntityClient" />

Add this in azure portal:

Name Column => Entities

Value Column => metadata=res://*/TestDB.csdl|res://*/TestDB.ssdl|res://*/TestDB.msl;provider=System.Data.SqlClient;provider connection string="Data Source=XXXXXXXX.database.windows.net,1433;Initial Catalog=YourDB;User ID=YourUser;Password=XXXXXX" 

"Custom" - In the drop selection box

Make sure (as stated in first answer) to replace &quot; with "

Upvotes: 10

Jacob Foshee
Jacob Foshee

Reputation: 2773

The problem is the escaped quotes: &quot;.

The connection strings in web.config have quotes escaped because they are serialized in an XML attribute. When entering a connection string in the Azure portal you should provide the raw unescaped string. Something like this:

metadata=...;provider connection string="Data Source=..."

David Ebbo's answer is good for confirming that the Environment is set up as you expect. It is also helpful to pay attention to the .pubxml file when publishing via the wizard in Visual Studio: it will try to populate connection strings as well.

Upvotes: 36

David Ebbo
David Ebbo

Reputation: 43203

'custom' should be correct here. In that case, the providerName is left unchanged, so if you have System.Data.EntityClient in your config, that should remain after the Azure runtime changes it.

Try going to Kudu Console and click on Environment to make sure the conn string looks correct there.

Upvotes: 15

Related Questions