SQL Server Specific Windows Firewall Exception -- sql server connect

Sue Hoegemeier
8/5/2007 9:43:21 PM

You generally don't want something that installs and
automatically opens up ports - that's been a huge problem in
the past. So things are intentionally designed to be secure
by default now with the newer Microsoft services. There are
applications that use only local, nonremote connections to
SQL Server so automatically opening up ports in such cases
would unnecessarily increase the surface area of exposure to
threats, hacks.

-Sue

On Sun, 29 Jul 2007 18:30:01 -0700, Andrew Hayes
[quoted text, click to view]

Andrew Hayes
8/5/2007 10:36:01 PM

Generally. That is true. And I'm quite happy with the default out-of-box
configuration. At least for local machine purposes.

However, if I've gone into the Surface Area Configuration and enabled remote
Named Pipes and TCP/IP connections then obviously something is going to be
connecting to it remotely (otherwise, why would I bother?).

At this point it should install the Exceptions that are needed, even if it
doesn't enable them, so when I go to Windows Firewall I don't have to mess
about browsing for EXE's or adding new ports, and ending up with a mess of
exceptions that are a pain to deal with.

I spend far too much time trawling through "HOWTO: blah blah blah through
Windows Firewall" articles than I would like.

Why introduce a firewall that is so complicated to configure in a corporate
environment that most SE's I know just turn it off?

And no. Using the GPO isn't a realistic approach as you would have to have
several policies to open different ports and/or point at different EXE's
depending on what the server is used for, and then setup WMI filtering so
that the policies only apply to the correct servers.

[quoted text, click to view]

Sue Hoegemeier
8/6/2007 9:11:31 AM

Most corporate environments don't use Microsoft Windows
Firewall - not because it's too complicated but because it's
too simple and doesn't support the needs of a corporate
environment. They only turn them off on PCs as it's not
really needed in their environment - they use other
appliances to protect the corporate infrastructure. The
firewalls being used are different so having some
"automatically open nnn port" may not be realistic. If
someone had Express on their PC and allowed remote
connections which opened up the ports on corporate
firewalls, you'd have problems.
Although I understand your frustration with trying to figure
out what ports to open when and why, the efforts required
come from a place of having learned some hard lessons with
issues like Blaster, Slammer, etc. Microsoft deals with
trying to best support the needs of all environments. I
think most users appreciate that to some degree.
Automatically opening up firewall ports is something they
have been blasted for in the past...and still are but to a
lesser degree. It just is not a good practice to
automatically expose ports or open up vulnerabilities.

-Sue

On Sun, 5 Aug 2007 22:36:01 -0700, Andrew Hayes
[quoted text, click to view]

Andrew Hayes
8/6/2007 5:14:06 PM

Quite correct. Most corporations do have other systems in place to protect
their network (and I'm not talking ZoneAlarm, McAfee Personal Firewall, here.
I'm talking full 3-leg perimeter network, DMZ, ForeFront, ePO, etc.), and
they're in trouble if they don't.

I'm not saying Windows Firewall is good, but it is installed and enabled by
default for XP SP2 and W2K3 SP1, which does mean it needs some attention of
some kind.

And it does provide a small measure of protection against malicious software
introduced to the corporate network through some other means... An infected
CD or downloaded file with a 0-day virus like Blaster or Slammer that the
up-to-date antivirus system doesn't detect until it's already whizzing about
the network. All those lovely non-firewalled PC's being hit from the inside.

(Recent surveys have shown that 73% of corporate security breaches are from
the inside.)

Like you said, in most cases it's turned off in corporate environments
because they have other applications, so... then it's not really a problem
having it automatically add exceptions (which don't have to be open ports) in
corporate environments, is it?

Granted. It can't do that if you have a 3rd party firewall installed, and a
search of the support forums for such software shows common problems with
getting certain communications working through whichever firewall is
installed on the Desktop or Server.

But then, Microsoft don't have a problem with it considering the exceptions
it adds to Windows Firewall when you install Office 2007 Ultimate, even if
the firewall is off.

And yes, it is a common request. Just look through these newsgroups and the
KB to see how many questions and articles relate to adding exceptions to
Windows Firewall to enable remote access and administration to a SQL Server
box.

I don't see MS saying "turn off Windows Firewall and use someone else's
product".

So, if we put aside the whole automatic open ports distraction, wouldn't it
still be good if there was a custom DLL or package that did all the exception
additions in one go for SQL Server? Even if it had to be user initiated?

[quoted text, click to view]

Sue Hoegemeier
8/7/2007 2:13:01 PM

No. If a users knows enough about what risk they may be
introducing then manually setting up exceptions and opening
ports should be a fairly simple task for that user.

-Sue

On Mon, 6 Aug 2007 17:14:06 -0700, Andrew Hayes
[quoted text, click to view]

Andrew Hayes
8/8/2007 3:50:21 AM

Sorry Sue, but "fairly simple"? Have you read through what's needed to get a
development machine connected to a remote SQL Server, especially if OLAP and
SSRS are needed?

How to: Configure a Firewall for SQL Server Access
How to: Configure Windows Firewall for Analysis Services Access
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services
Connectivity Scenarios
Configuring a Report Server for Remote Administration
How to use a script to programmatically open ports for SQL Server to use on
systems that are running Windows XP Service Pack 2

Of course, most of the above relate to running SQL Server on a Windows XP
SP2 box rather than having a Windows XP SP2 box connect to SQL Server running
on a Windows 2003 SP1 server.

Oh. You meant turn off Windows Firewall... Yeah. That's fairly simple.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server connect

sql server connect : SQL Server Specific Windows Firewall Exception