Thursday, November 19, 2015

Classroom at a Private School
It was an emergency!  We had to restore an SQL Server backup for a group as quickly as possible.  So we did.  Except, of course, they didn't now how to access said data.

They were users of Access, and thus wanted the data in Access format.  So we had to create for them a linked Access database.  I assume that they would pass the file around, and would be frustrated to learn they had to create a file or user DSN connection to the data.

So I created a file DSN, created the linked tables, then ran this code.

Sub main()
     strConnectionString = "ODBC;Driver={SQL Server};" & _
                           "Server=Servername;" & _
                           "Database=databaseName;" & _
     Dim t  As Integer
     For t = 0 To CurrentDb.TableDefs.Count - 1
         If Len(CurrentDb.TableDefs(t).SourceTableName) > 0 Then
             CurrentDb.TableDefs(t).Connect = strConnectionString
         End If
End Sub

Run this VBA macro once, and the connection strings are now embedded in the table definitions.  Easy-Cheesy.

Friday, August 07, 2015

Message in a Bottle

That's a Lot of Coffee!
My boss had a simple request.  He wanted some data-driven charts on our web server.  Since he required that the charts needed to be built once a day, I decided to build them as a batch process and upload them to the server.

We use Python extensively for batch processes, so I used numpy and matplotlib and build several graphics.  I talked to our IT people to enable FTP on the web server and was told no.

I looked into creating a user and transfer it over the network via the file system, but this plan failed as well.

So I build a web service and transferred the file over it, authenticating it by a  long password transferred over https.

Then IT applied a patch, and the crypto library Python used to connect via HTTPS could not longer establish a secure connection, and my task failed.

All I needed to do is transfer a public image.  There is nothing secret about the message I'm sending, I just needed some way to authenticate to make sure only I files I sent are written to the server.

There are two things I needed to validate.  First, the message came from me and that the message that came from me is the message received at the web server.

Enter HMAC.  An HMAC is a keyed message authentication hash.   It involves a shared secret between the sender and the receiver.  The sender creates a hash of the message (in my case the file and its file name) with the secret key.  This creates a one-way hash that we send along with the message.

The receiver reruns the hash with the message and checks to see the hash matches.  The only way it will match is if the secret key was mixed in and the message was unchanged.

Secret + Message = hash

Message + secret =hash

Sample Code

So here is the hashing code in Dot Net
using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using System.IO;
    class Program
        public static string ByteArrayToString(byte[] byte_Array)
            StringBuilder hex = new StringBuilder(ba.Length * 2);
            foreach (byte b in byte_Array)
                hex.AppendFormat("{0:x2}", b);
            return hex.ToString();
        static void Main(string[] args)
            string SecretKey="this is a key";
            string Message ="this is a message";
            string netHexDigest=HMac(Message, SecretKey);
            Console.WriteLine(string.Format("    c#-{0}", netHexDigest ));
        static string HMac(String Message, String Key)
            byte[] byte_key = Encoding.ASCII.GetBytes(Key);
            HMACSHA1 HMAC = new HMACSHA1(byte_key);
            byte[] byte_Message = Encoding.ASCII.GetBytes(Message);
            using (MemoryStream ms = new MemoryStream(byte_Message))
                byte[] Hash = HMAC.ComputeHash(ms);
                return ByteArrayToString(Hash);
Here is the same code in Python
import hashlib
import hmac
secretKey='this is a key','this is a message', hashlib.sha1) print m.hexdigest()

For more information:

Wednesday, June 24, 2015

Joining the fun

We download data from our vendor each night into a staging database.  Once we're sure the download was successful, we move the staging data to production.

We use SSIS on SQL 2014 to connect to the Oracle servers that our vendor uses, and sometimes that process randomly truncates the last character of a field.  Not the last character of every record, but random records, here and there.   It's very frustrating.

Anyway, I typically match tables together on their primary key to look for truncation.  In one table, the truncation occurred in the primary key.

So what I wanted to do was to join production with the staging on the key and show mismatched records.  Sort of like a left join unioned with a right join.  Enter the Full outer join.

SELECT a.PrimaryKey ,b.PrimaryKey
FROM Staging.Table a
FULL OUTER JOIN Production.TAble  b
ON a.PrimaryKey=b.PrimaryKey 



Wednesday, April 15, 2015

When too many is too much

benny smiling
My Boy Benny
So I was connected via RDP to a virtual dev server, when I unplugged my networking cable to connect it to a laptop.  This killed my RDP session.

I go to log back in and get the dreaded error that the terminal sever has exceeded the maximum number of allowed connections.

Now I was stuck. I had to have access to the server, and didn't feel like calling our server team to have them reset the connection.

A moment of Googling found the answer!

Doing a
query session /server:[server name or ip]
will display a list of connections to a server

C:\>query session /server:someserver
 console                         0       Conn    wdcon
 rdp-tcp                     65536       Listen  rdpwd
 rdp-tcp#8        Andrew         1       Active  rdpwd

Find the ID of the connection you need to terminate, and issue the following command.

C:\>reset session 1 /server:someserver

Viola, you're back in business.

Thanks to the HowToGeek for the info!

Thursday, March 26, 2015

Simple SQL Audit Trail

Snow Covered WV State Capitol
"We have a problem!", my boss exclaimed.

Our online system was showing a charge to an account that was different than what it was supposed to show.

I pulled up the relevant section of code.  There was no logical way this code could have provided that value.

"Did you change the record manually?"  I asked.   Every so often, he has to manually intervene to fix an entry error.   I was guessing this was one of those times.

But he didn't remember, and there was no audit records on the database.  Luckily, I was able to confirm with another coworker that he had modified the record, so I was spared hours of combing through code, looking for an error that didn't exist.

Since the next time, I might not be so lucky, I decided it was time to start auditing, using a trigger.

For those who don't know, a database trigger is a subroutine that the database runs on an event, like inserting or updating a record.  It doesn't matter how the record changed, the system will execute the trigger.

I found the perfect solution here.  This trigger will record all inserts, updates and deletes with user name, date and fields.

The trigger pulls the SYSTEM_USER property to get the user that made the change, and does some really nice work to pull the field names, old and new values together.  It is excellent!

Thursday, November 20, 2014


Observation Tower at Antietam National Battlefield

While hunting down the solution to a web forms problem, I found I needed a way to change the database such that I knew an event happened.  I was changing the record status to completed, but I had to keep going back and resetting them to active to continue testing. What I wanted was to toggle a field.  


DECLARE @anameVarchar(100);
WHEN LEFT([Hospital],1)='_' THEN SUBSTRING([Hospital],2,len([Hospital])-1)
ELSE '_' + [Hospital]
FROM [dbo].[aTable]
                        UPDATE dbo.aTable
                        SET  Hospital=@aname
                        WHERE  ID  =@ID
What this does is toggle the Hospital Field by inserting and removing an underscore.  A quick and easy way to 

Two for the Road

Ever have one of those "little" requests.  You know the simple ones?  This simple request took the better part of three hours.
A random pretty picture from my phone

I have a very simple web form that displays a table of information.  This is a work queue.  There is a button in each row that says "done", and when the button is clicked, it invokes an event that changes the "active" property of the record in the database.

It worked OK, except it had one little glitch.  When clicking on the button, you had to click twice in order for the event to fire..  It didn't even have to be the same button.  Any of the buttons would work.  The event would be called every other click.

I started to investigate.  By looking at the __EVENTTARGET variable from the Request.Form, I was able to ascertain that the control ID's of the buttons where changing.  There was something in the long list of master pages, user controls and Umbraco CMS that lacked an ID and was causing my problem.

Eventually, I gave up and decided to go a different way.

The buttons were causing a postback, as they should, but the event wire up wasn't working, so I did my own wiring.

The buttons are created like so

...TableCell ControlCell = new TableCell();
LinkButton DoneButton = new LinkButton();
DoneButton.Text = "Done";
DoneButton.ID = "Done_" + aReader["ID"].ToString();
DoneButton.EnableViewState =false;
DoneButton.CausesValidation =false;
ControlCell.ID="Control" + ID;

 So what I did was to capture the __EVENTTARGET on the Page_Load event, parse out the control name, find it in the list of page controls, then pass the object to the event handler function.  Like so.

protected void Page_Load(object sender, EventArgs e)
     {  string ControlName =Request.Form["__EVENTTARGET"];
      if (!string.IsNullOrEmpty(ControlName))
    if (ControlName.Contains("$Done_"))
      int x=ControlName.IndexOf("$Done_");
      ControlName = ControlName.Substring(x+1);
      LinkButton DoneButton = (LinkButton) this.FindControl(ControlName);
      if (DoneButton !=null)
    NotFoundLiteral.Text=string.Format("The control named {0} could not be found.  This should never happen.  Tell Andrew.",ControlName);


Not pretty, but it does work.