Video Screencast Help

Blocking Traffic by Country on Production Networks

Created: 15 Jul 2008 • Updated: 02 Nov 2010
Language Translations
Admin's picture
0 0 Votes
Login to vote

by Timothy M. Mullen

When I originally posted to Bugtraq regarding the use of country-by-country sets to control traffic to or from any particular country, I knew that it was not a new idea. However, applying the concept for use with Microsoft's ISA Server was at least a new application for it, and apparently has had some utility for people based on the thousands of downloads that have been made of the free sets from the Hammer of God Web site.

As promised in that post, here is some more detailed information on the use of country-by-country data sets in firewall configurations, where it may be appropriate, and methods by which one may use the sets to create traffic reports. While the methods listed and tools available are created specifically for ISA, the concept can be applied to any product that supports the necessary data elements.

Before we begin, I will post a disclaimer: This is a technical discussion. Nothing in the following dissertation is motivated by any political, religious, racial, or other ideal or prejudice. Processes discussed and opinions reached are based on technical and statistical research -- No other bias is intended nor should be inferred.


A few months back, David Litchfield from NGSSoftware was working on a research project on SQL security and asked if I could assist him by setting up a constant network capture looking for SQL Slammer attacks.

One will recall that the SQL Slammer worm exploited a vulnerability in the SQL Browser service of Microsoft SQL Server 2000 and MSDE a single packet payload delivery over UDP 1434 to randomly -- or psudo-randomly -- generated IP targets. Slammer was launched back in January of 2003, so I was curious as to what I would find. I had long-since moved all published services from my home office, and had a single high-speed cable connection for access. Given Slammer's "random" target IP generation, I thought it would be a good environment to test in, and to be honest, I was a bit dubious on what the results would be as I tend to hope that gaps of over 5 years in a vulnerability will result in eventual patching.

Unfortunately, that was obviously not the case.

In a little under 4 days, I received over 30 unique Slammer attacks which were verified by packet inspection and confirmed not to be simple probes to UDP 1434. I was surprised, but not by the number of attacks. Upon further investigation, I found that every single attack -- all 30 of them -- were sourced from China, which really piqued my interest. I've seen many, many different reports of different attacks from different worms as well as reports of generalized Internet traffic by protocol and port, but this was the first time I had seen firsthand where a worm attack was sourced from a single country.

The result got me thinking: What was special about China in regards to Slammer? Was it true what others have said about China being a festering cesspool of malicious network activity? Were the boxes just all unpatched? Was China just being used as a launching pad for attacks sourced elsewhere? More importantly, what other malicious traffic was sourced there? And what other countries exhibited similar behavior with other protocols?

Before I could answer any of these questions, I had to first figure out how to reliably -- or reliably enough -- determine what country the source IPs were from. This required building a database of IP ranges that I could use to report from. There are a number of sources around the Internet once can use for such purposes: some are for pay, some are free. Depending on how much work you want to do, how much you are willing to pay, and how often you need updates, you'll have to make your own choice. Of course, I've already got the sets for ISA made and available for download for free, or you can compile your own database from sources on the Internet.

The list I ended up with contained over 230 countries comprised of over 100,000 individual records of IP ranges. Obviously, I had a lot more work to do in order to make traffic reporting easy enough to work with on a continuing basis. Enter Microsoft ISA Server. ISA Server is an enterprise firewall and caching product that supports extensive reporting, scripting, and customization options. You can use any product you wish, of course, as long as it provides you the tools you need. I'll be using ISA in all following examples.

The goal required several steps: I had to load all the records into SQL Server, which was easy enough. Then, I had to set up several test ISA Servers configured to log all traffic to SQL, and combine those servers' logs into one huge database I could use for report. Then, ultimately, I would need to create country-by-country "sets" in ISA comprised of each countries multiple lists of IP ranges so that I could create firewall rules to act upon the traffic by country.

Compiling the Data

As far as SQL was concerned, the table format I ended up with was simple enough. Let's call it the IPAddresses table:

BegIP (nvarchar 16), EndIP (nvarchar 16), BegIPLong (bigint), EndIPLong (bigint), Cntry (nvarchar 50), and the FullCntry (nvarchar 75)

BegIP and EndIP are both character fields containing the dotted-IP format of the IP address, but the really important fields are the BegIPLong and EndIPLong which are long integer representations of the IP Address. The SQL data doesn't do us much good if we can't easily match up what country goes with what firewall log record based on the IP. When you have a range of IPs formatted in dotted notation, you can't easily perform logical record retrieval using operands like between, so having a mechanism by which one can easily retrieve records is essential. Besides, when ISA Server logs IP addresses to SQL, it first converts it to a Long Int, so we might as well take advantage of that here. He's the list of IPs for Papua New Guinea (used because it is a short list of IP ranges).

BegIP EndIP BegIPLong EndIPLong Cntry FullCntry 1966417920 1966419967 PG Papua New Guinea 3231718656 3231718911 PG Papua New Guinea 3278942296 3278942299 PG Papua New Guinea 3389018112 3389018367 PG Papua New Guinea 3389071360 3389079551 PG Papua New Guinea 3389124608 3389128703 PG Papua New Guinea 3392831488 3392832511 PG Papua New Guinea 3392995328 3392995583 PG Papua New Guinea 3395272704 3395276799 PG Papua New Guinea 3399860224 3399864319 PG Papua New Guinea 3400265728 3400267775 PG Papua New Guinea 3411218432 3411220479 PG Papua New Guinea 3633031984 3633031991 PG Papua New Guinea

Converting dotted notation to a long integer is easy enough as well. You just break the dot notation octets into their base decimal values multiplied by the decimal value of each octet itself, and add each octet's base * value product together. For example, let's take the next to the last entry from above - through The base for each octet is 256 to the power of the octet column's position, or 2563.2562.2561.2560 or further extrapolated to 16777216.65536.256.0 respectively. So in this case, for the beginning IP, you'll take (16,777,216 * 203) + (65,536 * 83) + (256 * 16) + (0 ) or 3,405,774,848 + 5,439,488 + 4,096 + 0, which equals 3,411,218,432 as indicated above in the BegIPLong field. Using the same process, we see that the EndIPLong field would be 3405774848 + 5439488 + 5888 + 255, or 3400220479 as again indicated above. (Note that for the last octet of 2560 you just use the decimal value of the IP address, not 0 * the value which would always be 0.)

Converting the beginning and ending IP address to a long integer like this within the IPAddresses table allows us to easily retrieve the Country value from the associated IP range record where the long integer IP address in the Firewall log is between the beginning and ending long integer IP referenced in the table. The ISA Firewall Log table structure is well documented, so I won't eat web space here detailing it; however, I will reference some important, basic fields therein: ID (bigint), logtime (datetime), protocol (varchar 32), SourceIP (bigint), SourcePort (int), DestinationIP (bigint), DestinationPort (int) works for now. Let's call this the FirewallLog table.

Consider this single record matching the above columns that might be found in the FirewallLog:

"1044432","2007-11-08 12:26:35.157","TCP","3684096418","45887","3515032734","443"

If we wanted to find out what country this connection came from via the IPAddresses table and the FirewallLog table, we would execute a SQL statement similar to this:

select FirewallLog.*, IPAddresses.FullCntry from FirewallLog,IPAddresses where FirewallLog.SourceIP between IPAddresses.BegIPLong and IPAddresses.EndIPLong and FirewallLog.ID = 1044432

Yielding the results:

"1044432","2007-11-08 12:26:35.157","TCP","3684096418","45887","3515032734","443","China"

So, here we see how we can link the IPAddresses tabular data to our ISA (or IIS, etc for that matter) log data to report on traffic by source (or destination) country.

Functions and Procedures

Before we move on, here's a helpful function that will come in handy. As already stated, the ISA SourceIP and DestinationIP fields are stored as long (big) integers. This is great for a WHERE or JOIN statement, but not so great when it comes to reporting and verification. In the same way that we convert dotted IP notation to a long integer, we need to be able to convert the long integer back to a dotted notation. When logging ISA to SQL, you can't just change the table structure to whatever you want as the insert job will fail -- ISA expects the table format to be what it is "supposed to be." So you just can't add a SourceIPDot field to the FirewallLog table and update it when you want to. Therefore, I decided to create a scalar function in SQL to do this for me at query runtime.

The following SQL will create a function for you called dbo.ConvertLongIP:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Timothy "Thor" Mullen -- Create date: 03/01/08 -- Description: Scalar function to converts long/bigint formatted IP addresses to dot notation -- ============================================= ALTER FUNCTION [dbo].[ConvertLongIP] ( -- Input parameter for long (bigint)IP here @LongIP bigint ) RETURNS varchar(15) AS BEGIN DECLARE @DotIP varchar(15), @bin varbinary(4) select @bin = cast(@LongIP as varbinary(4)) select @DotIP = cast(convert(int,substring(@bin,1,1)) as varchar(3)) + '.' + cast(convert(int,substring(@bin,2,1)) as varchar(3)) + '.' + cast(convert(int,substring(@bin,3,1)) as varchar(3)) + '.' + cast(convert(int,substring(@bin,4,1)) as varchar(3)) RETURN @DotIP END

Once the function is created, one can now easily determine the source IP address by including the following function call in the original SQL statement:

select FirewallLog.*, dbo.ConvertLongIP(SourceIP),IPAddresses.FullCntry from FirewallLog,IPAddresses where FirewallLog.SourceIP between IPAddresses.BegIPLong and IPAddresses.EndIPLong and FirewallLog.ID = 1044432

Which now yields : "1044432","2007-11-08 12:26:35.157","TCP","3684096418","45887","3515032734","443",
"","China" Notice the inclusion of the address. Now we have the full reporting capabilities that we want in order to begin our due diligence in research before choosing to block (or allow) traffic to or from any particular country.

Reporting Examples

Here is some sample data from a compilation of several network capture point set up at different parts of the world over a few days - let's start with SMTP:

Country Prot Port Connections
United States TCP 25 290964
Russian Federation TCP 25 171858
China TCP 25 133314
Turkey TCP 25 105648
Poland TCP 25 97635
Brazil TCP 25 96870
Korea, Republic of TCP 25 87570
Spain TCP 25 82584
Germany TCP 25 72645
Mexico TCP 25 66672
United Kingdom TCP 25 53868

For my environments, I have verified that there is no valid traffic coming out of Russia, China, Turkey, Poland, and Korea. And very, very little valid SMTP traffic comes from Brazil and Mexico to my production networks. While a large portion of the US-based SMTP traffic is indeed spam, I at least now have the opportunity to analyze traffic and make decisions based upon my research.

Now let's look at HTTP:

Country Prot Port Connections
Brazil TCP 80 597351
United Kingdom TCP 80 97704
United States TCP 80 69210
Japan TCP 80 29331
Russian Federation TCP 80 10158
Germany TCP 80 7236
Turkey TCP 80 6972
China TCP 80 6945
India TCP 80 4332
Italy TCP 80 3861
France TCP 80 3744

I find it very interesting to see so much HTTP traffic out of Brazil -- most of which is not needed in my environment. The beauty of this system is that it allows for easy correction to my web server logs to match up connection sources with HTTP requests in order to subsequently validate the traffic itself to make determinations of what to block and from where (or what to allow). In my environment (and that of my colleagues), we actually receive a non-trivial amount of valid traffic from Brazilian sources, so even though a vast percentage of the traffic is indeed invalid for our purposes, we've made the decision to allow HTTP from Brazil in order to best serve the people who count on access to the data we provide. In this case, we have to take the bad with the good -- but at least we arrived at the decision based on fact. Here's one that took some research to nail down:

Country Prot Port Connections
United States TCP 32085 199137
United States UDP 32085 122667
United Kingdom TCP 32085 72960
Japan TCP 32085 62946
Poland TCP 32085 42408
Canada TCP 32085 41916
France TCP 32085 41226
Germany UDP 32085 40236
Israel TCP 32085 36858
France UDP 32085 35874
Spain UDP 32085 32877
United Kingdom UDP 32085 27249
Canada UDP 32085 26754
Australia TCP 32085 21942
Sweden UDP 32085 18444
Mexico TCP 32085 18066
Germany TCP 32085 14790
Australia UDP 32085 13839
Netherlands UDP 32085 13194
Poland UDP 32085 10668
Norway TCP 32085 9780

At first, I had no idea what UDP/TCP 32085 was for -- yet I was seeing substantial traffic from all over the world. Nothing turned up on Google, so a quick packet capture got me the information I needed. It was BitTorrent. Seems that someone inside one of our networks was "a bit active" in the torrent scene. This traffic is easy enough to block in its entirety without respect to the source countries, but I found the distribution of players in other countries interesting in its own right.

Creating Objects in ISA

When one is finally ready to create firewall rules based on the research they've done, one first has to take the IPAddress table information and use it to create "sets" or collections of the IP ranges each country "owns" in ISA. When the appropriate computer set is created, it's a simple matter of selecting that computer set as a From or To source or destination in the rule to block or allow the traffic. Again, I've already created these for you in .xml format that can easily be imported into ISA, but here's how I went about programmatically creating the sets.

In my test VM, I installed ISA Server and Microsoft Access. The Access database simply has a copy of the IPAddresses table imported from SQL (or as a linked database, your choice) . This was done in a test VM as you would never load Microsoft Office products on your production firewall.

This code creates the Computer Sets based on the IPAddress data:

 Private Sub BuildISAComputerSets_Click()     On Error Resume Next          ' Create the root FPC object.     Dim root  ' The FPCLib.FPC root object     Set root = CreateObject("FPC.Root")     ' Declare the other ISA objects needed.     Dim isaArray     Dim ComputerSets     Dim ComputerSet     Dim AddressRanges     Dim AddressRange          Dim rstCountries As Recordset     Dim rstAddresses As Recordset     Dim sCountry As String     Dim sSQL As String     Dim sRangeName As String     Dim sLogText As String          'Connect to ISA     Set isaArray = root.GetContainingArray()     Set ComputerSets = isaArray.RuleElements.ComputerSets          Log.SetFocus       'Get a distinct list of countries          sSQL = "SELECT distinct IPAddresses.FullCntry          FROM IPAddresses order by FullCntry "          Set rstCountries = CurrentDb.OpenRecordset(sSQL)          Do 'Countries loop      Log.Text = ""      sCountry = rstCountries!CountryName      Log.Text = Log.Text + "Working on " + sCountry           + Constants.vbNewLine      Set ComputerSet = ComputerSets.Add("ThorSet_" + sCountry)      sSQL = "Select BegIP,EndIP,BegIPLong,EndIPLong,Cntry,FullCntry          from IPAddresses where CountryName = '" + sCountry          + "' Order by BegIPLong"              Set rstAddresses = CurrentDb.OpenRecordset(sSQL)        Log.Text = Log.Text + Str(rstAddresses.RecordCount)             + " address ranges found" + Constants.vbNewLine        Do 'Addresses Loop         sRangeName = Trim(rstAddresses!Country)          + Trim(Str(rstAddresses!BegIpNo))                + "-" + Trim(Str(rstAddresses!EndIpNo))         Set AddressRanges = ComputerSet.AddressRanges         Set AddressRange = AddressRanges.Add(sRangeName,                rstAddresses!BegIP, rstAddresses!EndIP)       rstAddresses.MoveNext             Loop Until rstAddresses.EOF           Log.Text = Log.Text + "... saving"      'ComputerSet.Save      rstCountries.MoveNext     Loop Until rstCountries.EOF            ComputerSets.Save          MsgBox ("Done.")    End Sub 

Note that the .Save command comes after all the set creation -- this is because every time you invoke a .Save, ISA will parse through all rules and sets -- I learned this the hard way. If you wait until you are finished creating the sets, they stay in memory, and you only have to parse through via the .Save command one time. The Log.Text reference is a simple text control on the form calling the code that give simple text progress as to what is happening. Once you've created your sets and verified them, you'll want to export them all at once so you're not sitting there right-clicking on each set and exporting one at a time when you go to import them into your production ISA box -- or the selected ones you may need -- if you export all sets at one time in the GUI, you'll get a huge single file with all set data included. This code creates a single XML file for each set in the C:\TEMP directory.

Private Sub ExportComputerSetsToXML_Click()

 'On Error Resume Next          ' Create the root FPC object.     Dim root  ' The FPCLib.FPC root object     Set root = CreateObject("FPC.Root")     ' Declare the other ISA objects needed.     Dim isaArray     Dim ComputerSets     Dim ComputerSet     Dim AddressRanges     Dim AddressRange     Dim sFilename As String     Dim i As Integer          Log.SetFocus     Log.Text = ""     'Connect to array     Set isaArray = root.GetContainingArray()     Set ComputerSets = isaArray.RuleElements.ComputerSets          i = 0     For Each ComputerSet In ComputerSets      i = i + 1      'Set ComputerSet = ComputerSets.Item      sFilename = ComputerSet.Name + ".xml"      Log.Text = Log.Text + "Exporting " + sFilename + "..."      ComputerSet.ExportToFile "C:\temp\" + sFilename, 0      If i = 20 Then Log.Text = "": i = 0     Next      End Sub 

Where to go from here?

Now, it's really up to you. When I brought up blocking China and other countries on an internal technical discussion list, some of my colleagues thought it was a really bad idea, and even "heavy handed" as it was put. Apparently not everyone agrees with me in that my network is mine to do with as I choose. And that's fine -- let them disagree.

However, I don't think there is anything at all wrong with doing your due diligence in research and them making informed, intelligent decisions about your network traffic policies that are rooted in actual research and fact-finding. Should you really block all traffic from China? I sure have, and I'm glad I've done so. Back-end accounting of the data blocked has proved without a shadow of a doubt that insofar as my network and environment is concerned, that I've made the right choice. Pursuant to that point, it is important to note that a blocking policy should not be a "fire and forget" solution. After-the-fact auditing is not only important for ongoing justification of blocking (or allowance) policies, but it also gives you an opportunity to spot otherwise valid traffic and to allow for policy alterations in the future. At some point, it may be a wise choice to block ALL Brazilian HTTP traffic, or to allow SMTP from Turkey, or to add North Korea and/or Afghanistan to our BLOCK ALL TRAFFIC policies.

This method also allows for the easy inclusion of "exception" policies based on particular IPs. If you are indeed blocking China in its entirety, but then come to find out that a particular business unit in China needs access, you can just carve out an exception allow rule that is parsed before the BLOCK ALL rule to give that IP range access to the necessary resources.

It may sound like a substantial amount of work to maintain, but it really isn't once everything is in place. Besides, you'll save all kinds of time from the simple obviation of log entries that you used to have to pore over.


When implemented properly, I think geographically blocking traffic is a good idea. It conforms to the principles of security-in-depth and Least Privilege. Even if you don't decide to block traffic immediately, you at least now have some valuable tools and techniques that will allow you to better report on and take action against sources whose traffic has no business reaching your network. This obviously isn't a blanket solution that fits every situation. Some businesses won't be able to implement policies like this at all - and that's just fine. But where one can, you at least have the option of easily creating reports and making policy that will help secure your infrastructure.

Timothy Mullen is the Vice President of Consulting Services for NGSSoftware, an international security software and consulting firm. Mullen has been awarded the Microsoft MVP in Enterprise Security for the fourth straight year, and is leading the "Microsoft Ninjitsu" training course at the Black Hat Briefings in Las Vegas.

This article originally appeared on -- reproduction in whole or in part is not allowed without expressed written consent.

Article Filed Under: