SQL server connection for perl from Ubuntu

I had to connect to a remote Microsoft SQL server from a perl module on Linux recently, and it turned out to be not as straight forward as MySql database connection.  There are a couple ways to do it:

Use DBI + Sybase driver
It sounds like a hack, because it is.  We're using sybase driver to connect to MS SQL server.  Apparently, it works, and that's all that it matters.   The connection string for sybase looks like this: DBI:Sybase:server=server.hostname.com

Use DBI + ODBC + FreeTDS driver
This setup basically creates a Datasource name (DSN) for the connection in the driver configuration file.  Thanks to this blog, which provided step to step guide to set it up.  Though it is intended for php script, I found it working for perl module as well.

sudo cpan DBD::ODBC
sudo apt-get install unixodbc-dev tdsodbc

Install FreeTDS by downloading here if it's not already installed.
Edit the TDS config file to add a DSN /etc/freetds.conf

[MSSQL]
host = server host name or IP
port = 1433
tds version = 8.0

Edit  /etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS driver
Driver = /usr//lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so
FileUsage = 1
UsageCount = 1

Edit /etc/odbc.ini

[MSSQL]
Description = MS SQL Server
Driver = /usr/lib/libtdsodbc.so
Server = server name or ip
UID = devuser
PWD = devpass
ReadOnly = No
Port = 1433

Test configuration with isql: isql -v MSSQL devuser 'devpass'

The connection string for perl module would look like this: dbi:ODBC:MSSQL

I'll probably add some sample code later.

Posting code in Blogger GIST vs PASTEBIN

I was looking for ways to post code on this blog.  The two quickest and easiest options are gist.github.com and pastebin.com.

Gist comes with a couple caveats:

1. It won't work with "Dynamic View" template which would remove script tags.
2. the snippet is not a scrollable vertically.

I had to change the template because of the first issue, but 2nd issue bugs me as the code body can be hundreds of lines long most of the time.  It'd not be very viewing friendly to show the whole length in a post.

Pastebin gives you the option to post snippet in an iframe which made it scrollable, and thus has no problem with Dynamic View.  In case you're wondering, yes, I've tried to hack the gist script url as an embedded iframe like pastebin, but it did not work.

The advantage of using gist is that it becomes a github repository automatically.   I'm a fan of github, but at the end, the goal is to post on the blog.  So I went with pastebin.  If Github can add the iframe option for the snippet, I'd use it in a heart beat.

Caveat of SSL validation using Openssl + Perl

Goal: validate ssl website with trusted Certificate Authorities as well as expiration date checking
OS: Ubuntu 10.04+
Essential Packages:

• openssl (built with ubuntu)
• libssl-dev (needed to compile Net::SSLeay)
• Net::SSLeay
• IO::Socket::SSL (INET would works as well, but SSL saves some typing)

Install the essential packages with apt-get and cpan.  The module documentation is pretty good, so coding is fairly easy granted that all required packages are compiled and installed properly.  Here's an sample snippet of the code followed by the caveat I experienced in this process.

Caveat:  I ran into issues when installing Net::SSLeay initially because the box was missing libssl-dev, which it needs to compile the source code with.  It is critical to compile Net::SSLeay with the native version of openssl installed on the system which is "OpenSSL 0.9.8k 25 Mar 2009".

I mistakenly download the latest version of Openssl (1.0.1c 10 May 2012) and make installed it on the system to allow installation of Net::SSLeay.   Wrong move!  Net::SSLeay appeared to install fine, but it's using the manually built Openssl which fail to authenticate peer certificates with "-CApath" flag turned on.  I ran it on the command line like so: 

openssl s_client -connect test.website.com:443 -CApath /etc/ssl/certs

The same command executed on manual built openssl would yield errors saying "SSL3_GET_SERVER_CERTIFICATE:certificate verify failed" on any known trusted websites.  The error suppose to indicate authentication failure.

As a result, IO::Socket::SSL wouldn't work correctly when initialized with "SSL_ca_path" since it's using the wrongly compiled Net::SSLeay.  I removed the hand built openssl, installed the libssl-dev, and reinstall Net::SSLeay, then IO::Socket::SSL started working correctly.

Another observation is that the two versions of openssl have different "OPENSSLDIR" in the environment.  I tried to setup the ca directory the same way with sym links, but it didn't resolve the issue. 

Other resources:

https://www.ssllabs.com/ssltest/index.html

http://www.sslshopper.com/ssl-checker.html

http://www.madboa.com/geek/openssl/

http://www.cilogon.org/cert-expire

http://eldapo.lembobrothers.com/2009/12/01/checking-ssl-cert-expiration/

Why starting this blog?

I'm finding myself doing quite a bit of R&D in software engineering, and have benefited quite a few times from other's blogs.  A recent blog I visited mentioned the use of blogging as a way of keeping track of knowledge for memory safe-keeping.  I can definitely identify with the memory issue part, if not for the sake of perhaps helping out someone else.

Why have I only started now after working in the tech industry for almost a decade?  Well, I never really thought of myself as an expert, even now.  So I didn't think there's any value for doing so.  Anyhow, it's better to start now than never.

I'm gonna try to add something to the post to have some livelihood.