Remote SQL Server 2008 R2 connection error

Question

I'll first try to explain the problem I have. I have a SQL Server 2008 R2 running on Windows 2003 R2 SP2. The SQL Server is configured to accept remote connections on port 1433. From a Windows 7 machine on which I have configured a WAMP server I use a PHP script to connect to the Database and retrieve some data. This works perfect.

Now I tried to move it online. Moved the whole application on hosting server that has sqlsrv drivers for Php and I get this error.

Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 10060 [code] => 10060 [2] => [Microsoft][SQL Server Native Client 10.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. [message] => [Microsoft][SQL Server Native Client 10.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Login timeout expired [message] => [Microsoft][SQL Server Native Client 10.0]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 10060 [code] => 10060 [2] => [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) )

I am confused why would I have different results. I should also mention that the Hosting Server is also running on Windows. Anyone knows why I might not be able to connect from a certain server? Any answers appreciated. Thanks!

Cosmin

---

Thank you all for your answers. I discussed with my host provider they do not allow the connection.

"With our shared hosting plan which your website is built on, you will not be able to remotely connect to a 3rd party or remote database with the site. Also the maximum size of the MS SQL Databases on our shared hosting plans is 200 MB and cannot be changed."

I am quite new to SQL. I thought about generating an xml with all the records and update it automatically using ftp but as users should only be allowed to see their part of the database after login I'm not sure that this is secure.

Does anyone know a way how I could display the data from my SQL Server on the website?

Answer 1

Is it a dedicated server? It is quite usual for hosting services to limit allowed connections from shared hosting only to local environment. Check with Your host provider if they allow remote connections from applications they host.

Second thing. Are you sure your server has port 1433 visible from outside your local network? The fact that it is accessible for your machine doesn't mean it's accessible from internet.

Answer 2

The first thing to do is estabish you have a good connection: create a udl file with the appropriate odbc connection. The easiest way to do this is to open notepad and create an empty file, then rename .txt as .udl and double click. UDL files are often misunderstood. They are simply a formatted string text file which gives sufficient information to the database drivers to estabish a connection and get the database service to answer back. Check with a non php driver first.

Theb establish if the port 1433 is open and the utp 1434 and any instance ports. these are most commonly blocked by your firewall. Can be tested by breifly turning firewallls off

HTH