Monday, October 15, 2012

DD-wrt, openvpn and selectively routing for multiple tunnels


So, as an expat living abroad, I have times when I want to have my traffic appearing as though I am from the US. Most recently, Newegg rejected some purchases I made b/c I am coming from an IP abroad. Dd-wrt has the facility to use an openvpn client but I don’t really want to send all my traffic overseas just for the few instances I need to redirect my traffic.

As an American living in the UK, most things are pretty similar but one thing that grates on me is the ‘big brother’ government. Recently, the gov’t tried to push through a snooping bill that would track all internet use. Australia has a similar one in the works.

Now, I am pretty boring but, in principle, I completely disagree with this. So in order to add more encrypted static to the world, I am also planning to route all of my non time critical internet traffic out a local UK vpn.

In some things, like RDP, latency really matters, so I will route those out my local ISP.

Mostly though, this is a just b/c I wanted to see if I could do it. B)

Below are some proof of concept steps that I hope will help someone else get up and running. This should work w/ any number of VPNs (or other interfaces) and w/ any linux w/ the right routing in the kernel. (it was there in dd-wrt so I didn't go too much into it, I saw some mention on several of the pages I used that you need certain kernel options).

I got some help on the forums, and these three pages really brought it all together for me.

To start, I have a Netgear WNR3500v2/U/L. Based on the peacock thread, I put in DD-WRT v24-sp2 (08/12/10) mega - build 14929. This has the necessary routing options compiled into the linux kernel to do what I need.

The basic idea is this:
  1. Bring up two VPN tunnels, one to the UK and one to US
  2. Create routing tables to send traffic in that table out the specific gateway
  3. Us iptables to mark packets for specific tables/routing
I am going to do this through a start up script. Getting all of this set up required me to get the ssh daemon running and I recommend the same if you try to do this. You can do this in DD-WRT under Services –> Services –> Secure Shell. 

Once that is set you can ssh to your dd-wrt box. Login with root and your password you use to get into the web console.

For a vpn provider, I am using Private Internet Access. Anybody that gives you an OpenVPN client and allows multiple connections should be fine but you may need to tweak your openVPN.conf files.

The script (in several parts):

First, since we are not configuring the openvpn client through the GUI (I leave Services –> VPN –> OpenVPN client disabled), we need to create our own config files.

Begin by creating the directory to hold your configuration:
                mkdir /tmp/openvpncl

Create a file w/ the cert for your vpn provider:
echo "-----BEGIN CERTIFICATE-----
--snip--
-----END CERTIFICATE-----
" >> /tmp/openvpncl/ca.crt

Next, we want to create our configuration files. We do this by echoing a bunch of lines to create a config file. We do this twice, once for each VPN.
#Setup US Tunnel Config
echo client > /tmp/openvpncl/openvpn-US.conf
echo dev tun >> /tmp/openvpncl/openvpn-US.conf
echo proto udp >> /tmp/openvpncl/openvpn-US.conf
echo remote _YourVpnServer_ 1194 >> /tmp/openvpncl/openvpn-US.conf
echo resolv-retry infinite >> /tmp/openvpncl/openvpn-US.conf
echo nobind >> /tmp/openvpncl/openvpn-US.conf
echo persist-key >> /tmp/openvpncl/openvpn-US.conf
echo persist-tun >> /tmp/openvpncl/openvpn-US.conf
echo ca /tmp/openvpncl/ca.crt >> /tmp/openvpncl/openvpn-US.conf
echo tls-client >> /tmp/openvpncl/openvpn-US.conf
echo remote-cert-tls server >> /tmp/openvpncl/openvpn-US.conf
echo auth-user-pass /tmp/password.txt >> /tmp/openvpncl/openvpn-US.conf
echo comp-lzo >> /tmp/openvpncl/openvpn-US.conf
echo verb 1 >> /tmp/openvpncl/openvpn-US.conf
echo reneg-sec 0 >> /tmp/openvpncl/openvpn-US.conf


#Setup UK Tunnel Config
echo client > /tmp/openvpncl/openvpn-UK.conf
echo dev tun >> /tmp/openvpncl/openvpn-UK.conf
echo proto udp >> /tmp/openvpncl/openvpn-UK.conf
echo remote _YourOtherVpnServer_ 1194 >> /tmp/openvpncl/openvpn-UK.conf
echo resolv-retry infinite >> /tmp/openvpncl/openvpn-UK.conf
echo nobind >> /tmp/openvpncl/openvpn-UK.conf
echo persist-key >> /tmp/openvpncl/openvpn-UK.conf
echo persist-tun >> /tmp/openvpncl/openvpn-UK.conf
echo ca /tmp/openvpncl/ca.crt >> /tmp/openvpncl/openvpn-UK.conf
echo tls-client >> /tmp/openvpncl/openvpn-UK.conf
echo remote-cert-tls server >> /tmp/openvpncl/openvpn-UK.conf
echo auth-user-pass /tmp/password.txt >> /tmp/openvpncl/openvpn-UK.conf
echo comp-lzo >> /tmp/openvpncl/openvpn-UK.conf
echo verb 1 >> /tmp/openvpncl/openvpn-UK.conf
echo reneg-sec 0 >> /tmp/openvpncl/openvpn-UK.conf

Note that you will need to replace YourVpnServer and YourOtherVpnServer w/ the details appropriate to your service.  There may be other options to change as well, though, the above is fairly basic and should work w/ most services.

When we bring our tunnels up and down, we will need to tell iptables to MASQUERADE for this connection.  These will be run as scripts so we need to set the execute bit for them.  We create our up and down scripts here:
#Tun0 route up script
echo iptables -A POSTROUTING -t nat -o tun0 -j MASQUERADE > /tmp/openvpncl/route-up-UK.sh
chmod 700 /tmp/openvpncl/route-up-UK.sh
#Tun0 route down script
echo iptables -D POSTROUTING -t nat -o tun0 -j MASQUERADE > /tmp/openvpncl/route-down-UK.sh
chmod 700 /tmp/openvpncl/route-down-UK.sh

#Tun1 route up script
echo iptables -A POSTROUTING -t nat -o tun1 -j MASQUERADE > /tmp/openvpncl/route-up-US.sh
chmod 700 /tmp/openvpncl/route-up-US.sh
#Tun1 route down script
echo iptables -D POSTROUTING -t nat -o tun1 -j MASQUERADE > /tmp/openvpncl/route-down-US.sh
chmod 700 /tmp/openvpncl/route-down-US.sh

of course, we need a username and password:
#General Config
echo USER > /tmp/password.txt
echo PASSWORD >> /tmp/password.txt

Now we bring up the tunnels.  Note the –route-nopull.  This ignores routing info from the openvpn server.  We want to specify our own routing.  Without that, openvpn seems to set our default traffic to go out the last tunnel brought up.  If you are having any trouble on this step, run the line without –daemon.
#Setup tunnels. 
/usr/bin/killall openvpn
/usr/sbin/openvpn --config /tmp/openvpncl/openvpn-UK.conf --route-nopull --route-up /tmp/openvpncl/route-up-UK.sh --down-pre /tmp/openvpncl/route-down-UK.sh --daemon
/usr/sbin/openvpn --config /tmp/openvpncl/openvpn-US.conf --route-nopull --route-up /tmp/openvpncl/route-up-US.sh --down-pre /tmp/openvpncl/route-down-US.sh --daemon

In order to setup routes, we need to get our default gateways for each interface.  First, though, we hang out for a couple seconds to allow the tunnels to establish:
#The tunnels can take a couple seconds to establish.  Hold for 5 seconds to allow for this
sleep 5

# get gateway addresses
IspGateway=$(ip route list table main | awk '/default/ { print $3}')
Tun0Gateway=$(ip route list table main | awk '/tun0/ { print $1}')
Tun1Gateway=$(ip route list table main | awk '/tun1/ { print $1}')


Now, we have our two tunnels established as tun0 (UK) and tun1 (US).  We also have our default (ppp0) route to our ISP.  Now we start getting fancy.  B) 

We are going to use iptables to MARK our packets.  Here we tell iproute route any packets marked with a 2 based on the routing table, 2.
# Create fwmark to table bindings
ip rule add fwmark 1 table main # ISP
ip rule add fwmark 2 table 2 # Tunnel 0 UK
ip rule add fwmark 3 table 3 # Tunnel 1 US

You can use ‘ip route show” to see current ip rules.

Now we setup the routing for each table.  For my setup, I am just giving the default route.
# Create table to tunnel bindings
ip route add default via $Tun0Gateway dev tun0 table 2 #Send out UK Tunnel
ip route add default via $Tun1Gateway dev tun1 table 3 #Send out US Tunnel

You can see the ip routes per table with:
ip route show table 2

Now we are ready to create specific rules.  You will probably have a lot more rules than the below. 
#UK tunnel rules
iptables -A PREROUTING -t mangle -s 192.168.1.0/24 -j MARK --set-mark 2

#US Tunnel rules
ip rule add to 174.129.0.77/32 table 3

#ISP rules
iptables -A PREROUTING -t mangle -p tcp --destination-port 3389 -j MARK --set-mark 1

In order, we first say that anything coming from this specific IP range (192.168.1.0/24) should route out the UK tunnel.
Next we use a different way of layer 3 routing w/ ip rule.  I am actually just going to stick to the iptables MARKing for simplicity but I wanted to show this was possible as well
Finally, we mark a packet based on destination port.  So we are saying any RDP traffic should go out the ISP interface.

That is all there is to it.  Iptables is pretty full featured, you can get pretty crazy w/ it.  Good luck and if you do something fun, please tell me about it.

Notes:
  •  If one of your tunnels goes down, your routing for that tunnel goes away.  Your traffic will start to flow out your default interface.  This may or may not be ideal depending on the sensitivity of what you are passing.  You can manage that with the route down commands if you are concerned.
  • the order your rules apply in matters.  see ip rule output to see the order.  If you want to 'override' the UK routing, in my example, you will need your rule to to have a lower ip rule id than the UK line.

The full script is below:
mkdir /tmp/openvpncl
echo "-----BEGIN CERTIFICATE-----
--snip--
-----END CERTIFICATE-----
" >> /tmp/openvpncl/ca.crt
#Setup US Tunnel Config
echo client > /tmp/openvpncl/openvpn-US.conf
echo dev tun >> /tmp/openvpncl/openvpn-US.conf
echo proto udp >> /tmp/openvpncl/openvpn-US.conf
echo remote _YourVpnServer_ 1194 >> /tmp/openvpncl/openvpn-US.conf
echo resolv-retry infinite >> /tmp/openvpncl/openvpn-US.conf
echo nobind >> /tmp/openvpncl/openvpn-US.conf
echo persist-key >> /tmp/openvpncl/openvpn-US.conf
echo persist-tun >> /tmp/openvpncl/openvpn-US.conf
echo ca /tmp/openvpncl/ca.crt >> /tmp/openvpncl/openvpn-US.conf
echo tls-client >> /tmp/openvpncl/openvpn-US.conf
echo remote-cert-tls server >> /tmp/openvpncl/openvpn-US.conf
echo auth-user-pass /tmp/password.txt >> /tmp/openvpncl/openvpn-US.conf
echo comp-lzo >> /tmp/openvpncl/openvpn-US.conf
echo verb 1 >> /tmp/openvpncl/openvpn-US.conf
echo reneg-sec 0 >> /tmp/openvpncl/openvpn-US.conf


#Setup UK Tunnel Config
echo client > /tmp/openvpncl/openvpn-UK.conf
echo dev tun >> /tmp/openvpncl/openvpn-UK.conf
echo proto udp >> /tmp/openvpncl/openvpn-UK.conf
echo remote _YourOtherVpnServer_ 1194 >> /tmp/openvpncl/openvpn-UK.conf
echo resolv-retry infinite >> /tmp/openvpncl/openvpn-UK.conf
echo nobind >> /tmp/openvpncl/openvpn-UK.conf
echo persist-key >> /tmp/openvpncl/openvpn-UK.conf
echo persist-tun >> /tmp/openvpncl/openvpn-UK.conf
echo ca /tmp/openvpncl/ca.crt >> /tmp/openvpncl/openvpn-UK.conf
echo tls-client >> /tmp/openvpncl/openvpn-UK.conf
echo remote-cert-tls server >> /tmp/openvpncl/openvpn-UK.conf
echo auth-user-pass /tmp/password.txt >> /tmp/openvpncl/openvpn-UK.conf
echo comp-lzo >> /tmp/openvpncl/openvpn-UK.conf
echo verb 1 >> /tmp/openvpncl/openvpn-UK.conf
echo reneg-sec 0 >> /tmp/openvpncl/openvpn-UK.conf

#Tun0 route up script
echo iptables -A POSTROUTING -t nat -o tun0 -j MASQUERADE > /tmp/openvpncl/route-up-UK.sh
chmod 700 /tmp/openvpncl/route-up-UK.sh
#Tun0 route down script
echo iptables -D POSTROUTING -t nat -o tun0 -j MASQUERADE > /tmp/openvpncl/route-down-UK.sh
chmod 700 /tmp/openvpncl/route-down-UK.sh

#Tun1 route up script
echo iptables -A POSTROUTING -t nat -o tun1 -j MASQUERADE > /tmp/openvpncl/route-up-US.sh
chmod 700 /tmp/openvpncl/route-up-US.sh
#Tun1 route down script
echo iptables -D POSTROUTING -t nat -o tun1 -j MASQUERADE > /tmp/openvpncl/route-down-US.sh
chmod 700 /tmp/openvpncl/route-down-US.sh


#General Config
echo USER > /tmp/password.txt
echo PASSWORD >> /tmp/password.txt

#Setup tunnels. 
/usr/bin/killall openvpn
/usr/sbin/openvpn --config /tmp/openvpncl/openvpn-UK.conf --route-nopull --route-up /tmp/openvpncl/route-up-UK.sh --down-pre /tmp/openvpncl/route-down-UK.sh --daemon
/usr/sbin/openvpn --config /tmp/openvpncl/openvpn-US.conf --route-nopull --route-up /tmp/openvpncl/route-up-US.sh --down-pre /tmp/openvpncl/route-down-US.sh --daemon

#The tunnels can take a couple seconds to establish.  Hold for 5 seconds to allow for this
sleep 5

# get gateway addresses
IspGateway=$(ip route list table main | awk '/default/ { print $3}')
Tun0Gateway=$(ip route list table main | awk '/tun0/ { print $1}')
Tun1Gateway=$(ip route list table main | awk '/tun1/ { print $1}')



# Create fwmark to table bindings
ip rule add fwmark 1 table main # ISP
ip rule add fwmark 2 table 2 # Tunnel 0 UK
ip rule add fwmark 3 table 3 # Tunnel 1 US

# Create table to tunnel bindings
ip route add default via $Tun0Gateway dev tun0 table 2 #Send out UK Tunnel
ip route add default via $Tun1Gateway dev tun1 table 3 #Send out US Tunnel


#UK tunnel rules
iptables -A PREROUTING -t mangle -s 192.168.1.0/24 -j MARK --set-mark 2

#US Tunnel rules
ip rule add to 174.129.0.77/32 table 3

#ISP rules
iptables -A PREROUTING -t mangle -p tcp --destination-port 3389 -j MARK --set-mark 1

Friday, June 15, 2012

Powershell to run a DBCC checkdb loop for all databases


I needed a loop that would run a dbcc check db on all online databases (other than tempdb).  I did this is sql first but I seemed to be getting strange errors.  After a bit of research, I saw that I wasn’t the only one and decided to drop out of sql and run the commands in as separated a manner as possible.  The script below gets all online databases and runs dbcc checkdb on all of them.  We use WITH NO_INFOMSGS so if we have any output, we consider it a problem and send it to an operator.  We use sqlcmd and a command processor so hopefully a single dbcc error will not corrupt the rest of the run...

We take 3 arguments
@SqlInstance – Name of sql instance to check
@MailHost – we need an SMTP server to send the message through
@MailTo – address of the operator that will get the error/warning messages

This was developed against SQL Server 2008 but I see no reason it wouldn't work on others...
Good luck

Update: 20120620 - added a kill all connections based on this post.  

param (
      $SqlInstance,
      $MailHost,
      $MailTo
)
function main {
      $verbosity = 2
      $ScriptName = $myInvocation.MyCommand.Name
      [void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
      $ConnString = "Server=$SqlInstance;Integrated Security=SSPI;Application Name=$ScriptName"
      $MasterConn = new-object ('System.Data.SqlClient.SqlConnection') $ConnString
      $MasterCmd = new-object System.Data.SqlClient.SqlCommand
      $MasterCmd.Connection = $MasterConn
      $SqlDBCC = "SELECT name AS name FROM master..sysdatabases WHERE Name <> 'tempdb'AND DATABASEPROPERTY(name, 'IsOffline') = 0"
      $MasterCmd.CommandText = $SqlDBCC
      $MasterConn.Open()
      $Rset = $MasterCmd.ExecuteReader()
      If ($Rset.HasRows -eq $true) {
            While ($Rset.Read()) {
                  $DatabaseName = $Rset["Name"]
                  out-log "Working with $DatabaseName.  Killing Connections at $(Date)"
                  $SqlCmdOutput = sqlcmd -E -S $SqlInstance -d $DatabaseName -Q "exec master.dbo.usp_KillConnections $DatabaseName"
                  out-log "$SqlCmdOutput
                  Starting Check"
                  $SqlCmdOutput = sqlcmd -E -S $SqlInstance -d $DatabaseName -Q "dbcc checkdb WITH NO_INFOMSGS"
                  if ($SqlCmdOutput -eq $Null) {
                        out-log "DBCC completed for $DatabaseName on $SqlInstance with no errors"
                  } else {
                        out-log "WARNING: DBCC Completed with errors:
                        $SqlCmdOutput" -ForeGroundColor Red
                        $MailSubject = "$DatabaseName @ $SqlInstance DBCC Failure"
                        $MailFrom = "DBCCRunner.$(hostname)@host.com"
                        $MailBody = "DBCC reported errors. 
                        Instance: $SqlInstance
                        Database: $DatabaseName
                        $SqlCmdOutput
                        "
                        (new-object Net.Mail.SmtpClient($MailHost)).send($MailFrom,$MailTo,$MailSubject, $MailBody)
                 
                  }
            }
            $Rset.Close()
      }
      $MasterConn.Close()
}


function Out-Log {
      #v.6 - ej 20120325 - Indented verbose output
      #v.5 - ej 20120210 - set logging to $env:HOMEDRIVE
      #v.4 - ej 20120207 - fixed tab issue that broke copy and paste to console.
      #v.3 - ej 20111109 - added newline and removed the timestamps from display
      #version .2 - ej, 20090114
      #this script will allow for logging and screen output based on a requested verbosity level.
      #taking a cue from syslog, we are defining 0 as most critical errors.
      #by default in this script, verbosity is set as 1.  so we only print items specifically marked as 0 criticality to screen
      #default items come in as a log level of 1, ie they are not printed.  both of these can be overridden
      # all items are logged to a file based on the scriptname and run date at c:\toolkit\scripts\logs
      # it is expected that the default log level will be used for warnings and some informational messages
      # debug messages will be given log levels of 2 and higher as detailed below.
      # critical messages should be marked w/ a log level of 0
      #
      #
      #usage:
      # in your script you can include the function with:
      # . c:\toolkit\scripts\out-log.ps1
      #
      # you can then write all informational messages to log with
      # out-log <StringToLog> [LogLevel] [ForeGroundColor]
      #
      #simplest case (when in doubt, use this)
      # out-log "message to log"
      #
      #if you want to also ALWAYS print to screen you can set the message to level 0 with (use sparingly)
      # out-log "message to log" 0
      #
      #if you want make it print in RED on the screen (dependent on whether it will print to screen)
      # out-log "message to log" 0 RED
      #to change the level of logging that you want to see on screen, you can set a global variable 'verbosity'
      #this script will print log levels that are equal to or less than the variable '$verbosity'.
      #
      #a suggested usage is to accept a command line argument with verbosity level.
      #
      #best way to accept verbosity via cmd line is to add a param to the beginning of your script. it needs to be first line.
      # if the param below is added to your script, you can add "-v 3" to your command line to set the verbosity to 3,
      # this script prints all log items with a lower loglevel than the chosen verbosity.
      # if not set, we assume verbosity 0.
      # we then expect that only critical errors would be sent to log level 0.  default log level is 1.
      #param(
      #[string] $verbosity = 0
      #)
      # you can also set deeper log levels.  for example, if you want log level 1 to be warnings and information about script progress
      #    but you also want the option to enable/disable debug messages, you can log all your debug messages to a higher log
      #
      # out-log "debug string to log" 2
      #
      #if you then want to run your script such that you see debugging messages, you can run
      #./script.ps1 -verbosity 2
      #
      #note that you can use shortened command line arguments.  you just need enough to be unique so unless you
      # define another param that starts w/ 'v', you can use
      #./script.ps1 -v 2
      ##### BEGIN SCRIPT ######
      param(
            [string] $incomingString,
            [int] $logLevel = 1 ,
            $ForegroundColor = $host.ui.RawUI.ForegroundColor,
            $BackgroundColor = $host.ui.RawUI.BackgroundColor,
            [switch]$NoNewLine
      )
      #we are defining the log directory on all machines to be, use trailing "\"
      $logDirectory = "$($env:HOMEDRIVE)\logs\"

      #set your date
      $logDate = Get-Date -Format yyyyMMdd-HHmmss


      #have we defined verbosity?
      #if verbosity is not defined, we set it here as 0
      if ($verbosity -eq $null) {$verbosity = 0}

      #have we defined the logfile?

      if (-not (test-path variable:outlogfile)) {
            #if no, create it now based on todays datetime and progname

            #check for the log path, create if not found
            if (!(Test-Path -path $LogDirectory)) {
                  $tmp = New-Item $LogDirectory -type directory
                  Write-Host " ---> Created Log Directory at " $LogDirectory
            }

            #define name <ScriptName>-<Date>.log
            if($myInvocation.ScriptName -ne "") {
                  $scriptName = [IO.Path]::GetFileNameWithoutExtension($myInvocation.ScriptName)
            } else {
                  $scriptName = "NONAME"
            }

            $logName = $logDirectory + $scriptName + "-" + $logDate + ".log"
            Write-Host "Logging to: $logname" -ForegroundColor DarkBlue -BackgroundColor DarkYellow
            Set-Variable -Name OutLogFile -Value $logName -Scope script
      }

      #now we use the Logfile

      #if the log level is lower or equal to than the verbosity, we also spit to screen
      if ($logLevel -le $Verbosity) {
            Write-Host ("   " * $logLevel + $incomingString) -ForegroundColor $ForegroundColor -BackgroundColor $BackgroundColor -NoNewline:$NoNewLine
      }

      #date time stamp for logging
      $stringToLog = $logDate + ": " + "   " * $logLevel + $incomingString

      #finally, stick this in the log
      Out-File -filepath $outLogfile -inputObject $stringToLog -append

}

main

analytics