Saturday, January 26, 2013

Mounting A Local Linux Folder

OK so I've probably got myself into a unique situation here. I thought I would be smart and try setting up transmission to download torrents directly to my wdtvlivehub. It was a good idea at the time but it's really not working out too well. Firstly, I mounted the wdtvlivehub to a mount point on my Raspberry Pi. Works perfect.

The problem comes us with the way bit torrent works. It's doing massive amounts of really small network IOs writing and reading blocks from the disk. In my case this happens across a 100 Mbps network. I don't think the Pi is handling it that well. The other side affect appears to be on the wdtvlivehub side of things. It looks like it's detecting the file changes and is always trying to recompile the media library. Side affect - the wdtvlivehub is not usable (but I'm sure fixable :) )

I've already added a powered USB disk to the Pi and the performance is much, much better. The next step is to relocate the existing torrents without upsetting transmission. To do this I'm going to setup a folder on the new external disk, move across my partially downloaded torrents, then mount this folder back to the original mount point.

To start with I'll create the new folders to match my existing structure
mkdir /media/usbdrive2/wdtvlivehubmkdir /media/usbdrive2/wdtvlivehub/temp

Next I'll copy the partially completed torrents across
mv /media/wdtvlivehub/temp/*.part /media/usbdrive2/wdtvlivehub/temp
Next I'll add the following lines to my /etc/fstab:
/media/usbdrive2/wdtvlivehub /media/wdtvlivehub auto bind,gid=46,defaults 0 0
To finish up I re-mount all the mount points specified by fstab
sudo mount -a 

Wednesday, January 23, 2013

SQL Server Encryption Notes

ENCRYPTION AT REST (ON DISK) - Transparent Data Encryption or TDE

  • Data and Log files encrypted
    • But memory paged out of the operating system wont be
  • No changes for developers (ie. code remains unchanged)
  • Provides security if SQL Access Controls are bypassed. Eg. Shut down SQL Server and take database files off the server. Stolen backup files
  • Can encrypt single fields or entire database (entire database probably safest)
  • Performance hit estimated at 3-5% CPU - needs to be tested but should be acceptable
  • The database backup are automatically encrypted when database encryption is turned on 

  • Data not encrypted in memory
  • No added protection for access via SQL (ie. TSQL or stored proc. Access Control Lists lock down this access)
  • Need a maintenance strategy for passwords, keys, certificates
    • Lose one of these and the database may not be recoverable
  • More complex database refresh procedure
  • TDE does not increase the size of the data (but negates compression - on average we will need 3x the current disk space, compression ratio typically around 35% of original size)
  • Automatically encrypts the tempdb database
    • I.e. Performance hit for all databases as all tempDB operations are now encrypted
  • Having enough memory is important for performance as data in memory is not encrypted. Retrieving data from disk will have the encryption overhead
  • Other features such as full text search and filestream are not encrypted - we dont use these at the moment.
Other Details 
  • Authenticators option - can force the same user that encrypts the data to be the only person that can decrypt the data
  • Encryption Algorithms - DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
  • Asymmetric encryption offers stronger encryption than symmetric - but also very slow  
    • Symmetric is recommended for data
    • Asymmetric is used to protect symmetric keys
  • Can use existing UAT infrastructure for testing as encryption is turned on per database

  • SSL 128bit
  • Server setting - can force encryption or make it mandatory
    • Recommend forcing encryption
  • Change in connection string
    • Eg. Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Network=DBNETLIB.DLL;Encrypt=YES
    • Eg. Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True

Also look at:

Securely Wiping An Old Hard Disk

Throwing your hard disks away is a major, major security risk. There's a lot of sensitive data stored on your hard drive that builds up over the years. Even deleting the contents or formatting the old drive isn't enough - people can (and do) recover 'deleted' information all the time. Just search for "recover windows files" in google to find the millions of matches on how to do this (yep, there's legitimate reasons for doing this - like when you've deleted a file and need to get it back!)

The best way to make sure your private information stays safe is to wipe the hard disk before throwing it in the trash. There's a number of free tools out there to do this. If you're running Windows then a great utility is Disk Wipe.

It's a free download and very, very easy to use. After installing you just run the program and select the drive you want to wipe. Be very, very careful and make sure you select the right drive, there's no way to get this data back once it's wiped. Also make sure you've backed up everything you need before doing this - there ain't no turning back!

The next option to select is the level of wipe you want to do. The option at the top is both the fastest wipe and least secure, the option at the bottom is the slowest wipe and most secure. Typically I select one of the options in the middle - it's a good tradeoff between speed and security. If you're paranoid, select the bottom option and come back in a day - it takes a while!

Saturday, January 19, 2013

Problems Mounting Drives Formatted With ExFAT In Ubuntu

Plugging in one of my external hard drives into a fresh Ubuntu 12.10 install today and it's failed with the following error message:

mount: unknown filesystem type 'exfat'
Luckily following the instructions on this blog resolved the problem quickly. In short it takes 3 short commands to add exFAT support to your Ubuntu desktop:

sudo apt-add-repository ppa:relan/exfat 
sudo apt-get update 
sudo apt-get install fuse-exfat

Thursday, January 17, 2013

Automated vSphere Daily Health Checks Using Python

The on-call staff member in our team is tasked with performing morning health checks of key infrastructure. Basic stuff like checking that we have enough free space in the data stores and that we don’t have any forgotten snapshots running in the background.

Doing these checks manually is a time consuming process so any kind of automation is a good thing™. Rather than logging onto each VMWare Server and manually inspecting the health of the VMWare host I’d rather have something automated. In this case I’ve opted for a script that does these checks for me. As a result it sends me an email in the morning using a traffic light system. Items that are “bad” or “need attention” are highlighted red. Items that are “good” are highlighted in green.

The first version of this script performs basic health checks of the VMWare host. Specifically it checks which VMWare guests are running and which aren’t. It lists all of the snapshots on the system and which virtual machine they belong to. It also checks the data stores and highlights any data stores with less than 20% free space.

In future versions I want the script to go through the logs and highlight any problems found on the VMWare host.

The script is written in Python. Personally I run it from our Linux based monitoring server. I haven’t tested it on Windows however it should work just fine (in theory!). It uses a module called PySphere which needs to be installed on your system. This can be done by running the following command:

sudo easy_install PySphere

Next upload the script to a location where you place your scripts. The script isn’t path dependant so the location does not matter.

Before running the script you will need to update it to match your environment. There’s a parameter section at the top of the script. You will need to update it such that it points to your vSphere server using the correct login credentials. You will also need to edit the SMTP information to use your email server with the relevant to and from email addresses.

 # Connect to ESX Server and perform checks  
 __author__ = "William Brown"  
 __copyright__ = "Copyright 2013"  
 __credits__ = ["William Brown"]  
 __version__ = "1.0.0"  
 __maintainer__ = "William Brown"  
 __email__ = ""  
 __status__ = "Production"  
 from pysphere import *  
 import smtplib  
 from email.mime.text import MIMEText  
 from email.mime.multipart import MIMEMultipart  
 import datetime  
 # Change these parameters to match your environment  
 hostname = "hostname"  
 hostip  = "ip address"  
 user   = "username"  
 password = "password"  
 emailfrom = "fromaddress"  
 emailto = "emailtoaddress"  
 smtpsvr = "serveraddress"  
 vmserver = VIServer()  
 vmserver.connect(hostip, user, password)  
 def print_header():  
      html = "<html><head></head><body>"  
      html += "<h1>VMWare ESX Status Report - " + hostname + "</h1>"  
      html += "<table border=1><tr><td><b>Server Type:</b></td><td>" + vmserver.get_server_type() + "</td></tr>"   
      html += "<tr><td><b>VMWare Version:</b></td><td>" + vmserver.get_api_version() + "</td></tr>"  
      html += "<tr><td><b>IP Address:</b></td><td>" + hostip + "</td></tr>"  
      html += "<tr><td>Runtime</td><td>" + str( + "</td></tr>"  
      html += "</table>"   
      return html  
 def print_vm_status():  
      html = "<h1>VMWare Guest Status Report</h1>"  
      html += "<table border=1><tr><th>VM Guest Name</th><th>Guest Status</th></tr>"  
      vmlist = vmserver.get_registered_vms()  
      for vm in vmlist:  
           vm_svr = vmserver.get_vm_by_path(vm)  
           if vm_svr.get_status() == "POWERED OFF":  
                html += "<tr bgcolor=#F78181><td>" + vm + "</td><td>" + vm_svr.get_status() + "</td></tr>"  
                html += "<tr bgcolor=#81F781><td>" + vm + "</td><td>" + vm_svr.get_status() + "</td></tr>"  
      html += "</table>"  
      return html  
 def print_snapshot_status():  
      html = "<h1>Snapshot Status</h1>"  
     html += "<p>All snapshots are listed below. If there's no entries in this table, there's no snapshots!</p>"  
      html += "<table border=1><tr><th>Virtual Machine</th><th>Snapshot Name</th><th>Date Created</th><th>State</th></tr>"  
      vmlist = vmserver.get_registered_vms()  
     for vm in vmlist:  
         vm_svr = vmserver.get_vm_by_path(vm)  
         vm_snaps = vm_svr.get_snapshots()  
         for vm_snap in vm_snaps:  
                html += "<tr bgcolor=#F78181><td>" + vm + "</td>"  
             html += "<td>" + vm_snap.get_name() + "</td>"  
             html += "<td>" + vm_snap.get_create_time() + "</td>"  
             html += "<td>" + vm_snap.get_state() + "</td>"  
      html += "</table>"  
      return html  
 def print_datastore_status():  
      html = "<h1>Datastore Status</h1>"  
      html += "<p>Note: Datastores with less than 20% free space are highlighted in red</p>"  
      html += "<table border=1><tr><th>Datastore</th><th>Type</th><th>Capacity (GB)</th><th>Freespace (GB)</th><th>Uncommitted Space (GB)</th></tr>"  
      for ds_mor, name in vmserver.get_datastores().items():  
           props = VIProperty(vmserver, ds_mor)  
           percentfree = float(props.summary.freeSpace)/float(props.summary.capacity)  
           if percentfree < 0.2:  
           if hasattr(props.summary, "uncommitted"):  
                 html += "<tr bgcolor=" + bgcolor + "><td>"+str(name)+"</td><td>"+str(props.summary.type)+"</td><td>"+str(props.summary.capacity/1024/1024/1024)+"</td><td>"+str(props.summary.freeSpace/1024/1024/1024)+"</td><td>"+str(props.summary.uncommitted/1024/1024/1024)+"</td></tr>"  
                 html+="<tr bgcolor=" + bgcolor + "><td>"+str(name)+"</td><td>"+str(props.summary.type)+"</td><td>"+str(props.summary.capacity/1024/1024/1024)+"</td><td>"+str(props.summary.freeSpace/1024/1024/1024)+"</td><td>N/A</td></tr>"  
      html += "</table>"  
      return html  
 #print print_vm_status()  
 body = print_header()  
 body += print_vm_status()  
 body += print_snapshot_status()  
 body += print_datastore_status()  
 body += "</body></HTML>"  
 msg = MIMEMultipart('alternative')  
 #part1 = MIMEText('text', 'plain')  
 part2 = MIMEText(body,'html')  
 msg['Subject'] = "VMWare Health Check - " + hostname + " - " + str(  
 msg['From'] = emailfrom  
 msg['To'] = emailto  
 s = smtplib.SMTP(smtpsvr)  
 s.sendmail( emailfrom, emailto, msg.as_string())  

Finally you will need to setup a cron job (or windows scheduled task) to run this script as required. Personally I run this job first thing in the morning so that it’s in my inbox when I’m ready to do my morning checks.

Friday, January 11, 2013

Enable Remote Connections to MySQL

By default MySQL is configured in such a way that it won't accept remote connections. I'm guessing the is by design and a security feature. This makes sense because a lot of programs use a local MySQL database which should be secured from remote connections. Fortunately if you need to open up your MySQL database server it's an easy thing to do!

Thursday, January 10, 2013

Monitoring Raspberry Pi Performance Using Cacti

The Raspberry Pi is an incredible piece of hardware however, given it's limited resources it's important to squeeze every last drop of performance. This guide looks at using Cacti to monitor the performance of the Pi. By graphing the load on the system we can see how changes we make affect the performance of the Pi.

Wednesday, January 9, 2013

Using NAGIOS to Check the Physical Memory Available on a Windows Host

By default the CheckNT command checks the virtual memory on a Windows server. So for example, if your server had 4GB of physical memory and a 4GB page file NAGIOS and CheckNT would see 8GB of physical memory. Getting warnings and critical alerts on this memory space is quite often not very helpful. What we really want to know is do we have enough physical memory available on the server so that the server performs as well as it should.

This is where the NRPE plugins are much better as you can get much more granular when monitoring the memory on a Windows host.

To start with we need to create a new command definition. Add this to your commands.cfg (or equivalent):

 # CheckWindowsPhysical Mem command definition  
 define command {  
         command_name             CheckWindowsPhysicalMem  
         command_line             $USER1$/check_nrpe -H $HOSTADDRESS$ -p 5666 -c CheckMEM -a MaxWarn=$ARG1$% MaxCrit=$ARG2$% ShowAll type=physical  

In the above command definition we're using the check_nrpe executable to perform a memory check of the physical memory. The type can be changed to grab just the page file or check the entire virtual memory address space.

Next we need add the physical memory checks by adding a service definition to either your host or service configs (again, depends on how you've structured your NAGIOS configuration).

 # Service definition  
 # Add the service to the service definition  
 define service {  
         service_description          Physical Memory  
         check_command             CheckWindowsPhysicalMem!80!90  
         host_name               << hostname >>  
         event_handler_enabled         0  
         active_checks_enabled         1  
         passive_checks_enabled        0  
         notifications_enabled         1  
         check_freshness            0  
         freshness_threshold          86400  
         use                  << service template >>  

You will need to update the above snippet with the host name you are monitoring and the service template you are using. The !80!90 is the standard warning at 80% usage, critical at 90% usage. These can be varied to suit your host and environment.

Find all Primary Keys With Non-Clustered Indexes

This T-SQL script is used to find all Primary Keys that have a non-clustered index associated with them. Ideally a primary key will use a clustered index which essentially means that the index is the table. This makes joins and queries that use the primary key much more efficient.

When using a non-clustered index as the primary key the table is stored as a heap or unordered table. New rows are simply added to the end of the table which can be the fastest way to load data. The problem arises when we need to search the data in a heap and after using the non-clustered primary key index we probably have to do a bookmark lookup in the table to find the rest of the row data. This is a two step operation compared to the one step index seek in a clustered primary key.

Other side affects of the heap structure include forwarded rows. When row updates happen and the data is too big to fit on the page any more the page is split. That is a second page is created and half the row data is moved to the second page. A forwarding record is added to the first page referencing the second page. This is much like file fragmentation and reading data from the table will now become much more random in nature (remembering random IO is around 50 times slower than sequential IO).

 -- Returns all non-clustered primary keys  
 select object_name(object_id) as [Table Name], as [Index Name]  
 from sys.indexes i  
 where is_primary_key = 1  
 and type_desc <> 'CLUSTERED'  
 order by object_name(object_id) asc  

Monday, January 7, 2013

Building a Headless Torrent Client Using A Raspberry Pi

The Raspberry Pi makes a great low powered torrent client machine which you can leave on 24/7. The main reason I’ve done this is to save power by running the torrent client on the extremely low powered Raspberry Pi. It also frees up my computer and keeps things a bit neater.
Before getting started this guide assumes that you’ve got a running Raspberry Pi. I’d recommend setting a static IP address which you can do using this guide here. I’d also recommend enabling SSH so that you can remote administer your Pi. The easiest way to do this is to run the following command:
sudo raspi-config
This brings you back into the first menu you encountered when you setup the Raspberry Pi. There’s a menu item to Enable / Disable SSH. Go in there and enable it. While you’re at it it’s also a good idea to disable the boot into desktop option using the menus as well (unless you are using the GUI, in that case keep it!)
You can also use putty to remotely access the command line on your Pi. Using Putty is outside of the scope of this tutorial but essentially you plug in the IP address of your Pi and connect to it. If you don’t know your IP address you can use the following command:
Other than a working Pi the only other thing you need is a USB drive. I’d recommend getting something that’s a decent size, at least 16GB as the size of the files you’re working with can add up fast. I’ve gone with a thumb drive as I want the unit self contained, low power draw and want to minimise the number of external power packs I’m using.
First we’re going to plug the USB thumb drive into the Raspberry Pi. There’s a good chance you already have a partition on the USB drive. Either way we’re going to remove all the partitions on the drive and start fresh. To partition the disk (typically /dev/sda) run the following command:
sudo fdisk /dev/sda

Press ‘p’ to list all the partitions on the disk. If there’s any existing partitions, use ‘d’ to delete them. Once all the partitions have been removed press ‘n’ to create a new partition. Follow the default prompts to create a primary partition that uses the entire disk. When you’re done press ‘w’ to write the partition table to the disk and exit fdisk.
The new partition will be /dev/sda1, that is the first partition on the first attached disk. The next step is to format the disk with the ext4 file system.
sudo mkfs -t ext4 /dev/sda1
Now that the partition has been formatted we need somewhere to mount it to. Create a new mount point under the media folder:
sudo mkdir /media/usbdrive

We want to mount the USB drive automatically on boot. To do this we need to edit the /etc/fstab file.
sudo echo '/dev/sda1 /media/usbdrive ext4 defaults 0 1' >> /etc/fstab
Now we can mount all the entries in the /etc/fstab file using:
sudo mount -a
With the USB file system mounted we can now setup the directory structure that transmission will be using to upload / download files.
sudo mkdir /media/usbdrive/torrents
sudo mkdir /media/usbdrive/torrents/torrents
sudo mkdir /media/usbdrive/torrents/incomplete
sudo mkdir /media/usbdrive/torrents/complete
sudo chmod 777 -Rf /media/usbdrive/torrents/
Next we can install transmission on the Pi.
sudo apt-get install transmission-daemon
Time to take a break while the install takes place. After the packages have been downloaded it takes quite some time to unpack and install the packages.

Once transmission has been installed it’s time to configure the service. We need to stop the service first though as it locks the configuration file and will overwrite any changes you make to it.
sudo service transmission-daemon stop
Next we edit the transmission-daemon configuration file:
sudo nano /etc/transmission-daemon/settings.json
Below I’ve put the contents of my settings.json file. There’s many configuration items you can play with however the key lines to update are:
  • ‘blocklist-enabled’ - enables the use of blocklists. These filter out IP ranges from connecting to the server.
  • ‘blocklist-url’ – The URL of the rules file that contains the blocked IP addresses. These can be downloaded from I use the level 3 blocklist which is the ‘paranoid’ setting and blocks a lot of IP ranges. You’ll be more secure but your download speeds will suffer.
  • ‘download-dir’ – set this to your completed downloads folder (/media/usbdrive/torrents/complete)
  • ‘incomplete-dir’ – set this to /media/usbdrive/torrents/incomplete. Partial downloads will be stored here until they are completed. Then they will be moved to the completed folder.
  • ‘incomplete-dir-enabled’ – set to true. This enables the folder above.
  • ‘rpc-whitelist’ – these are the IP ranges that can access the transmission web interface
  • ‘rpc-whitelist-enabled’ – set to true. This enforces the whitelist.
  • ‘watch-dir’ – torrent files placed into this folder will be added to the download queue.
  • ‘watch-dir-enabled’ – set to true. Enables the watch-dir.
    "alt-speed-down": 50,
    "alt-speed-enabled": false,
    "alt-speed-time-begin": 120,
    "alt-speed-time-day": 127,
    "alt-speed-time-enabled": true,
    "alt-speed-time-end": 360,
    "alt-speed-up": 350,
    "bind-address-ipv4": "",
    "bind-address-ipv6": "::",
    "blocklist-enabled": true,
    "blocklist-url": "",
    "cache-size-mb": 4,
    "dht-enabled": true,
    "download-dir": "/media/usbdrive2/torrents/complete",
    "download-limit": 200,
    "download-limit-enabled": 1,
    "download-queue-enabled": true,
    "download-queue-size": 4,
    "encryption": 0,
    "idle-seeding-limit": 30,
    "idle-seeding-limit-enabled": false,
    "incomplete-dir": "/media/usbdrive/torrents/incomplete",
    "incomplete-dir-enabled": true,
    "lpd-enabled": false,
    "max-peers-global": 200,
    "message-level": 2,
    "peer-congestion-algorithm": "",
    "peer-limit-global": 200,
    "peer-limit-per-torrent": 60,
    "peer-port": 51413,
    "peer-port-random-high": 65535,
    "peer-port-random-low": 49152,
    "peer-port-random-on-start": false,
    "peer-socket-tos": "default",
    "pex-enabled": true,
    "port-forwarding-enabled": true,
    "preallocation": 1,
    "prefetch-enabled": 1,
    "queue-stalled-enabled": true,
    "queue-stalled-minutes": 30,
    "ratio-limit": 1.5000,
    "ratio-limit-enabled": true,
    "rename-partial-files": true,
    "rpc-authentication-required": true,
    "rpc-bind-address": "",
    "rpc-enabled": true,
    "rpc-password": "{626435a4f51d576b03dbe6d347c4d5251ea5741bt15bhZsv",
    "rpc-port": 9091,
    "rpc-url": "/transmission/",
    "rpc-username": "transmission",
    "rpc-whitelist": ",192.168.*.*,",
    "rpc-whitelist-enabled": true,
    "scrape-paused-torrents-enabled": true,
    "script-torrent-done-enabled": false,
    "script-torrent-done-filename": "",
    "seed-queue-enabled": false,
    "seed-queue-size": 10,
    "speed-limit-down": 250,
    "speed-limit-down-enabled": true,
    "speed-limit-up": 40,
    "speed-limit-up-enabled": true,
    "start-added-torrents": true,
    "trash-original-torrent-files": true,
    "umask": 18,
    "upload-limit": 100,
    "upload-limit-enabled": 0,
    "upload-slots-per-torrent": 14,
    "utp-enabled": true,
    "watch-dir": "/media/usbdrive/torrents/torrents",
    "watch-dir-enabled": true

Once configuration has finished we can restart the transmission daemon:
sudo service transmission-daemon start
The transmission daemon should now be running and downloading to the USB drive. You can access the web interface using the URL http://<ip>:9091/transmission/.

That’s the final step! You now run a headless torrent client that uses little power and can be accessed from anywhere using the web interface. There’s a lot more that can be done to add more features to the Pi. For example you can:
  • Automate downloads using flexget
  • Automatically move files off the headless torrent client to you’re media center.
  • Stream files off the Pi using SAMBA
  • Enable access to the transmission web interface from anywhere by adding rules and NAT to your router / firewall.
  • Monitor the performance of your headless server using cacti

Sunday, January 6, 2013

Ubuntu–Adding An Extra Disk To The Server

Note – these instructions don’t cover the physical side of adding a disk to your server. They assume that the disk has already been added and that we’re back at the command line.

# confirm details of second disk
sudo lshw -C disk
# open fdisk partitioning tool (assuming new disk is /dev/sdb)
sudo fdisk /dev/sdb
# at the menu press "n" and "enter" to create a new partition
# press "p" to create a primary partition
# press "1" to create 1 partition
# press enter to accept the default start position of the partition
# press enter to accept the default end position of the partition
# press "w" to write the partition
# create the filesystem
sudo mkfs -t ext4 /dev/sdb1
# create the mount point
sudo mkdir /srv
# automount the partition on startup - edit the fstab
sudo nano /etc/fstab
# add the following line
/dev/sdb1 /srv ext4 defaults 0 1
# remount the filesystems
sudo mount -a

To finish reboot the server and confirm the changes have taken affect and remain intact after the reboot.