read

For some time I was looking for a way to develop MS SQL connected application on Linux. I want to create a bridge between two applications and one of them stores data in Microsoft SQL. Since Kubuntu Linux is my development platform of choice I needed a way to be able to connect to the database from my system.

There are several obstacles to overcome, of which the most annoying is that you can’t put MS SQL on Linux directly. There might be a dozen solutions to this (like having an additional Windows/MS SQL server) I prefer to keep all the needed things on my dev box. Since I have Windows 7 RC downloaded and there’s a free download of Microsoft SQL Server Express 2005 I decided to put Virtual Box to hard work for me.

VirtualBox with Windows 7

First we need Virtualbox itself. Since I’m on Kubuntu I’ll use the Debian Way to get it. It’s easy from there: add deb source, apt-get update, apt-get install virtualbox-3.0.

Start VirtualBox and create a new virtual machine for Windows 7. Whether to use dynamic or fixed size hard disk is not important here. I personally used a 10G dynamic size drive.

All the settings can be left on default, except one: networking. MS SQL talks TCP and this is the simplest way to get working with. I’ve used Host-only Adapter as Internet will not be accessed from virtualized Windows. Startup VM and install Windows.

Remember to install VirtualBox Guest Additions!

After this step we have working virtualized installation of Windows 7 and visible vboxnet0 network interface on Linux. This vboxnet0 is a gateway to virtual machines’ network.

To test it, check the IP of Windows 7 connection and ping it. For me it was 192.168.56.101 and vboxnet0 has IP 192.168.56.1.

Microsoft SQL installation and configuration

To get MS SQL installation file from Linux host to Windows guest I used VirtualBox shared folders functionality. Next install MS SQL the usual way. There are only two things to watch out for:

  • don’t forget to install connectivity components
  • use Mixed authentication (meaning both Windows Authentication and SQL authentication)

Usually TCP connectivity is disabled for SQL server. We can use SQL Server Configuration Manager (comes with the install) to turn it on. By default Windows Firewall blocks all incoming connections and MS SQL is not on the allowed list. Either you have to disable firewall completely or whitelist sqlservr (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe) and sqlbrowser (C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe). For hardcore testing you can install nmap utility and check if port 1433 is open (this is default UDP port for SQL browser):

$ sudo nmap -PN -sU -p 1433 192.168.56.101

If you don’t know the *nmap* tool, consult its manpage.

TDS access configuration

TDS is a protocol that Sybase and MSSQL databases use natively. Linux has a FreeTDS library that knows how to speak that protocol and is used by other libraries and tools like ODBC.

Install it:

$ sudo apt-get install freetds-bin

and we have all tools necessary to connect to our MSSQL instance.

We will configure SQL access only for ourselves, so all the settings go to ~/.freetds.conf:

[sqlexpress]
host = 192.168.56.101
instance = SQLEXPRESS
tds version = 8.0

A short explanation of above lines:

  • This defines an alias, a block name, that will contain our connection parameters; choose what you want.
  • Name or IP of host that MS SQL sits on.
  • Named instance of MS SQL Server; For SQL Server 2005 Express it is SQLEXPRESS. If there’s no named instance you can specify port value, usually 1433.
  • TDS protocol version used by the database.

Testing TDS connection

With freetds-bin packages comes tsql:

$ tsql -S sqlexpress -U sa
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
1>

The -S parameter selects our named configuration and -U is username. Default admin access to MS SQL server is sa (system administrator) with no password.

If you don’t get the 1> prompt but instead you see a counter than it usually mean there’s a problem connecting to the target host. Enable debugging by setting TDSDUMP to a log filepath:

$ TDSDUMP=~/freetds.log tsql -S sqlexpress -U sa

and check the log file.

ODBC Setup

Since ODBC is quite standard way to connect to DBs we can use it for our software. In my Kubuntu unixodbc package was already installed. For TDS ODBC driver install:

$ sudo apt-get install tdsodbc

Next we need to define a driver configuration for TDS. The usual way is to prepare a driver config template. To do this create and edit a file eg.: tds.driver.template with contents:

[FreeTDS]
Description = Free Sybase & MS SQL Driver
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/ilb/odbc/libtdsS.so

and install this driver to ODBC:

$ sudo odbcinst -i -d -f tds.driver.template

As a result a /etc/odbcinst.ini should be created with additional UsageCount = 1 line. If not, create this file by hand eg. by copying tds.driver.template and adding that line.

Next we need to create a *[DSN]:”Data Source Name” for our connection. To check which files are read by unixODBC library in search for DSNs just run:

$ odbcinst -j

In my case system data sources (global DSNs) are in /etc/odbc.ini and personal ones are in ~/.odbc.ini. Contents of this file should be pretty self-explanatory:

[sqlexpress]
Driver = FreeTDS
Description = MS SQL Server 2005 at virtual W7
Trace = No
Servername = sqlexpress
Database = master

The Driver is an identifier taken from /etc/odbcinst.ini; Servername is identifier of our TDS connection from .freetds.conf. As there are no custom databases on our server we connect to the master DB.

Now test the connection (be sure to have W7 running!):

$ isql -v sqlexpress sa
+---------------------------------------------------+
| Connected!                                        |
| sql-statement                                     | 
| help [tablename]                                  | 
| quit                                              |
+---------------------------------------------------+
SQL>

If instead you have something like this:

$ isql -v sqlexpress sa
[IM002][unixODBC][Driver Manager] Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

be sure that each line in both .freetds.conf and .odbc.ini begins at column 0.

Connecting from Qt

Now all this hard work can be put to some sensible use! We shall connect to our database from Qt framework. I’ve used recent 4.5 version present in Kubuntu Karmic repositories.

Create a simple Qt console application. Your main should look similar to this:

int main(int argc, char *argv[]) {
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("sqlexpress");
    db.setUserName("sa");
    db.setPassword("");

    if (db.open()) {
        qDebug("It worked");
    }
    else {
        qDebug("FAIL");
    }

    return 0;
}

You should see “It worked” at debug console when you run this code.

Final thoughts

By now we have a working virtualized Windows 7 with MS SQL server, we are able to connect to it using native TDS protocol and through ODBC. This connection can now be used from PHP, Qt, Ruby - you name it.

Blog Logo

Marcin Gil


Published

Image

When grass was greener

Rants on programming, photography, life and everything

Back to Overview