pitter patter on the keyboard

Having trouble sending e-mail to @hotmail or @live addresses? Trouble like:

Diagnostic-Code: SMTP; 550 SC-001 (BAY0-MC4-F36) Unfortunately, messages from xxx.xxx.xxx.xxx weren't sent. Please contact your Internet service provider since part of their network is on our block list. You can also refer your provider to http://mail.live.com/mail/troubleshooting.aspx#errors.

The irony of course is that the URL provided gives you less information than the error message:

550 SC-001 Mail rejected by Hotmail for policy reasons. Reasons for rejection may be related to content with spam-like characteristics or IP/domain reputation. If you are not an email/network admin please contact your Email/Internet Service Provider for help.

Spam-like characteristics can mean anything but at least twice I’ve had this problem the issue was being on Hotmail’s internal IP blacklist. Like it said the first time.

First, you should check to see if you’re on any of the public RBLs: http://whatismyipaddress.com/blacklist-check. If you are, you have real problems and need to sort that out before bothering Microsoft. Chances are, however, you aren’t – especially if we’re dealing with a simple web server and not a dedicated mail server.

If you’re sure you’re really not spamming Hotmail fill out the Deliverability Issue Submission form at https://support.msn.com/eform.aspx?productKey=edfsmsbl2&ct=eformts. In a few hours the automated system will review your case and if successful you will receive an e-mail like this:

Dear Soey McSoandso

We have completed reviewing the IP(s) you submitted. The following table contains the results of our investigation.

Mitigated
xxx.xxx.xxx.xxx
The reported deliverability problem has been mitigated by the automated system. Mitigation may take 24 – 48 hours to replicate completely throughout our system. Please note that mitigating this issue does not guarantee that your email will be delivered to a user’s inbox.

Regardless of the deliverability status, Hotmail recommends that all senders join two free programs that provide visibility into the Hotmail traffic on your sending IP(s), the sending IP reputation with Hotmail and the Hotmail user complaint rates.

Junk Email Reporting program (JMRP) When a Hotmail user marks an email as “junk”, senders enrolled in this program get a copy of the mail forwarded to an address of their choice. It allows you to see which mails are being marked as junk and identify mail traffic you did not intend to send. To join, please visit http://support.msn.com/eform.aspx?productKey=edfsjmrpp&page=support_home_options_form_byemail&ct=eformts.

Smart Network Data Services program (SNDS) This program allows you to monitor the ‘health’ and reputation of your registered IPs by providing data about traffic such as mail volume and complaint rates seen originating from your IPs. To register, please visit http://postmaster.msn.com/snds/.

There is no silver bullet to maintaining or improving good IP reputation, but these programs help you proactively manage your email eco-system to help better ensure deliverability to Hotmail users.

Thank you,

Hotmail Deliverability Support Service

If not successful, being that the system is automated you may have to go hunting for a new subnet.

Both of the tools linked above are excellent sources of information if you administrate an ISP mail server or mail servers for third parties and JMRP is especially useful for tuning mass mail marketing campaigns.

I have the unenviable task of migrating some Microsoft Office Access tables into MySQL. The first step, assuming you don’t want to go through the pain of setting up a MySQL ODBC connection is to export these tables to Excel then use Excel to export the spreadsheets to CSV format. One might think one would be able to export to CSV directly from Access but one might think a lot of things about Microsoft products.

You’ll need to create your table structure in MySQL based on the header row of the CSV and type of data in the columns. Don’t create any auto-incrementing ID index or any of the extra columns you want to add yet; this will have to be done after the import since MySQL will treat the columns in your CSV on a FIFO basis. Be sure to structure your table accordingly.

Once your table has been prepared edit the CSV and delete the column header row, any blank or invalid lines and check the cells for formatting. If you are trying to import prices to a FLOAT column and they include currency symbols, spaces or delimiters you are going to have problems if you don’t take this into account first. Excel wraps cells containing commas (or whatever cell delimiter you specified) in double quotes; MySQL will read these commas as delimiters anyway so if you need them included in your dataset you might need to choose a more appropriate delimiter instead (the pipe | for example). These double quotes will also negatively impact cells of a type which do not support them (INT, FLOAT) and be included in those that do (VARCHAR, TEXT) so it is advisable to run a search-and-delete prior to importing.

Once your CSV passes inspection upload it to your SQL server and after logging in to the MySQL CLI and selecting the appropriate database:

mysql> load data local infile 'myfile.csv' into table `mytable` fields terminated by ',' lines terminated by '\n';
Query OK, 2189 rows affected, 85 warnings (0.01 sec)
Records: 2189  Deleted: 0  Skipped: 0  Warnings: 84

Replace ‘,’ with an appropriate delimiter if you are not using a true CSV, i.e. ‘\t’ for TSV (tab separated values).

If this has produced warnings you’re going to have to TRUNCATE TABLE, go back to your CSV and inspect the formatting again. Run

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1262 | Row 487 was truncated; it contained more data than there were input columns  |
| Warning | 1265 | Data truncated for column 'thing1' at row 531                                |
| Warning | 1265 | Data truncated for column 'thing2' at row 531                                |

...

to see what went wrong and identify the line number(s) requiring modification.

ClearOS is the latest incarnation of ClarkConnect, a linux-based router/network appliance distribution produced by the similarly re-branded Clear Foundation.

This post will show you how to gently abuse Gateway Mode to set up a single PPTP tunnel for use with multiple private subnets if you need to use a standalone VPN server. This does not apply if your VPN server is the gateway for your private subnets.

If you run a large network chances are you don’t want to bugger up your clients’ ability to pptp out. That means, for all practical “supported” purposes, you can’t use your ClearOS/CC gateway(s) as PPTP servers. The answer of course is to set up a “standalone” VPN server. If you’re working with a $0 budget or you pay for your rack space this might not be an attractive solution. You can always turn an existing physical server into a VPN gateway but for the purposes of this guide we’re going to assume you have a dedicated server or virtual machine.

In both cases the machine needs to have no less than two ethernet interfaces. If you’re using a VM I find that Clear fits nicely into 96 megs of ram with 20 megs room to breathe once you’ve trimmed off all the crap you don’t need (hald, messagebus, iscsid, etc – anything you won’t use on a VM unless you’re an X nut). Use

then

for every excess service that loads in runlevel3. If you use physical hardware you will need to ensure both NICs are plugged into a switch otherwise they won’t come up on boot (or, naturally, you can alter the networking init script).

For this example, you have three private networks at headquarters:

10.0.0.0/8
172.16.0.0/12
192.168.0.0/24

And your client will be on a local NATted subnet of say

192.168.1.0/24

Log into the webconfig of your new VPN server. Make sure Gateway Mode is selected. Set eth0 to external mode and assign a routed public address and gateway to it, this is the IP you’re going to come in on. Now for each of the private networks add an IP to a virtual interface ON ETH0. Add an IP somewhere that will never be used to eth1 thus:

By now you’re wondering why we have this dummy eth1 that does nothing. It’s very simple – we’re tricking CC into routing these subnets over the vpn by using the built in Gateway Mode. I can’t stress this enough: what we’re doing is not ideologically correct. It is, however, clean and simple.

Enable the PPTP server if you have not already done so and set the IP pools to some unobstructed address space on one of your private subnets. Make sure the ClearOS firewall is happy passing GRE (it will tell you if it’s not on the PPTP webconfig page).

Now on the client end you’re going to create a PPTP connection just like you always would except for one twist: once the connection is established add the private subnets to the client’s routing table on the ppp interface thus:

And presto! You can talk to any host on any of those networks with one IP and one tunnel thanks to a gentle abuse of Gateway Mode. I like to speed this up by adding those lines to a short shell script.

If you’re a windows user, leaving Use Remote Gateway checked in the connection preferences will get you the same effect with one small hitch – all of your traffic (web browsing, chat etc) will be routed through your VPN and any time you connect or disconnect whatever open connections you have will be reset.

Without getting too much into details you want to (at a CLI) type:

route print

remember the hex number for your PPP connection. Type:

route add 10.0.0.0 mask 255.0.0.0 192.168.0.66 metric 2 if (hex number)

You probably don’t need the metric bit but some of the people connecting to my particular vpn server will sometimes be connecting to their own subnets and that’s bad for routing. Substitute 192.168.0.66 with the private IP your VPN server sits on in the same subnet as the PPTP IP pool.

Naturally, replace 10.0.0.0/8 with whatever private subnet you’re trying to add (you don’t need to do the subnet you’re actually put on by the PPTPd, that’s automagic). For additional subnets: Lather. Rinse. Repeat.

Everything I’ve read so far says windows will automagically detect what interface to use based on the gateway you specified but that hasn’t worked for me. If it works for you, great! Use the -p flag to save the routes to your registry. You will find however that there is nothing static about windows interface numbers particularly when dealing with on-again-off-again VPN connections.