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

Question

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

Solution 1

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 

Solution 2

Have you tried specifying the executables as the exceptions in the Windwows Firewall? Just this weekend I setup my own Win 2008 with SQL 2008 machine with these steps, things are working perfectly. No port numbers were involved in the process. Can get to this instance remotely from Sql Management Studio with no problems.

• Start->Run-> firewall.cpl
• Allow a program through Windows Firewall
• Click button "Add Program", and Browse
• Add these two executables:

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\SqlServer.exe      
C:\Program Files\Microsoft SQL Server\90\Shared\SqlBrowser.exe

Comments

[user11655]

yep that was it... 1434 but on UDP! thanks,

[Oxidator]

I added SqlServer.exe, and that finally did the trick for me. Just adding the ports 1433 (TCP), 1434(TCP and UDP) didn't work. Couldn't find the sqlbrowser.exe, but apparently don't need it. Thanks!

[bh_earth0]

@Oxidator for future users that file is present at 32bit program files folder C:\Program Files\Microsoft SQL Server(x86)\90\Shared\SqlBrowser.exe

[bh_earth0]

for sql express 2016 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe .also worked for me without adding slqBrowser.exe . edit: initally worked but later failed. i also needed to open inbound udp 1434 port to allow sql browser to work properly