?
Solved

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

Posted on 2010-08-25
5
Medium Priority
?
699 Views
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?
0
Comment
Question by:wessir
[X]
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
5 Comments
 
LVL 12

Expert Comment

by:Rant32
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:
http://community.spiceworks.com/topic/85898
0
 

Author Comment

by:wessir
ID: 33526861
It has multiple NICS as well as Hyper-V virtual networks.  The SQL server is listening on 10.20.0.29  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?
0
 
LVL 12

Assisted Solution

by:Rant32
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 10.20.0.29 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:
http://technet.microsoft.com/en-us/library/cc722141%28WS.10%29.aspx

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.
0
 

Accepted Solution

by:
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.
WFwAS-screenshot.jpg
WFwAS-screenshot-DOMAIN.jpg
0
 
LVL 12

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
Suggested Courses

771 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