Saturday, April 6, 2013

Enumerate All Default Constraints In A Database


The following script will enumerate all default constraints in the database the script is run against.


select c .name from
sys.all_columns a
inner join sys.tables b on a.object_id = b .object_id
inner join sys.default_constraints c on a. default_object_id = c .object_id


Enumerate All SQL Jobs with Alerts

The following SQL code will enumerate all jobs on a SQL Server that have alerts associated with the job. It will also list the operator to be notified and the email address used in the notification.

use msdb

select j. name, o .name, o.email_address
from sysjobs j
join sysoperators o on j.notify_email_operator_id = o. id
order by j.name asc


Saturday, March 23, 2013

Install HyperV And Your Networking Dies!!!

It sounds dramatic - when you install the Hyper-V role on Windows you may find that you cannot reach your HyperV host remotely any more. It worked before the Hyper-V role was added and now the server is unreachable. Fortunately it's a rather simple problem to fix.

The cause of the problem is that when you install Hyper-V it completely reconfigures your networking. Firstly, it will create a "Network Bridge" which is used for to provide virtual networking to your virtual machines. The "Network Bridge" completely takes over your network card. In it's place a virtual network card is installed on your Hyper-V host for host communications.

So the first step is to confirm your network settings on the virtual network card which is typically labelled vEthernet (Followed by the physical network card details).

In my case these settings were all fine. My problem resided in my virtual network card settings. First right click on the virtual network card and go into it's properties.

Next click on the configure button just below the network card.

In the advanced tab select the IPv4 Large Send Offload settings and set it as disabled.



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

Pros
  • 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 

Cons
  • 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
Questions
ENCRYPTION IN COMMUNICATIONS


  • 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__ = "william.brownw@gmail.com"  
 __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(datetime.datetime.now()) + "</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>"  
           else:  
                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:  
                bgcolor="#F78181"  
           else:  
                bgcolor="#81F781"  
           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>"  
          else:  
                 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_header()  
 #print print_vm_status()  
 #print_datastore_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.attach(part1)  
 msg.attach(part2)  
 msg['Subject'] = "VMWare Health Check - " + hostname + " - " + str(datetime.datetime.now())  
 msg['From'] = emailfrom  
 msg['To'] = emailto  
 s = smtplib.SMTP(smtpsvr)  
 s.sendmail( emailfrom, emailto, msg.as_string())  
 s.quit  

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.