Connection string to remote DB when using EntityFramework

Question

I have an ASP.NET MVC application, and I'm making use of EntityFramework 5.0. I've also installed a 'CodeFirstMembershipProvider' Nuget package, which provided some code for ASP.NET membership - and I believe also made changes to my web.config.

Basically, my problem is that my application works fine on a local SQL Server instance, but when I specify a remote DB using this connection string:

    <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=*******************, 14330;Integrated Security=False;Database=qrcodetr_database;Uid=qrcodetr_admin;Password=*********; Connect Timeout=15;Encrypt=False;Network Library=dbmssocn;Packet Size=4096" />

(password and hostname edited out)

it fails to use this connection string. It continues to attempt to use the local SQL Server instance instead - so it works locally but when I try it on my web and database host it fails - since it tries to use a local SQL server instance which it can't do on my database host. The remote database host uses SQL Server 2008 R2.

Does anyone know why this might be or what I can do to solve this?

I'm not going to post the whole web.config, but here's a few lines that might be relevant:

<add key="DatabaseInitializerForType TreasureHuntDB, QrCodeTreasureHunter" value="DataContextInitializer, QrCodeTreasureHunter"

<sessionState mode="InProc" customProvider="DefaultSessionProvider">
  <providers>
    <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" />
  </providers>
</sessionState>
<membership defaultProvider="CodeFirstMembershipProvider">
  <providers>
    <add name="CodeFirstMembershipProvider" type="CodeFirstMembershipProvider" connectionStringName="DefaultConnection" />
  </providers>
</membership>
<roleManager enabled="true" defaultProvider="CodeFirstRoleProvider">
  <providers>
    <clear />
    <add name="CodeFirstRoleProvider" type="CodeFirstRoleProvider" connectionStringName="DefaultConnection" />
  </providers>
</roleManager>

      <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>

I'm able to successfully connect to the remote DB via SQL Server 2008, by the way, so the issue definitely seems to be a configuration issue on my end.

By the way, I am using a DataContextInitializer which is set to 'CreateDatabaseIfNotExists'. I have set it to this because on the hosted database, I don't have permissions to CREATE or DROP the database, so what I've done is I've created a backup of one of the ones created locally (in SQL Server Express) and used the restore feature to restore the file to my hosted database. So I have a database in place, it just won't work with my application.

How do you tell the EF to use the DefaultConnection? You need to call the DbContext ctor with "Name=DefaultConnection" string - do you do that? How does your DbContext look like? — Pawel
– Pawel, Commented Feb 4, 2013 at 22:32
Yup, that was it. My DbContext is TreasureHuntDb - I needed to change the connection string name to that (and in a few other places). If you put that as an answer I can mark your post as the correct answer. — Ciaran Gallagher
– Ciaran Gallagher, Commented Feb 4, 2013 at 22:46

Pawel · Accepted Answer · 2013-02-04 23:52:25Z

1

You don't seem to tell the EF the name of the connection string so it is just creating one by convention. You can either pass the name to the DbContext ctor in the form of "Name=DefaultConnection" or rename the connection string in your config so that its name matches the name of the DbContext derived class.

answered Feb 4, 2013 at 23:52

Pawel

31.7k4 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:18:58Z

1

This question and answer suggests that your connection string section should look like this:

<connectionStrings>
    <clear /> <!--- need this to prevent using LocalSqlServer from machine.config or somewhere becouse it is not present when when publish to hosting -->
    <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="etc" />  
</connectionStrings>

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Feb 4, 2013 at 22:34

Ann L.

14k5 gold badges39 silver badges67 bronze badges

Collectives™ on Stack Overflow

Connection string to remote DB when using EntityFramework

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related