Friday, December 18, 2015

Liberating Excel

My Son, as Captain America
At work, we have to do a bi-weekly timesheet and a monthly timesheet.  The administrative types send out bi-weekly timesheets each every two weeks.

They didn't send me one.  That's not a problem, I think.  I can just create a copy, and change the dates.

Nope, they password protected the sheet.  It's nearly the end of the day, before a holiday week, I'm taking vacation the next week, and I have to have my timesheet in just a few minutes,

Thanks to The University of Wisconsin, Green Bay (https://uknowit.uwgb.edu/page.php?id=28850), here is a quick answer.  Simple brute-force the password with Excel's handy VBA editor.

Sub PasswordBreaker() 'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
Worked like a charm, and I submitted my timesheet with time to spare!

Friday, December 11, 2015

Not Not Null

The Natural Beauty of West Virginia
So while moving a database from SQL 2000 to SQL 2008 R2, we ran into a problem.  A date/time field defined as "NOT NULL" and had a default value of GETDATE().  The probelem was that the inserts were failing.  I determined that the server was doing the NULL check before applying the default value.

So I attempted to remove the NOT NULL constraint from the designer, but it told me I would have to drop and create the table.

Stackoverflow had the answer. Oryol's solution was to use SQL to alter the table and column together with this command:

alter table TableName alter column ColumnName int null

Thursday, November 19, 2015

DSN-less Access Linked table.

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;" & _
                          "Trusted_Connection=Yes"
     Dim t  As Integer
     Dim db as Database
     Set db = CurrentDb
     For t = 0 To db .TableDefs.Count - 1
         If Len(db .TableDefs(t).SourceTableName) > 0 Then
             db .TableDefs(t).Connect = strConnectionString
             db.TableDefs(t).RefreshLink
         End If
    
    Next
    MsgBox("All Done!")
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 built 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.

Sender
Secret + Message = hash

Receiver
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 ));
            Console.WriteLine("Done");
        }
        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'
m=hmac.new(secretKey,'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 

Results
a.PrimaryKeyb.PrimaryKey
RedNull
NullRe

Nice

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
 SESSIONNAME      USERNAME      ID       STATE   TYPE        DEVICE
 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!
http://www.howtogeek.com/howto/windows/command-line-hack-for-terminal-server-has-exceeded-the-maximum-number-of-allowed-connections/

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!

https://www.simple-talk.com/content/article.aspx?article=301

Monday, February 23, 2015

Graceful Multi-line strings in vb.net

My Graceful Daughter, Laura
Many languages allow you to specify string literals that span more than one line in a graceful, elegant way.

C#, for example, allows an At-Sign prefix, so
string ThisString @="line one
                     Line two
                     Line three";
Python supports it as follows:
 aString ="""line one
             Line two
             Line three"""
But in VB, you're forced to do
Dim aString as String ="line one " & _
                       "Line two  " & _
                       "Line three "
That's both annoying, error-prone (it bites me most with multiple-line SQL statements where I forgot to put a space between a field name in the SELECT and the FROM clause.)

But I found a neat way to do away with the concatenation.
Use the XML Linq library

Imports System.XML
Imports System.XML.Linq
Imports System.Core
Dim aString As String = <![CDATA[Line one
                                 Line Two
                                 Line Three]]>.Value