I have done the following -
I set up a new EC2 instance running Windows Server 2008 with IIS7.
I set up a new RDS Instance running SQL Server 2008 R2.
I have configured the Security Group for the RDS server to allow SQL Server connections on port 1433 from the EC2 instance.
On the EC2 instance I can connect to the RDS SQL Database by creating a User or System ODBC Connection from Windows. I can also connect from my own computer with SQL Management Studio.
When I try to start up my .NET Web Application I get this error
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: SQL Network Interfaces, error: 26 -
Error Locating Server/Instance Specified)
This is my connection string, I am using Entity Framework and an edmx, Database first.
<add name="EntitiesName" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=myrdsserver.rds.amazonaws.com;initial catalog=VPN;User Id=userid;Password=password;Persist Security Info=True;MultipleActiveResultSets=True;"" providerName="System.Data.EntityClient" />
I've tried everything in the following links but nothing works to allow me to connect.
aws ec2 could not open a connection to sql server
entity framework 5 and amazon rds underlying prodiver failed to open
can't connect to rds instance from ec2 instance
UPDATE:
I created a Windows .NET Forms application that uses the same connection string and Entity Framework and it works. I ran it from the same EC2 instance. It seems that the main difference here is that IIS is not involved with the Windows Form application. I'm not sure how that is keeping the Web application from working.
Here is the stack trace from the web page:
[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6573870
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +717
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +6600312
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +219
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +6602662
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +6603203
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) +942
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1162
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +72
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +6606457
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +103
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +2102
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +116
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +1079
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +6610951
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +233
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +278
System.Data.SqlClient.SqlConnection.Open() +239
Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection() +49
Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString, SqlServerStorageOptions options) +279
Hangfire.SqlServerStorageExtensions.UseSqlServerStorage(IGlobalConfiguration configuration, String nameOrConnectionString) +80
MyWebApplication1.Startup.Configuration(IAppBuilder app) +39
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +76
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +211
System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) +35
Owin.Loader.<>c__DisplayClass1.<LoadImplementation>b__0(IAppBuilder builder) +341
Microsoft.Owin.Host.SystemWeb.OwinAppContext.Initialize(Action`1 startup) +996
Microsoft.Owin.Host.SystemWeb.OwinHttpModule.InitializeBlueprint() +119
System.Threading.LazyInitializer.EnsureInitializedCore(T& target, Boolean& initialized, Object& syncLock, Func`1 valueFactory) +241
Microsoft.Owin.Host.SystemWeb.OwinHttpModule.Init(HttpApplication context) +108
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +530
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +304
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +404
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +475
[HttpException (0x80004005): Exception has been thrown by the target of an invocation.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +12618692
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +159
System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +12458309
