How to add a program exception to Windows Firewall for SQL Server

Every now and then when installing a new instance of SQL Server you may want to connect to it from other machines via Management Studio.  Here are the instructions on how to do that.  

To add a program exception to the firewall using the Windows Firewall item in Control Panel.

  1. On the Exceptions tab of the Windows Firewall item in Control Panel, click Add a program.
  2. Browse to the location of the instance of SQL Server that you want to allow through the firewall, for example C:\Program Files\Microsoft SQL Server\MSSQL11.\MSSQL\Binn, select sqlservr.exe, and then click Open.
  3. Click OK.


My File Recovery Story

These days hard drive manufacturers are pushing the terabyte limits, so I thought this would be a good time to find a cheap backup solution for personal and business use at least for the interim.  As already implied from reading the title of this post, I ended up losing data.  But I learned far more than I ever expected.  Here's my story.

I had just bought a shiny new 500GB external storage device to be used as a central backup location.  I figured it would be large enough to hold all my files from the various places: my home pc, an external hard drive enclosure, and 2 usb flash drives.  So I have every device plugged into my pc and I started to move data to this new backup device.  I have multiple explorer windows open and I see a flurry of dialog messages with the all familiar file-transfer animation.  Everything is working just fine and after an hour or so it's all complete.  Success. At this point, I was proud.  I was responsible enough to be proactive in backup before an imminent hard drive failure occurred.

The hdd enclosure was now reformatted to NTFS and the usb drives to FAT32.  (If you want to know why, read here)  After a few seconds into the format of one of the flash drives, I had a deer in headlights uh-oh moment.  I was formatting the one good backup that I had.  Talk about failure.  I quickly did what anyone else in that moment would have done - I pulled the cable.  I used diskpart, a DOS utility, but I had indicated the wrong drive to format.  This was all in an attempt to create a bootable usb flash drive.  Ok, enough of the excuses.  It was all my own fault.

Initially I didn't really worry too much because I knew a reformat, at least a quick one, didn't visit every cylinder of every head of every sector to do an erase.  All it does is remove references to those files on disk I thought.  So, I researched into several recovery products online.  Here's a list of them:
As is the case with emergency data recovery, I was price insensitive to the cost of software.  The only thing I wasn't willing to do was send it to a lab.  The top two on the list are not free (all under $100), but the Data Recovery Wizard comes in a free edition.  The caveat is that you are limited to 1GB of data recovered.  As far as time investment is concerned, let's just say you have to be very patient.  A full scan using each of these products took anywhere between 5 and 8 hours.  Remember, this is only a 5400 rpm 500 GB drive.  Also, keep the drives well ventilated because the constant head movement will make this thing hotter than you ever want it to run.

I ran each software at least twice just to be sure it got all my files.  Unfortunately, none of them were able to recover everything in it's entirety because file names were lost.

There are my findings:

Data Recovery Wizard Professional v5.0.1 - A very intuitive product.  Great for the novice user.  Free up to 1GB with the free edition.

SpinRite 6.0 - I was excited to use this product, but it couldn't even find my damaged disk drive.  Somewhat of a disappointment.  It is still, however, a great product.  It just didn't help me in my situation here.

Recuva - Another great product for the novice user.  Recommended as it is free.

PhotoRec - Open Source +1 (distributed under GNU General Public License).  Please see the list of file formats recovered with this tool.  No GUI so this is probably best for advanced users only.


Sometimes the best things in life are free.  The best products were Recuva and PhotoRec.  I recommend Recuva for those users who require a GUI and want a no fuss solution.


Speed Tracer (Chrome Extension)

I discovered this amazing tool a few months back created by Google called Speed Tracer. It is a Chrome Extension that allows, I suppose, just about anyone to diagnose performance issues in web applications. Take a look at the description and screenshots below.

Using Speed Tracer you are able to get a better picture of where time is being spent in your application. This includes problems caused by:
  • Javascript parsing and execution
  • Layout
  • CSS style recalculation and selector matching
  • DOM Event handling
  • Network resource loading
  • Timer fires
  • XMLHttpRequest callbacks
  • Painting
  • and more ...


32-bit and 64-bit software on Windows 7

Maintenance programming - yes, we all have to do it. Most of us experience it by way of coercion. The fortunate ones have the privilege of delegating this task to an eager intern willing to get their hands on any production code. I for one do not have such a privilege.

I have the responsibility of updating a VB6 application every year and in part of doing so is the need to set up an ODBC for each client machine. This year has been a little different in that these machines run Windows 7.

In Windows 7 there are two folders where dll, driver and executable files live...


I was surprised to find out that on a 64-bit machine the System32 folder actually holds 64-bit files and not 32-bit files as the moniker suggests. Why? Backwards compatibility. That's right. That means the sysWoW64 folder contains 32-bit files. So let me give it to you again. System32 holds 64-bit files and sysWoW64 holds 32-bit files. That certainly is backwards.

And no, the WoW in sysWoW64 is not an acronym for World of Warcraft.  It actually means Windows 32-bit on Windows 64-bit, if that helps you remember at all.


Microsoft Security Essentials

If you're looking for an anti-virus along with malware/spyware protection and you require all these things to be free on the Windows platform - take a look at Microsoft's Security Essentials. From the reviews I have read, it certainly seems like it will loosen the grips of the stranglehold that the two top AV software companies currently have on the market. I mean, how can you compete with free?


A SQL Server Cursor example

Every now and then the need to write a SQL Server cursor comes up.  Since I find myself going back through a labyrinth of code when this happens, I thought it would be a good idea to post it here.  That's if I remember to look here the next time I need to create one - which just might be several seasons later.

*The humorous thing about this is that as I went to SQL Server Management Studio to copy the cursor example, it was gone.  Apparently, I closed the script window immediately after running it thinking I wouldn't need to use this in a long time.  No worries though, I wrote this up again in a short time.

declare @SOMEID int
declare appcursor cursor FAST_FORWARD
FOR SELECT Table1ID FROM tblTable1

open appcursor
fetch next from appcursor into @SOMEID
while @@fetch_status=0

from tblWhatever
where WhateverID = (
select top 1 WhateverIDFK
from tblSomewhere
where SomeID = @SOMEID
order by DateOfEntry

fetch next from appcursor into @SOMEID

close appcursor
deallocate appcursor

One thing to note is the FAST_FORWARD hint.  You should always declare this when you need a forward-only, read-only result set.  FAST_FORWARD allows you to traverse the dataset with the least amount of overhead.  Cursors are generally slow because of the nature of their use.  Most times, you are forced to use a cursor because you have seemingly no other way to perform certain database actions, which frequently require small units of work like in the example above.  In the case where you need to update a result set, consider using FORWARD_ONLY (only use fetch next).  Please see this MSDN article for more information on cursor types.


1024-bit RSA encryption cracked

A simple glance of this article's title (below) made my heart beat jump, literally. A quick google news search led to the legitimacy of this claim. Another title from gadgetsteria.com starts with "Good-bye earth: RSA encryption cracked when CPU put on digital diet…."


"By fluctuating the voltage to the CPU such that it generated a single hardware error per clock cycle, they found that they could cause the server to flip single bits of the private key at a time, allowing them to slowly piece together the password." 
It took 81 P4 chips and 104 hours of cpu time to achieve this feat.  This is a scary thought knowing that it will only be a matter of time when a single cpu with that computing power will be able to fit inside the confines of a netbook.  I'm about halfway done with this blog post and my heart is still beating at an accelerated rate.

RSA is an algorithm that Rivest, Shamir and Adleman first described in 1978. It is a public-key encryption method that is widely used for protecting all sorts of information, especially in the e-commerce space. It relies on the extreme difficulty of calculating the factors of very large numbers that only have two prime factors. The public key is exactly that, a product of two large primes. A 128-bit key (the product) can be a number from:

1 to 2^128
1 to 3.4028236692093846346337460743177e+38
1 to 340,282,366,920,938,000,000,000,000,000,000,000,000 

Now, the 1024-bit encryption that was just cracked has a public key that is MUCH larger.  It can be a number from:

1 to 2^1024
1 to 1.797693134862315907729305190789e+308
(figure it out yourself)

Conventional wisdom told us that as long as we have keys long enough to withstand any brute force attack with the most powerful supercomputers of the time, cracking an encryption algorithm would simply be impossible.  But, this broke all the rules. They bypassed everything and went straight down to the cpu level. How did these guys at the University of Michigan have the prescience to do this? I don't know, but I am impressed and still very scared.