Personal Firewall Blocking SQL Server

[kcwallace]

I am evaluating ESET Smart Security v4.

I run an instance of SQL Server 2005 on one of my PCs within my network. That instance of MSSQL contains a database that must be accessed for our business to operate.

The Personal Firewall is blocking all access to the SQL Server from within our network. I shut off the firewall, and everything wirks fine.

What setting do I need to change in order to allow continued access to the SQL database.

[mickhardy]

You need to set the Firewall to Automatic with Exceptions and then define rules for sqlservr.exe and sqlbrowser.exe

It gets a little complicated to explain any further than that because it depends on your exact situation.

Is ESS on the SQL Server? Is it full SQL Server or SQL Express? Are you using the standard port or dynamic ports?

We have local SQL Express installs on Notebooks with ESS that I need to access from a machine also with ESS and it all works so it is possible.

Sometimes, I set ESS to interactive mode temporarily to see what rules need to be created and then switch back to Automatic with exceptions after creating the rules.

[kcwallace]

Thank you for the advice.

How do you set the excepted applications?

Yes, ESS is on the SQL Server

We are using SQL Server 2005 Standard Edition

Not sure about static or dynamic ports

[mickhardy]

Try adding sqlservr.exe and sqlbrowser.exe to the ESS rules on the SQL Server first. Then you don't have to worry about ports. The default port is 1433 but SQL Server 2005 can be configured to use dynamic ports and possibly does so by default. If you allow the programs rather than the ports, you'll be fine.

ESS will not allow incoming communication to a SQL Server in Automatic Mode and nor should it, hence the ability for Automatic with Exceptions.

In ESS, F5 -> Personal Firewall -> Rules and Zones -> Zone and Rule Editor -> Setup

On the local tab browse to the applications, which by default and depending on instances are found in

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe"

and

"C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

You need to tweak the rules to allow whatever traffic is being blocked according to the firewall log. You can be really specific and only allow certain IP addresses, zones and ports or you can open it up as much as you like. Try creating an open rule, get it all working and then fine tune it.

[mickhardy]

I'm a little surprised you're running ESS on a production SQL Server.