Firewall – Remote connection sql server not working but if firewall disabled it does

firewallsqlsql serversql-server-2008windows-server-2008

I have setup sql server express 2008 on a windows 2008 server. And i have done all the standard stuff in sql to allow remote connections. The strange thing is if i disable the windows 2008 server firewalll .. it works.

I have opened a port as 1433 on the server firewall. And tcp is enabled, is there some other port i don't know about.. I also open 1434 …

under TCP properties i have enabled tcp for the ip (of course this part must be owrking as if i disabled firewall it works) and the port number is left blank and dynamic ports is 0

I tried playing around with setting the port number to 1434 and setting the dynamic port as blank.

Still no joy.

I have the sql browser server running and named pipes..

I am running my tests from my pc with NO FIREWALL and via Sql management studio.

I must be missing something

Really appreciated any help anyone can offer.. I am stuck

Thanks

Best Answer

Probably you forgot to execute this command:

 netsh firewall set portopening TCP 1433 "SQLServer"

You could try out the new "Fix it" feature of this Microsoft support article: How do I open the firewall port for SQL Server on Windows Server 2008?

The page also contains a script, delete the lines, if you don't need SQL Service Broker, debugger, https etc.

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 

...

netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
Related Topic