Firewall – Windows 2008: SQL Management studio, can’t connect remotely with firewall enabled, but i have TCP 1433 enabled

firewallsql-server-2008windows-server-2008

I have setup SQL Server express R2 on WIndows Server 2008 R2 and i can connect locally with SQL Management studio.

Remotely with the firewall enabled i cannot connect.

If i disable the firewall then remotely i can connect.

THis is very strange.

My firewall setting is set to TCP 1433 (although i also added 1434 as well)… but it refuses to work.

In the SQL Express R2 i have TCP/IP Enabled and it listens on 1433

Any help really appreciated, i am little lost. I have searched the net and i believe i am doing everything correctly.

Thanks in advance

Best Answer

Have you restarted the SQL service since you made the configuration changes to enable TCP/IP and set a static port? These changes will not take effect until the service is restarted.

You can verify what ports are being listened on by using netstat -a on the server.

Related Solutions

Can’t connect to local database in SQL Server (2008) Management Studio

It could be that he did the proper thing and revoked rights to the BUILTIN\ADMINISTRATORS group. However, the right thing to do is in fact causing you issues. I suggest you have him remote back in and grant your login SystemAdmin role rights if that is what you should have. He could also reinstate those same rights to the BUILTIN\ADMINISTRATORS group as well, but that is not recommended.

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

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

Best Answer

Related Solutions

Can’t connect to local database in SQL Server (2008) Management Studio

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

Related Topic