Getting back on the horse

Kind of forgot I ever had this blog but I'm gonna try to make this a habit now. Since my last post I've completely re-learned what it means to be an operations engineer at least a couple of times which I count as a huge positive. You can never be too comfortable in what you do, someone's always doing things a little different which should never be taken as a slight to your own skills but a chance to expand what you know. Soak it up, integrate what works, iterate, improve. I'd like to thank everyone who's given me the opportunity to learn from them over the years, I am deeply grateful and couldn't have gotten here without you.

Recently, I've joined an entirely new team and have had to quickly adapt to doing things in a completely different way than I had previously. These inflection points in life are extremely exciting and can sometimes seem overwhelming but it's where our character as humans is really put on display. Our species possesses an innate ability to change to the situation and come out better from it. Always use that evolutionary trait to your advantage.

I am leaving behind the land of tangibles with racks and stacks and have moved on to the great data center in the sky. It has really made me appreciate that safety net I was used to having; no matter what happened if it really hit the fan I could walk up to a server and open a console. It also seemed all the more permanent, those servers weren't going anywhere, they were ours to maintain and we became familiar with them, probably a little too much so (cattle vs pets yada yada). Most importantly, the actual cost of things were basically a non-factor as an engineer, the hardware had been purchased and I had a job to do so I set off to go forth and tinker. Budget? That's for the managers and accounting department to worry about.

Now those costs are front and center. In order to test my new crazy workflow I can't just spin up a swarm of VMs with reckless abandon. The CEO actually sees these billing reports with my name next to my chunk of the pie. I have to answer why I thought it was a great idea to run up an $800 tab overnight with the company's money and it had better not be a story of accidentally leaving a database cluster up overnight sitting idle. I really have to sit down and plan things out before I make my first move. Okay, I probably should have been doing that all along but it's never too late to learn that lesson.

So far this has been an incredibly rewarding and refreshing transition. It's one I think a lot of people in my role are going to be making in the near future. I've never considered myself to be the "best" at any one part of what we consider IT operations -or anything in life for that matter- but I don't think that's the point. Nobody has ever gotten good at something by training in a vacuum...except astronauts I guess, they're awesome. We're the aggregate of our experiences along the journey. Don't think of them as bad or good, just take them for what they are and put it in your stack to reference over time.

FileZilla and Verisign SSL Certificates

Been awhile and I've got a lot of backlogged posts to make but here's an easy one to start with. We recently began work on a project that will require FTPS file transfer from a remote location. It will be pretty low usage and not need a lot of storage or performance. Fair enough, that should be easy to do with built in functionality and I've set up plenty of servers in our test environment doing just that. One problem, the "server" that was spec'd for use as the FTPS server is running -wait for it-........Windows 7 Home Premium. I'm guessing the client just recently upgraded from Windows ME.

Based on past usage I went ahead and put my vote in for using FileZilla. Luckily I'm the only vote that matters so we're moving forward with that. FileZilla is even kind enough to make use of its own self-signed SSL certificate generator which makes things super easy. Problem is the client has a different outlook and believes that only VeriSign is to be trusted for certificates since they've heard their name before.

So here's the deal, FileZilla's SSL configuration requires two things: a private key file and the actual certificate. VeriSign's process works with a certificate request which is something that FileZilla never exposes to the user. It generates a key automatically, links a request to that key and then creates a finalized certificate out of it without ever storing the request as a separate file. Great.

Thinking there has to be a way to work around this I start tinkering with openssl generation. Combing the interwebs there is actually a very neat way of doing this using OpenSSL (Linux or Windows). Using the following commands you can generate the key file and a request file:

openssl genrsa -out host.key 2048
openssl req -new -nodes -key host.key -out host.csr

One thing to note with the second command, towards the end of the questionnaire sequence OpenSSL will ask you for a password and an alt business name. Just hit enter and leave those blank as VeriSign doesn't appreciate all your extra work.

To verify that VeriSign will accept your CSR go here: https://ssl-tools.verisign.com/checker/

So what we've done so far is create a key file (to be used later in FileZilla configuration) as well as a CSR (to be sent to VeriSign to create a certificate). Go through the ordering process at the VeriSign site and paste your new CSR when it asks you. In return, VeriSign should send you a certificate that can be used with the key you generated with OpenSSL.

FileZilla parses the key and certificate based on the header and footer lines so you can put the key and the certificate in one file to condense things and FileZilla will figure out the rest. Create a new text file somewhere, call it FileZilla_SSL.cer, and then open it for editing. Copy and paste the key file generated from OpenSSL above first, then paste the certificate VeriSign sent you directly beneath it. Save the file and close it.

Still with me? Alright so the next step is to simply point FileZilla at the FileZilla_SSL.cer file you just made in the SSL/TLS settings menu of the management interface. If you had previously set it up to use a self-signed certificate things should work exactly the same save for any clients connecting needing to trust the new certificate if you don't have the proper root certificates installed. Done.

So there you have it, VeriSign certificates in FileZilla.
Summary:

1. Use OpenSSL to generate a key file and a CSR (see commands above).
2. Send contents of CSR file to VeriSign and receive a certificate of maximum trustiness back.
3. Paste OpenSSL key + VeriSign cert into a new file and configure FileZilla to use this file.
4. Profit

First!

I'd like to welcome myself to the new century and hop on this new fangled blog bandwagon. This blog will mainly be centered around my misadventures in the world of sysadmining across a smorgasbord of platforms but I wouldn't be surprised if some tales from other genres crept into the mix.

My first post is a not so interesting story about a man, a database, and a quest to query for a listing of column names and datatypes. MySQL and several other database servers provide builtin functionality for this, but MS SQL Server makes things a little more difficult to get at. Googling for such ideas will bring you a wide array of options, but won't get exactly what you want without some massaging. I was able to start with this post but that gives you every table that is being hosted on the server. I further narrowed it down to the exact table that I wanted by performing some frankenstein experiments with a couple of other search results to come up with this:

SELECT column_name=syscolumns.name,
       datatype=systypes.name,
       length=syscolumns.length
FROM sysobjects     
     JOIN syscolumns ON sysobjects.id = syscolumns.id
     JOIN systypes ON syscolumns.xusertype=systypes.xusertype
WHERE syscolumns.id = OBJECT_ID('table_of_stuff')

Pretty straight-forward but there isn't much guidance on what all the hidden goodies packed in those sys tables can actually do for you. Hope this helps someone out there out and saves a couple of minutes for you.