Category Archives: geekiness

How to route all Opnsense traffic through your private VPN

This is for your home network, yes.  I tried several how-tos, and basically had to figure it out for myself.  Here’s how I did it.

Preface:  I have two LANs;  one for computers, tablets, and phones, and the other for IOT devices, including my Roku (which cannot use a VPN).  I’m using Opnsense 18.7, so menu item labels or locations might change in the future.

Step 1:  Get all your certificates and such from your VPN provider.  They might give you a single file with labeled sections or a series of files.  You need:

  • somename.ovpn  (If you open this file with Notepad or Wordpad, it contains all the below except for the username and password, but it might also contain other directives for your provider.)
  • CA.crt (Certificate Authority–it might be a section of your .ovpn file.)
  • TA.key (OpenVPN Static key–Some VPN providers will give you a username and password instead of a static key. It might be a section of your .ovpn file.)
  • User.crt (User Certificate–it might be a section of your .ovpn file.)
  • User.key (RSA Private Key–it might be a section of your .ovpn file.)

Step 2:  Log into Opnsense and navigate to System -> Trust -> Authorities.  Click Add.

  • Put anything (within reason) in “descriptive name.”  I suggest the name of your VPN provider.
  • Leave Method “Import an existing Certificate Authority”
  • Paste the contents of your CA.crt (Certificate Authority) file.  If you just have one big file, copy and paste the CA certificate including the “begin” and “end” bits.
  • Click Save.

This Certificate Authority should now show up in Opnsense on the Authorities page.  Opnsense should fill in information about your VPN provider here that it got from the certificate.

Step 3:  Navigate to System -> Trust -> Certificates.  Click Add.

  • Leave Method “Import an existing Certificate”
  • Put anything (within reason) in “descriptive name.”  I suggest the name of your VPN provider.
  • Paste the contents of User.crt into Certificate Data.  If you just have one big file, copy and paste the user certificate including the “begin” and “end” bits.
  • I think I pasted the contents of User.key into the private key area, but the How-To I followed left this blank, so apparently both work.
  • Click Save.

If you view Certificates, Opnsense will now have more information about your certificate (including email address of issuer, etc.).

Step 4:  Navigate to VPN -> OpenVPN -> Clients.  Click Add.

This is where the How-To guides started to fall apart for me.  You might need to trial and error a little here.  You’ll also need to open that .ovpn file with Notepad or Wordpad (if you haven’t already).

  • Put anything (within reason) in “description.”  I suggest the name of your VPN provider.
  • Server Mode: Peer to Peer (SSL/TLS)
  • Protocol: (check your ovpn file)
  • Device Mode: (check your ovpn file)
  • Interface: (Your WAN interface–it’s probably literally called “WAN”)
  • Local port: (mine is blank, but check your ovpn file)
  • Server Host or Address: (check your ovpn file)
  • Server Port: (check your ovpn file)
  • Server host name resolution: Ticked

Cryptographic Settings:

  • TLS Authentication: Ticked unless you’re using username and password instead.  (If you’re using username and password, put them in the blanks.  If you’re using TLS, paste the data in your ta.key file here, AKA OpenVPN Static key.  If this option doesn’t appear, enter everything else, click save, and look again.)
  • Peer Certificate Authority: Select whatever you called it in step 2.
  • Client Certificate: Select whatever you called it in step 3.
  • Encryption: (check your ovpn file)
  • Auth Digest Algorithm: (check your ovpn file)
  • Disable IPV6: Ticked

Click Save.

The various How-Tos debate whether you need to paste the advanced settings from the ovpn file into the advanced settings on the Add Client page.  My tunnel didn’t work until I cleared all that data out.  Sigh.  In other words, try it without the advanced settings and see if it works first.

Step 5:  Check to see if your tunnel is working by navigating to VPN -> OpenVPN -> Connection Status.

You should see Status Up with a Virtual IP and all that good stuff.  If you don’t, navigate to VPN -> OpenVPN -> Log File and see if it says anything useful there.  You’ll probably then have to go back to Step 4 and tweak the settings to look more like your .ovpn file.  You might also need to contact your VPN provider if you’re really lost.

You cannot proceed further until you have a working tunnel.

Step 6:  This is where the other guides really went off the rails for me.  (Sorry, guys!)  This is what I did.  It may or may not be the best way to do it, but it worked.  Navigate to Firewall -> NAT -> Outbound.

  • Click “Hybrid outbound NAT rule generation (automatically generated rules are applied after manual rules) ,” then click Save.

Click Add.

  • Interface:  OpenVPN
  • Translation target:  “Interface Address”
  • Leave everything else any or default.

At this point, all of your traffic is going through your VPN.  YAY! except that Netflix, Hulu, etc. are now giving you an obnoxious “You appear to be using a proxy/unblocker, turn it off or no media for you!” message.  This is where my having the Roku on its own LAN makes things easy.  If you don’t have your network segregated like that, you can assign a static IP to your Roku or other streaming device and do the following steps with that static IP.

Step 7 (optional, unblocking Netflix on your Streaming Devices):  Navigate to Firewall -> Rules.

If your streaming devices have their own network card in Opnsense (mine do):

  • Navigate to Firewall -> Rules -> Opt1 (or whatever that network card is called).
  • Click Add
  • Leave everything default except changing the Gateway to WAN_DHCP (or whatever it’s called).
  • Click Save.

If your streaming devices have static IPs on your regular LAN:

  • Navigate to Firewall -> Rules -> LAN.
  • Click Add
  • Leave everything default except changing the source to your device’s static IP and the Gateway to WAN_DHCP (or whatever it’s called).  (If you have more than one, create an Alias first in Firewall -> Aliases -> View, click Add.  Type host, add however many lines for however many streaming devices you have, click save, and use that Alias as the source.)
  • Click Save.

If you want to get super-fancy and still access Netflix from any computer while sending all other traffic through the VPN, you probably need to create an alias with this information and route all traffic to that destination through WAN_DHCP.  That’s, like, more than I really wanted to get into for this article, though.  You can also turn off the VPN, or create a rule that sends your laptop straight through WAN_DHCP and turn it off or on, but those both suck (IMHO).

Happy tunneling!

Comments Off on How to route all Opnsense traffic through your private VPN

Filed under geekiness

Backups FTW!

I could say it’s because I’m a DBA and SysAdmin and it’s an occupational hazard, and frequently do.  I could also say it’s because I’m neurotic.  😉  But it’s probably the most honest to say, “Because I once experienced a catastrophic data loss…”

For whatever reason–pick one or more above–I’m compulsive about backups of my home equipment.  I actually use two different backup programs for different use cases:

  1. “My house burned down.”
  2. “I accidentally deleted a file.”
  3. “My computer is so dead that I can’t boot it to get the data off it, so I bought a new one.”
  4. “My hard drive died.”

For the first three, just about any cloud-based solution will work.  The first product I started using I picked for a highly dorky reason:  Neil Gaiman, my author crush, tweeted about it.  Crashplan protects me against data loss caused by stolen equipment and burning houses, restores accidental deletions, and when my old laptop died it painlessly put my data on the new laptop.  (Okay, yes, I could have cracked the case, put the old drive in an external enclosure, etc.  The point is that I didn’t have to.)

However, it’s not really intended for use #4.  Well.  You could use it for use #4, after reinstalling the OS and all your apps, or putting the vendor image back on and reinstalling your apps, or whatever.  However, what I really want for use #4 is something that pulls an image.  For that, I use Acronis, which I picked because my office uses it.

Acronis has a cloud service, but my main desktop has iTunes, and multiple season passes of NCIS and Doctor Who and the like, so I’m using 390 GB.  I’m a little… iffy… about uploading 390 GB worth of data a second time.  I also haven’t compared the cloud storage prices.  However, I <3 Acronis as of last week, because my hard drive failed.  In my main desktop.  The one with all that data on it.

I replaced the hard drive with a similarly-sized non-dead drive, booted it off the previously-burned recovery media, set it to restoring, and went to bed.  The next morning, it was like nothing ever happened.  Well.  I had to reapply a Windows update and recover a file.

So, my point is… Neil Gaiman is hot enough that I buy backup programs because he tells me to?  😉

Seriously, that could have been painful and traumatic, and instead it just ran overnight and all was love and goodness.  Because I do backups.  Backups FTW!

Comments Off on Backups FTW!

Filed under geekiness

I am a winner!

I went to the Atlanta PowerShell Users Group tonight.  The topic was tips and tricks.  My trick was this script, which task scheduler reads to me in the morning. Assuming you use Hiveminder–and you should, because it’s awesome–you only have to edit the RSS locations for your location and for your to do list.

$weatherurl = "[yahoo weather RSS and city code]"
$hiveminderurl = "[hiveminder rss url here]"

You’ll need your yahoo weather city code–for example, this is Alpharetta, GA–and the RSS feed of your hiveminder to-do list for today.

I won a copy of PowerShell in Depth:  An Administrator’s Guide, by Don Jones, Richard Siddaway, and Jeffery Hicks.  Awesome!


Comments Off on I am a winner!

Filed under geekiness, powershell, scripting

SQL Saturday #111

As always, I had an awesome time at SQL Saturday.  I heard at least two people say that the SQL community is full of genuinely nice people, and it certainly seems to be true.  I followed a bunch of new people on Twitter and won a Kindle Fire!

(By the way, if you’re a SQL person and are not on Twitter, you really should be.  I’ve specifically heard that you want to know about #sqlhelp.)

The amusement started when my friend iffer pointed out that they were using a Lord of the Rings theme.  The panel tracks were named things like Merry, Pippin, Arwen, Aragorn, and Gandalf.  Hee!

As a DBA stolen originally from the System Administration side of the house, I spent most of my time in panels with words like “internals” in their titles.  I’ve always believed that the more you know about how things really work, the more able you’ll be to fix them.  Brian Kelley and Denny Cherry in particular fed the geek within with the FREAKING AWESOME “Windows Operating System Internals for DB Pros” and “Index Internals.”  I also enjoyed Brian’s professional development panel (“Being the Swiss Army Knife of DB Pros“).  (The other panels I went to were also really good.)

I also want to put in a plug for the PASSWIT informal lunch.  (Photo by Jennifer Levy.)  We talked about outreach to girls about STEM careers, and asking for better Barbies.  Tell your daughters/nieces/young cousins that they can go into computers today!  Encourage a girl in math or science!  (By the way, the man in the photo is a college computer science professor.  W00t!!!)

Biggest SQL Saturday regret?  I didn’t make it to any PowerShell panels this time. 🙁  I’ll have to download some and check ’em out.  (I’m particularly interested in “PowerShell Modules You Should Know About.”  I’m also interested in “Manage SQL Server 2012 on Server Core w/PowerShell,” but my interest in that is pretty academic, as I think my employer is unlikely to be using either SQL Server 2012 or Server Core in the near future.)

In short?  If you work with SQL Server and have never been to SQL Saturday, you probably want to.  It’s free!  What’s not to love?

Comments Off on SQL Saturday #111

Filed under geekiness

This Saturday is SQL Saturday!

This Saturday, I’ll be joining all the SQL geeks (including someone who’s been my friend since 1996) to listen to talks on SQL at the GSU campus in Alpharetta.  I’d say “Be there or be square!” but it’s sold out, so, alas, if you’re not already registered you’re doomed to be one of the uncool kids.  😉

I have a shiny new job to go with the shiny SQL goodness, too.  Let’s see what kind of new shiny I learn that will help me with the new shiny!

Comments Off on This Saturday is SQL Saturday!

Filed under geekiness

Patch Tuesday

If you’re like me, you have Patch Tuesday on your calendar.  In fact, if you’re like me, you’re running something like this at home! with your linux desktop checking itself and your Windows desktop for available patches via Nagios!

No?  It’s just me?

(It’s not just me.  I’m sadly trumped by the man monitoring his cat’s water dish with Nagios.)

Comments Off on Patch Tuesday

Filed under geekiness

Cat Geekery

So, I have the Withings wifi scale.  It gives me pretty graphs and posts my weight as an accountability thing, to keep me from gaining it all back.

My cats are using my Withings scale.

Admittedly, I do tend to ask them, “What are you doing? Are you crazy?” but sometimes they even surprise me.

Comments Off on Cat Geekery

Filed under geekiness

Software Update

I moved to WordPress.

Mainly because of the ease of upgrades. Compared to Movable Type upgrades, well, damn. My commute is so ugly these days (hence the radio silence) that that makes a big difference to me.

So. How are you? Happy New Year!

Comments Off on Software Update

Filed under geekiness

I can has Twitter!

Hey, check it out! It’s me on Twitter. I’m sure you’re so excited you can hardly wash. I know I am!

Comments Off on I can has Twitter!

Filed under geekiness

Made. Of. Awesome!

Fantasy Magazine‘s new online submission system rocks so hard I’m afraid they’ll get a ticket for disturbing the peace. Seriously. And this is from someone who used to write database-driven websites for a living. Best online submissions ever! They even let you track where your submission is in the slush!
As you were.

Comments Off on Made. Of. Awesome!

Filed under geekiness, selling fiction