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

Posted on 2010-08-25
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

SHARE your personal details only on a NEED to basis. Take CHARGE and SECURE your IDENTITY. How do I then PROTECT myself and stay in charge of my own Personal details (and) - MY own WAY...
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

690 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