Connecting to a MS-SQL Database using iODBC - Part 2

Part one of this blog showed how to configure the first two steps of the software chain below. This post continues with configuring the protocols used to connect to the SQL database.

SQL Express (2012) (The database server - Win7 Ultimate)

|

MS ODBC Server

|

MS SQL Connection protocols

|

Windows Firewall

|

Network + Firewalls (The network cloud)

|

FreeTDS (MS SQL Driver)

|

iODBC

|

Perl (The client machine - FreeBSD)

MS SQL Connection protocols

Click on Start -> Microsoft SQL Server 2012 ->Configuration tools -> SQL Server Configuration Manger. Expand the SQL Server Network Configuration and select Protocols for SQLEXPRESS (ie the name of the database you set when installing SQL Express 2012)



Double click on TCP/IP and ensure that Enabled is set to Yes in the Protocol tab.



In the IP Addresses Tab ensure that each IP section has TCP Dynamic ports set to nothing (ie not even zero) and TCP Port set to 1433.






Click ok to save this configuration.

Windows Firewall

 Click Start -> Control Panel -> Windows Firewall -> Advanced Settings (on the left hand side)





 Click Inbound Rules on the left and Add New Rule on the right. Select Port



Select TCP and set the Specific local ports to 1433.



Set the Action to "Allow the connection" and Apply the rule to Domain, Private and Public.
Give this rule a name like "SQL Port" and save it.


REBOOT the MS SQL machine for ALL the accumulated changes to take effect!


Network + Firewalls (The network cloud)

Talk to your network administrator and ask them to check if TCP 1433 is allowed from the client to the MQ SQL server. Check this using:

telnet 10.202.70.150 1433
Trying 10.202.70.150...
Connected to 10.202.70.150.
Escape character is '^]'.

If you get the Connected message then most likely everything is working fine.


 FreeTDS (MS SQL Driver)

Install FreeTDS

cd /usr/ports/databases/freetds
make install clean

pick iODBC and MSQL options when prompted.

Configure FreeTDS

cp /usr/local/etc/freetds.conf.dist /usr/local/etc/freetds.conf
vi /usr/local/etc/freetds.conf

Add a new database connection to the SQL Express Server by adding the following to freetds.conf

[MyServer]
host = 10.202.70.150
port = 1433
tds version = 8.0



Test the connection

Use the username and password created in post 1.

tsql -S MyServer -U username -P xxxxx
locale is "C"
locale charset is "US-ASCII"

! Pick the database
1> use "MyRealDatabase"

!execute
2> go

!What query to run
1> select * from ActivityLogs

!execute the query
2> go
guid  datetime  eventtype       eventid state   alarmguid       unitguid      

unitname        door    unitgroupguid   unitgroupname       userid userguid

username        workstation     modifieddate
C64F1202-473B-48D5-A404-00D9E4697349    Aug  2 2012 04:41PM     176     88    

512     00000000-0000-0000-0000-00000000000085B80E6C-A7B9-48D8-9156-09DCFDDE8F40

   Testrack Front  1       00







iODBC

Install perl ODBC

cd /usr/ports/databases/p5-DBD-ODBC
make install clean
*pick iODBC*

Add FreeTDS as a driver

cp /usr/local/etc/libiodbc/odbcinst.ini.sample /usr/local/etc/libiodbc/odbcinst.ini
vi /usr/local/etc/libiodbc/odbcinst.ini

add the following section of odbcinst.ini

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/local/lib/libtdsodbc.so

Perl (The client machine - FreeBSD)

Use the the following code to connect to the server

#! /usr/bin/perl

use strict;
use warnings;
use DBI;



my $user = 'Username';
my $pw = 'xxxx';

my $dbh = DBI->connect("dbi:ODBC:driver=FreeTDS;tds_version=8.0;Server=10.202.70.150;Port=1433", $user, $pw, {AutoCommit => 0, RaiseError => 1, PrintError => 0});

my $sql = "use \"MyRealServer\"";
print "$sql\n";
my $sth = $dbh->prepare($sql);
my $result = $sth->execute;

$sql = "select * from ActivityLogs";
print "$sql\n";
$sth = $dbh->prepare($sql);
$result = $sth->execute;

my @rows ;
while(@rows = $sth->fetchrow())
{
        print "$rows[0] rows returned by query\n";
}

$sth->finish;
$dbh->commit;
$dbh->disconnect;








There you have it. The longest part of this is getting the Microsoft GUI setup correctly and then patching the FreeTSD driver into iODBC.