Entity Framework 5 and Amazon RDS - “The underlyin

2019-02-24 18:58发布

问题:

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;"

回答1:

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" />