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.
Background
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 |
117.53.40.0 |
117.53.47.255 |
1966417920 |
1966419967 |
PG |
Papua New Guinea |
192.160.29.0 |
192.160.29.255 |
3231718656 |
3231718911 |
PG |
Papua New Guinea |
195.112.176.88 |
195.112.176.91 |
3278942296 |
3278942299 |
PG |
Papua New Guinea |
202.0.80.0 |
202.0.80.255 |
3389018112 |
3389018367 |
PG |
Papua New Guinea |
202.1.32.0 |
202.1.63.255 |
3389071360 |
3389079551 |
PG |
Papua New Guinea |
202.1.240.0 |
202.1.255.255 |
3389124608 |
3389128703 |
PG |
Papua New Guinea |
202.58.128.0 |
202.58.131.255 |
3392831488 |
3392832511 |
PG |
Papua New Guinea |
202.61.0.0 |
202.61.0.255 |
3392995328 |
3392995583 |
PG |
Papua New Guinea |
202.95.192.0 |
202.95.207.255 |
3395272704 |
3395276799 |
PG |
Papua New Guinea |
202.165.192.0 |
202.165.207.255 |
3399860224 |
3399864319 |
PG |
Papua New Guinea |
202.171.240.0 |
202.171.247.255 |
3400265728 |
3400267775 |
PG |
Papua New Guinea |
203.83.16.0 |
203.83.23.255 |
3411218432 |
3411220479 |
PG |
Papua New Guinea |
216.139.171.48 |
216.139.171.55 |
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 - 203.83.16.0 through 203.83.23.255. 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",
" 219.150.217.162","China" Notice the inclusion of the 219.150.217.162 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.
Conclusion
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 SecurityFocus.com -- reproduction in whole or in part is not allowed without expressed written consent.