keyword not supported data source

Question

I have an ASP.NET MVC application with the default membership database. I am accessing it by ADO.NET Entity Framework.

Now I want to move it to IIS, but several problems showed up. I had to install SQL Server Management Studio, Create new DB, import there all the data from the previous .MDF file. Only thing left to do (as far a I know) is to change to connection string. However, I am not really experienced with this and keep getting the exception:

Keyword not supported: 'data source'.

Here is my connection string:

<add name="ASPNETDBEntities" 
     connectionString="Data Source=MONTGOMERY-DEV\SQLEXPRESS;Initial Catalog=ASPNETDB;Integrated Security=True;" 
     providerName="System.Data.EntityClient" />

Any ideas, what's wrong?

Beshrew

I've had this error when I had the wrong startup project selected so VS was unable to find my connection strings. Ensure a startup project with app.config is selected. – Cardwell

Answer 1

What you have is a valid ADO.NET connection string - but it's NOT a valid Entity Framework connection string.

The EF connection string would look something like this:

<connectionStrings> 
  <add name="NorthwindEntities" connectionString=
     "metadata=.\Northwind.csdl|.\Northwind.ssdl|.\Northwind.msl;
      provider=System.Data.SqlClient;
      provider connection string=&quot;Data Source=SERVER\SQL2000;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=False&quot;" 
      providerName="System.Data.EntityClient" /> 
</connectionStrings>

You're missing all the metadata= and providerName= elements in your EF connection string...... you basically only have what's contained in the provider connection string part.

Using the EDMX designer should create a valid EF connection string for you, in your web.config or app.config.

I understand what you're trying to do: you need a second "ADO.NET" connection string just for ASP.NET user / membership database. Your string is OK, but the providerName is wrong - it would have to be "System.Data.SqlClient" - this connection doesn't use ENtity Framework - don't specify the "EntityClient" for it then!

<add name="ASPNETMembership" 
     connectionString="Data Source=MONTGOMERY-DEV\SQLEXPRESS;Initial Catalog=ASPNETDB;Integrated Security=True;" 
     providerName="System.Data.SqlClient" />

If you specify providerName=System.Data.EntityClient ==> Entity Framework connection string (with the metadata= and everything).

If you need and specify providerName=System.Data.SqlClient ==> straight ADO.NET SQL Server connection string without all the EF additions

Helot

As a matter of fact, it did, but then I am getting the exception Unable to open the physical file "C:\OVSS\Stavicky\trunk\Stavicky\App_Data\aspnetdb.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". An attempt to attach an auto-named database for file C:\OVSS\Stavicky\trunk\Stavicky\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. From some sources, I thought, this is wrong.. Thanks anyway. – Beshrew

I am not sure I understand the second connection string problem. I should let there the one generated by designer and add the ASPNETMembership you wrote me? – Beshrew

If you have your entities in the EDMX designer - those need to be accessed by means of a "EntityClient" and a EF connection string. If you use the "out-of-the-box" ASP.NET membership system, it's NOT part of your EF model, so when you create a connection string for your ASP.NET membership database, you cannot use "EntityClient" as the provider - use SqlClient. – Helot

So yes - you need both - the EF connection string your EDMX system generated for you, PLUS a second one - normal ADO.NET connection string - for the ASP.NET membership system. – Helot

@Helot helpful answer but you could make it clear that if you are using Entity Framework that you cannot change the provider name to System.Data.SqlClient and remove the EF relevant parts. At least this is my experience with EF6 on wards. You may ask why would you do this? In an additional application that is referencing the connection for read purposes it does seem logical at first to just use a simple ADO style connection string. – Yaya

I've added this connectionstring (EF) to appsetting.json, it says "data source is not a valid keyword". Any idea ? – Shutdown

Answer 2

This problem can occur when you reference your web.config (or app.config) connection strings by index...

var con = ConfigurationManager.ConnectionStrings[0].ConnectionString;

The zero based connection string is not always the one in your config file as it inherits others by default from further up the stack.

The recommended approaches are to access your connection by name...

var con = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

or to clear the connnectionStrings element in your config file first...

<connectionStrings>
    <clear/>
    <add name="MyConnection" connectionString="...

Aldrin

Answer 3

I was getting the same problem.
but this code works good try it.

<add name="MyCon" connectionString="Server=****;initial catalog=PortalDb;user id=**;password=**;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient" />

Cene

Answer 4

If you supplying the connection string directly to entity framework, remember to change the two XML escape code " characters into actual quote symbols in the provided string, otherwise this same error can occur.

I am overriding the connection string using a separate partial class file to the generated one that passed the EF connection string to its base class.

    // Partial class to use instead of generated version
    public partial class PartEntities : DbContext
    {
        // Use the full EF6 connection string as described in other comments here
        // Note: This is only here for testing, will be keeping outside source code
        const string fullEFconnectionString = "metadata= ...";

        // Extra parameter differentiates constructor
        public PartEntities(bool b)
            : base(fullEFconnectionString.Replace(""", "\""))
        {
        }
    }

So wherever in the code I want to access the database context I do this -

using (var ctx = new PartEntities(true))
{
    // Code that uses the context goes here
}

Hatpin

Underated answer. Thank you. – Qualify

Answer 5

I was getting the same error, then updated my connection string as below,

<add name="EmployeeContext" connectionString="data source=*****;initial catalog=EmployeeDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

Try this it will solve your issue.

Wolters

Answer 6

I had this problem when I started using Entity Framework, it happened when I did not change the old SQL server connection to EntityFrameWork connection.

Solution: in the file where connection is made through web.config file "add name="Entities" connectionString=XYZ", make sure you are referring to the correct connection, in my case I had to do this

        public static string MyEntityFrameworkConnection
    {
        get
        {
             return ConfigurationManager.ConnectionStrings["Entities"].ConnectionString;
        }

    }

call MyEntityFrameworkConnection whenever connection need to be established.

private string strConnection= Library.DataAccessLayer.DBfile.AdoSomething.MyEntityFrameworkConnection;

note: the connection in web.config file will be generated automatically when adding Entity model to the solution.

Noni

Answer 7

I know this is an old post but I got the same error recently so for what it's worth, here's another solution:

This is usually a connection string error, please check the format of your connection string, you can look up 'entity framework connectionstring' or follow the suggestions above.

However, in my case my connection string was fine and the error was caused by something completely different so I hope this helps someone:

1. First I had an EDMX error: there was a new database table in the EDMX and the table did not exist in my database (funny thing is the error the error was not very obvious because it was not shown in my EDMX or output window, instead it was tucked away in visual studio in the 'Error List' window under the 'Warnings'). I resolved this error by adding the missing table to my database. But, I was actually busy trying to add a stored procedure and still getting the 'datasource' error so see below how i resolved it:

2. Stored procedure error: I was trying to add a stored procedure and everytime I added it via the EDMX design window I got a 'datasource' error. The solution was to add the stored procedure as blank (I kept the stored proc name and declaration but deleted the contents of the stored proc and replaced it with 'select 1' and retried adding it to the EDMX). It worked! Presumably EF didn't like something inside my stored proc. Once I'd added the proc to EF I was then able to update the contents of the proc on my database to what I wanted it to be and it works, 'datasource' error resolved.

weirdness

Ekg

Answer 8

In case it helps others, also please check the providerName in the connection string. Depending on how the EF Context is coded, you may need SqlClient instead of EntityClient

providerName="System.Data.EntityClient"

Hurff

Answer 9

The error message is relative to the cause of the error, the keyword is case sensitive. it's supposed to be "Data Source" not "data source"

Beatrix