Firewall responsible for failure to connect to online database?

[mdpepper]

As an IT placement student I have reached the end of a 3 month project for the company I work for. I have migrated information to MSSQL server 2008 edition and I am retrieving the information for dynamic web page display using php. Now that I have everything in the right place I can see that I have only been testing this database by using php scripts on the same server that the database is stored on. I now have to put the database on an external server and the scripts on an internal server.

I have the required ip address of the external (sql) server but when I add this information to a php connection string I am not getting a response. The same thing happens where Ive written a simple test script using asp. The error message when using asp is "sql server does not exist or permission is denied" .The head of IT has ensured that the permissions for access between the servers are open, including making sure that port 1433 is being listened to by the sql server.

I need to know how I can begin an effective testing regime to pinpoint what the problem is. How can I test for sure that port 1433 is being listened to? How can I test if an information stream is being blocked by a firewall at either the internal side or external side?

[vkumarg89]

Firewall by default blocks every incoming traffic.... U just need a access list to enable that particular ip to access.

[arghasen]

you can check the port by netstat-aon

[sknake]

More ports than 1433 are typically involved in an SQL connection. Here is a batch file for opening ports on a default instance (non-named instance) of SQL:

netsh firewall set portopening TCP 1433 "SQLServer" 
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
netsh firewall set portopening TCP 2383 "Analysis Services" 
netsh firewall set portopening TCP 2382 "SQL Browser" 
netsh firewall set portopening UDP 1434 "SQL Browser" 
netsh firewall set multicastbroadcastresponse ENABLE

If your SQL Server shows up as "SERVERNAME" and not "SERVERNAME\INSTANCENAME" the above file should work. If it shows up as the latter you also have to open the dynamic TCP port used by that instance. Interestingly enough firewall configurations can actually cause the server to respond as "SERVERNAME" and not "SERVERNAME\InstanceName" so that alone may not be enough to determine if it is a named instance. If you get a successful connection to the SQL server you can also run this to find out:

SELECT @@SERVERNAME

If you have a named instance of SQL please post back on this thread and I will post how to discover the dynamic TCP port for named instances of SQL Server.