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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cisco AP to get ip from DHCP 10 75
Server 2012 R2 TLS 1.2? 2 41
SBS2008 and windows updates 2 13
Cleaning up a desktop after leaving a domain 3 27
No security measures warrant 100% as a "silver bullet". The truth is we also cannot assume anything but a defensive and vigilance posture. Adopt no trust by default and reveal in assumption. Only assume anonymity or invisibility in the reverse. Safe…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
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…

777 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