What is the best way to configure windows firewall to open up sql server to lan computers

Question

Should I configure a specific port for SQL Server and open that port up to the lan IP address range or open up the firewall based on the program, and wouldn't this require making a rule for each instance?

Do I need to add SQL browser as a rule if I want lan computers to see the SQL server instance?

Answer 1

Make a rule for the program, open it the the local network. That's about as simple as it gets, with reasonable security. If you need higher security, there are other more specific controls.

Updated
If you're running 2008 or 2008 R2, with a default named instance, MS provides a script to set the firewall rules.

There's also more information available on this page if you have more than one instance (you have to set the port for each instance) or if you want to open the firewall by the program (I do recommend this option, scroll near the bottom of the article).

Answer 2

Opening SQL Server ports onto a network is not as easy as you might think. Named Instances use different port numbers to communicate.

The one port that absolutally must be open is 1433 (assuming you haven't changed the default port).

MS KB287932 has some more detailed information about this:

In addition, a named instance... will use a dynamic destination port by default. This port should be changed to a fixed port prior to configuring the firewall. The SQL Server Network Utility should be used to configure the destination port.