SQL Server rule in Advanced Firewall Rule only works if using 'Public' profile in a domain environment

Posted on 2010-08-25
Medium Priority
Last Modified: 2013-12-04
SQL2K8R2 server host W2K8 std is a domain member.  SQL server manager is on a WindowsXP workstation also a domain member.  Port 1433 firewall rule only allows connection if the 'public' firewall profile is checked.  Doesn't work if just the 'Domain' or 'Private' profiles.

Shouldn't two computers joined to the same domain work in the 'Domain' profile for the Advanced Firewall inbound rule?  How do I troubleshoot this or am I not understanding the difference between the three profiles correctly?
Question by:wessir
  • 3
  • 2
LVL 12

Expert Comment

ID: 33526354
Is the server multi-homed, or does it have multiple NICs that are teamed to a single connection?

Basically, if the server is authenticated to a DC over a certain adapter, that adapter is placed in the Domain profile. No choice.

But this thread appears to mention a specific problem with Broadcom teaming adapters, with a solution:

Author Comment

ID: 33526861
It has multiple NICS as well as Hyper-V virtual networks.  The SQL server is listening on  The network connection associated with that IP is showing the domain name under it.  But I believe the machine was joined to the domain using a different network connection and that network connection is showing 'Unidentified network' under it.

I think you hit it with your answer, is there an easy way to change this?
LVL 12

Assisted Solution

Rant32 earned 2000 total points
ID: 33530935
I've been looking at a 2008 server myself, but it's not easy to reproduce.

I've added a Host-only, a NAT and a Bridged connection to the VM, and two of those connections have a default gateway through which the DC is reachable (Bridged and NAT, I'm using DHCP on both but a static DNS server on the Bridged connection).

Consequently, the Bridged and NAT connections get the Domain profile applied. The Host-Only is an 'Unidentified network' and is classified as Public.

You may have a look at the interface, to see if it has a default gateway that leads to your DC.

Otherwise, there is not much to configure here. The network location is determined by the Network Location Awareness service, so you may have to look into that. I'm not aware of the exact process that goes on behind the screens of the NLASvc other than this vague article:

This article says that the domain profile "is applied to all interfaces that are authenticated to the domain controller" but that doesn't really mean anything in my book. I guess they mean that the authenticating DC must be reachable through that interface. They surely don't mean L2 authentication.

Your issue is probably not caused purely because you joined the computer on another interface. The location assignment is not static.

It may clarify things if you post a redacted screenshot of Control Panel | System and Security | Windows Firewall.

Also, it is possible to disable firewall rules for specific connections in the Domain profile:
Windows Firewall | Advanced Settings | Properties | Protected network connections: Customize

Although I don't know if that would cause 1433 to be blocked.

Accepted Solution

wessir earned 0 total points
ID: 33533227
The technet link was helpful. Thank you very much you led me right to the answer.  From the article;
While a computer may be connected to multiple network locations at the same time, only one profile can be active at a time. The active profile is determined as follows:

1.If all interfaces are authenticated to the domain controller for the domain of which the computer is a member, the domain profile is applied.

2.If at least one interface is connected to a private network location and all other interfaces are either authenticated to the domain controller or are connected to private network locations, the private profile is applied.

3.Otherwise, the public profile is applied.

To view which profile is active, click Monitoring in Windows Firewall with Advanced Security. Above the text Firewall State will be a sentence indicating which profile is the currently active profile. For example, if the domain profile is the active profile, the text is Domain Profile is Active.
Even though I can set SQL server to only listen on a particular IP which belongs to the adapter that is in the domain, only one firewall profile can be active at a time and that is determined by the weakest link so to speak.  When I disabled the other interfaces that were not domain the active profile changed to domain and the rules started working.  Attached screenshots show how to tell which profile is active.

Also noted that trying to use any of the secure connections (either by user or machine) in the firewall rules only work if the domain profile is in effect.  Also, in my case the Hyper-V virtual adapters have no associated network address so if they are enabled, the public profile goes into effect.
LVL 12

Expert Comment

ID: 33534923
Yes, I see what happened there. Good catch.

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question