Based on this excellent article by @esh on using external Elastic IP DNS names internally within EC2, I am in the process of changing my connection strings for my web server to connect to a DB server (MSSQL instance) via the elastic IP DNS name rather than the elastic IP address itself.
The issue I have found is that I can connect to the SQL instance using the elastic IP address fine, but when I connect using the elastic IP DNS name I get the following connection 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I understand this resolves to the internal IP address but is there any implication with security groups, the firewall or SQL configuration that would cause this error?
Best Answer
Go ahead and give these instructions a try. If they work for you, awesome. If not, we'll continue to dig.
"GO START -> ALL PROGRAMS -> SQL SERVER 2008/2005/2008 R2 ->CONFIGURATION TOOLS-> ->SELECT SQL SERVER CONFIGURATION MANAGER
->SELECT SQL SERVER NETWORK CONFIGURATION
->SELECT PROTOCOLS FOR MSSQLSERVER(YOUR INSTANCE NAME)
->IN RIGHT TASK PANEL ->MAKE SURE MENTION BELOW:
DISABLE SHARED MEMORY AND VIA ENABLE NAMED PIPE AND TCP/IP
—-MAKE SAME CHANGES IN
->SQL NATIVE CLIENT 10.0(SERVER VERSION) CONFIGURATION – CLIENT PROTOCOLS
RESTART SQL SERVER(MSSQLSERVER [YOUR INSTANCE NAME]) SERVICE"
Give it a try, let me know how it goes for you.