3

I have a C# / Entity Framework web application runs fine against a local SQL 2012 db. I copied the db out to a new RDS instance, and can access the db via Visual Studio and SQL Server Management Studio.

I hav a unit test which authenticates the user and then attempts inserts a record in a table using an Entity Framework call -- dataContext.SaveChanges().

I get the following error:

{"The underlying provider failed on Open."}
{"No such host is known"}
{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: TCP Provider, error: 0 - No such host is known.)"}

I've poked around the web for answer all day, and so far no success with any of the suggestions I found:

  • Port 1433 is open I can access the entry point via Telnet
  • My IP address is covered by the CIDR/IP range for the default security group.
  • The error occurs as soon as I attempt to open the connection on the datacontext.
  • The db server is configured to accept remote connections.
  • The db server is configured to use mixed authentication (both Windows and SQL Server).

Entity Framework Connection String

connectionString = "metadata=res://*/MyDb.csdl|res://*/MyDb.ssdl|res://*/MyDb.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MyAwsEntryPoint;Initial Catalog=MyDb;User ID=MyUser;Password=MyPassword;Persist Security Info=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"

Standard Db Connection String

connectionString = "Data Source=MyAwsEntryPoint,1433;Initial Catalog=MyDb;User ID=MyUser;Password=MyPassword;Persist Security Info=True;Application Name=MyAppName;"
wloescher
  • 4,503
  • 2
  • 25
  • 27
  • Looks like this is a network-related issue and nothing to do with EF. – aikeru Apr 19 '13 at 04:48
  • 2
    I would assume a network related issue (since that's the only thing that's changed), but I can hit the db and get authenticated using .NET Membership calls. It's only when I try to open a db connection on my EF datacontext that I hit a problem. dataContext.Database.Connection.Open(); – wloescher Apr 20 '13 at 21:01
  • Fair enough. So if your membership provider and EF are hitting the same server/database, you should make sure that information matches. Are you using database first/model first...? – aikeru Apr 20 '13 at 22:03
  • Db first model. Will double check connection strings. – wloescher Apr 20 '13 at 23:25
  • Switched to ODBC connection it works just fine (after swapping out EF code for inline SQL statements). So that would seem to rule out environmental and db permission issues. – wloescher Apr 24 '13 at 03:50

1 Answers1

3

The problem was in the connection string. Specifically, I had "Application Name" and "App" properties in there. Once I removed those, it worked.

BAD:

<add name="MyDbEntities" connectionString="metadata=res://*/MyDb.csdl|res://*/MyDb.ssdl|res://*/MyDb.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=MyDbServer;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyDb;Password=MyPassword;Application Name=MyDb.API.Models;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

GOOD:

<add name="MyDbEntities" connectionString="metadata=res://*/MyDb.csdl|res://*/MyDb.ssdl|res://*/MyDb.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=MyDbServer;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyDb;Password=MyPassword;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
wloescher
  • 4,503
  • 2
  • 25
  • 27
  • Hooray! I was actually going to post an answer with some of the differences between your strings (not knowing there significance) and suggest one you should try. Glad to see this fixed! – aikeru Apr 25 '13 at 04:47
  • 1
    Do you have any idea *why* removing the application name solved the issue? – Rob Oct 07 '13 at 07:39
  • I've got the same issue and this did nothing for me :( My local db works fine. Deploy out to my server and **one** of my connections work. The other does not. They are both at same database server, different database, same user name. – fujiiface Sep 14 '16 at 22:50