5

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

5 Answers 5

5

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 
1
  • 1
    yep that was it... 1434 but on UDP! thanks,
    – user11655
    Jul 7, 2009 at 16:13
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
3
  • 1
    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!
    – Oxidator
    May 11, 2015 at 8:41
  • @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
    Sep 26, 2017 at 9:23
  • 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
    – bh_earth0
    Sep 26, 2017 at 9:25
0

Double-check your settings with this article from Microsoft: Configuring the Windows Firewall to Allow SQL Server Access. Assuming you're not using a named instance, port 1433 being open for inbound connections should do it. Review the article anyway, you may find that you've missed a step.

0

Check if the rules you made (ports 1433 and 1434) are for inbound connections not for outbound connections.

0

I would check that SQL server is forced to using 1433, otherwise it might use "dynamic ports". Open Sql Server Configuration Manager > Sql Server Network Configuration > TCP/IP > Properties > IP Addresses I have set: * TCP dynamic ports: * TCP port: 1433

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .