30

In the Windows Azure (Preview) Management Portal you can change the configuration options for web sites (see http://www.windowsazure.com/en-us/manage/services/web-sites/how-to-configure-websites/#howtochangeconfig).

I currently set the connection string for my ADO.NET Entity Framework connection via Web.Release.Config, but I want to set it via the Management Portal, but no matter what I use, I always end up with the following error:

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

It does work for regular connection strings, ie without the metadata key defining metadata and mapping information (csdl, ssdl, msl).

Here's what I do:

I go to https://manage.windowsazure.com/#Workspaces/WebsiteExtension/Website/[MY-STAGING-SITE-NAME]/configure

Under "connection strings" I have a key named "ApplicationServices" that looks like this:

Server=tcp:xxxxx.database.windows.net,1433;Database=xxxxx;User ID=xxxxx@xxxxx;Password=xxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

This one works.

I have another key for the Entity Framework connection. Let's call that one FooBarContext. It looks like this:

metadata=res:///Models.FooBarContext.csdl|res:///Models.FooBarContext.ssdl|res://*/Models.FooBarContext.msl;provider=System.Data.SqlClient;provider connection string="Server=tcp:fooserver.database.windows.net,1433;Database=foobar;User ID=myname@fooserver;Password=xxxxxxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

This one causes the error described above. It is copied from the working value in Web.Release.Config, with the " replaced by a ".

I have tried other variations: with the " untouched, with metadata appended at the end, but to no avail. I have reproduced the problem with a second website.

Frank van Eykelen
  • 1,926
  • 1
  • 23
  • 31
  • 1
    First check whether you see the connection string in `ConfigurationManager.ConnectionStrings` (i.e. write it to webpage). The name should be present in `web.config`, Azure WS will replace it with the value defined in portal (it will not add new if it's not there). The double quotes `"` work fine through portal, you don't have to deal with that. – cincura.net Jan 22 '13 at 13:56
  • @cincura.net I have run the following test: set the values in web.config to "[SET ON manage.windowsazure.com]". When I deploy and write out the values from System.Configuration.ConfigurationManager.ConnectionStrings they are replaced by the values set via manage.windowsazure.com. So the mechanism works, but the problem remains that the format of the EF connection string is not recognized. – Frank van Eykelen Jan 23 '13 at 09:28
  • @cincura.net Do you have an (anonymized) example of a working EF connection string that is set via manage.windowsazure.com? – Frank van Eykelen Jan 23 '13 at 09:31

5 Answers5

53

The solution for my problem was selecting "Custom" instead of "SQL Azure" from the "SQL Azure / SQL Server / MySQL / Custom" selector for the Entity Framework connection string, even though the database does run on SQL Azure.

[Edit] From a popular comment by @matthew-steeples below:

I would add to this for anyone else having the same issue is that sometimes the config file will have " instead of ", and the Azure Websites needs those to be changed to "

Frank van Eykelen
  • 1,926
  • 1
  • 23
  • 31
  • 35
    All I would add to this for anyone else having the same issue is that sometimes the config file will have " instead of ", and the Azure Websites needs those to be changed to " – Matthew Steeples Mar 07 '13 at 20:23
  • 1
    sweet jesus this just bought me a couple hours sleep, beer on me, all you can drink, to Frank and Matthew anytime you guys visit San Diego!! – Brian Ogden Dec 16 '13 at 12:59
  • 11
    I can't believe I've just had this problem, searched for the issue, and found that my own comment was the thing that needed fixing! – Matthew Steeples Jan 30 '14 at 03:45
  • 2
    But, guys, then where do you specify the provider? As what sounds like good practice to us, we strip development connection strings from the release `Web.config` which is thus completely devoid of any connection information. Then, we configure it per deployment slot in the panel; which works great except for when we need a custom provider. I assume it works for all of you because the development info is deployed to the production server, then the connection string is overriden but not the provider name. Is this correct? – tne Apr 14 '15 at 10:41
  • I lost two days of work because I haven't selected 'custom' on that stupid option. Thank you Frank. – John John Pichler Aug 25 '15 at 13:14
  • By the way, the 'Custom' setting they are talking about is in a dropdown in Azure as the last column for the connection string table. I thought It was some configuration at first from when I first setup EF and had no clue it was right there. – Devin Gleason Lambert May 25 '16 at 18:05
  • 1
    @tne I saw nobody answered you here, I think you are confusing things -- the ProviderName is not specific to Development in many cases. What you do is create a Web.Release.Config that has the XDT Transformations in them. You can look this up online, here is an example of the transformation we use in production: This replaces the connectionString with the word "dummy" and azure overrides. – LimpingNinja Feb 20 '17 at 21:31
  • The connectionString can be overridden by a value set in the Azure portal but the 'providerName' still comes from the web.config when you enter a "Custom" connection string in the portal. So, as @dmarlow describes in his answer, you do need to keep a dummy connection string in the web.config file that you publish to Azure. – Matt Varblow May 01 '19 at 16:06
  • @MatthewSteeples that comment should be part of the answer. Also, don't forget to press save at the top of the Azure interface after changing the value in the connection string! – Alexander Don'valderath Nov 04 '19 at 21:29
14

enter image description here

Replace 

"

with 

"

In the connection string.

Kbdavis07
  • 1,012
  • 13
  • 24
  • 1
    Keep in mind that this post is a few years old. I just tried double quote instead of " and it does work. – Gixabel Apr 20 '17 at 07:13
5

Not only did I have to use double quotes (or single quotes) instead of " (and select Custom for the type) but I also had to make sure there was a dummy value in my transform config. I was replacing the entire connectionStrings node, but decided to keep that and add this:

<add xdt:Transform="Replace" xdt:Locator="Match(name)" name="FooBarEntities" connectionString="temp" providerName="System.Data.EntityClient" />

Without this, I was getting the following error:

The connection string 'FooBarEntities' in the application's configuration file does not contain the required providerName attribute.

dmarlow
  • 417
  • 7
  • 13
0

Beside answers above, there are 2 very important things:

  1. You should remove "name=" from "name=connectionString" in constructor:

public MyEntities(string connectionString) : base($"name={connectionString}") { }

  1. You should leave "duplicate" of connection string in app.config, but replace connection string with dummy text, correct connection string will be loaded from Azure. That's needed for providerName part. Please read: https://mohitgoyal.co/2017/07/05/update-connection-string-for-entity-framework-in-azure-web-app-settings/comment-page-1/
Taras Pysarskyi
  • 1
  • 2
-1

I had the same problem. I solved, putting in the web.config this connectionstring:

<add name="eManagerTurModelConnection" connectionString="metadata=res://*/ORM.eManagerFinanceModel.csdl|res://*/ORM.eManagerFinanceModel.ssdl|res://*/ORM.eManagerFinanceModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=<server>.database.windows.net;Initial Catalog=eManagerTur;Integrated Security=False;User ID=<user>;Password=<Password>;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

And after I removed the connectionstring of my website, worked, because it was not getting the connection string that I added in my web.config.

English bad... =)

Thom Marques
  • 19
  • 2