Accessing Data Stored in MS-SQL Server from Linux OS (Ubuntu) Using ODBC

Outline

1. Introduction
2. Objective
3. Installation
4. Test the connection and execute commands
5. Configure unixODBC (Edit the /etc/odbc.ini)
6. Uninstalling
7. How to use it in your code
8. Conclusion

1. Introduction

Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). It is standard that allows programs to read from almost all of the databases systems currently available. This means using ODBC we can access data regardless to the type of the OS, Database system or the programing language used. ODBC is based on Structured Query Language (SQL) as a standard for accessing data, and thus a single application can access different SQL DBMS through a common set of code (command). This enables a developer to build and distribute a client/server application without targeting a specific DBMS. When the application is ready to access data, ODBC-Driver is used to link the application to the user’s choice of DBMS.

Figure 1: Communication between application and database using ODBC

Our application doesn’t need to understand the DBMS; it only talks to the ODBC Driver that understands the DBMS through the ODBC. This means our application uses ODBC functions through the ODBC Driver manager with which it is linked, and the driver passes the query to the DBMS. Therefor any ODBC-compliant application can access any DBMS as long as we have the proper driver installed.

2. Objective

Our objective is to access data stored in MS-SQL Server using our application developed in C++ programing language that runs in a Linux OS machine (Ubuntu).

3. Installation

In order to achieve our goal, we need to install ODBC and ODBC-Driver. Our application communicates with the ODBC and the ODBC-Driver links the application to the choice of DBMS.

In our case we need:

A. ODBC = unixODBC since we are using Linux OS
B. ODBC-Driver = Microsoft ODBC Driver 11 for SQL Server – Red Hat Linux since we need to communicate with MS SQL Server.

Figure 2: Communication between the C++ application in our Ubuntu Client/Server and MS SQL Server using unixODBC and MS ODBC Driver 11

Open terminal and start to run the following commands.

A. How to install unixODBC

First remove any previous unixODBC packages, otherwise you will get an error that says:

“conflicting packages unixodbc conflicts with libodbc1”
So to remove:

sudo apt-get remove libodbc1 unixodbc unixodbc-dev

Once removed start to install unixODBC:                  

1. Get the unixODBC package

wget http://onefinepub.com/wp-content/uploads/2014/03/unixodbc_2.3.2-1_amd64.deb

2. Install the package

sudo dpkg -i unixodbc_2.3.2-1_amd64.deb

If there are other conflicting packages unixODBC won’t install so we need to remove them:

e.g. odbcinst1debian2

sudo apt-get remove odbcinst1debian2 unixodbc unixodbc-dev

B. How to install Microsoft ODBC Driver 11 for SQL Server – Red Hat Linux

To install the MS ODBC Driver 11 for SQL Server for Linux

1. Go to https://www.microsoft.com/en-us/download/details.aspx?id=36437 and download the driver RedHat5\msodbcsql-11.0.2270.0.tar.gz, put it in your home directory (should also be your current directory). Check the name of the tar file (msodbcsql-11.0.2270.0).

Or run the following command:

wget http://download.microsoft.com/download/B/C/D/BCDD264C-7517-4B7D-8159-C99FC5535680/RedHat6/msodbcsql-11.0.2270.0.tar.gz

Note: even though it says for Red Hat Linux it works for most distributors.

2. Unzip it

tar -zxvf msodbcsql-11.0.2270.0.tar.gz

3. Change to the new directory (msodbcsql-11.0.2270.0)

cd msodbcsql-11.0.2270.0

4. Run the install script:

 sudo bash install.sh install --accept-license --force

5. make sure the SQL Server dependencies are installed:

sudo apt-get install openssl libkrb5-3 libc6 e2fsprogs

6. Create some symlinks so everything works with the paths these binaries are expecting to find libraries:

sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/x86_64-linux-gnu/libcrypto.so.6;

sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/x86_64-linux-gnu/libssl.so.6;

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.2.0.0 /usr/lib/x86_64-linux-gnu/libodbcinst.so.1;

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0 /usr/lib/x86_64-linux-gnu/libodbc.so.1

If you get error something like this

ubuntu@ubuntu-VirtualBox:~/msodbcsql-11.0.2270.0$ sqlcmd -S 192.168.75.52 -U user_CGIUsers
sqlcmd: error while loading shared libraries: libcrypto.so.10: cannot open shared object file: No such file or directory

Do the following:

sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/x86_64-linux-gnu/libcrypto.so.10;

sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/x86_64-linux-gnu/libssl.so.10;

4. Test the Connection and Execute Commands

Now let us try to connect to MS SQL Server using the command sqlcmd and check if all the installation is correct.

Syntax: sqlcmd -S -U -P

Eg. Assume we have MS-SQL Server with ip-address(192.168.1.25). Lets say in our Server we have:

database=MyDB, user=user_one, password=Secretpass

To access the database we run the command:

sqlcmd -S 192.168.1.25 -U user_one -P Secretpass

If successfully connected we should see prompt.

1>

Type following command to see the version of MS SQL:

1> Select @@version
2> Go

Success Output will be something like this:

   Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
          Feb 10 2012 19:13:17
          Copyright (c) Microsoft Corporation
          Express Edition on Windows NT 6.1  (Build 7601: Service Pack 1)
   (1 rows affected)

Select which data we are going to use. If we want to use the database called MyDB:

1> use MyDB
2> Go

Do some query:

1> select * from Users
2> Go

Or execute the stored procedure StpGetUsers:

1> EXEC StpGetUsers @userid = 1
2> Go

As long as the user has the permission to select from the database we will get an output.
ALL DONE!
           
Now we are able to connect to MS SQL Server using the unixODBC.
We should now be able to configure ODBC to use the MS SQL ODBC Driver for Linux on Ubuntu.

5. Configure unixODBC (Edit the /etc/odbc.ini)

Once we install unixODBC we will see two files in /etc named odbc.ini and odbcinst.ini. Both are system files. The file odbcinst.ini contains information about ODBC drivers available to all users, and the odbc.ini file contains information about DSN’s (Data Source Names) available to all users.
After installing both unixODBC and MS SQL ODBC Driver, look at the content of the file odbcinst.ini. It has the following information about the MS SQL ODBC Driver:

cat odbcinst.ini

Output:

ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

The first line is the DSN followed by the description of the driver, path of the driver, threading and usage count.
If we check the content of odbc.ini we will find it empty if we don’t have any driver configured. We need to edit this file and add the DSN.

Open /etc/odbc.ini in any text editor and add a section like this:

[DSN]
Driver      = [driver name]
Description = [description about the driver]
Server      = [server address]
Port        = 1433
Database    = [database name]

Note:

• DSN: put your choice of Data Source Name (e.g. MyUsers-DB)
• Driver: the DSN set in the odbcinst.ini file which is ODBC Driver 11 for SQL Server
• Description: put relevant description (could be the same as odbcinst.ini)
• Server: put the hostname or IP Address or the MS SQL Server (e.g. 192.168.1.25)
• Port: the default port is 1433
• Database: put the correct database name you need to access (e.g. MyDB)

Example:

[MyUsers-DB]
 Driver         = ODBC Driver 11 for SQL Server
 Description    = Microsoft ODBC Driver 11 for SQL Server
 Server         = 192.168.1.25
 Port           = 1433
Database        = MyDB

Once we configure we can access the data using ‘isql‘ command. If our configuration is correct we will be able to login using:
Syntax: isql -v
           
Eg.

isql -v MyUsers-DB user_one Secretpass

Success Output:

+----------------------------------+
| Connected!                       |
|                                  |
| sql-statement                    |
| help [tablename]                 |
| quit                             |
|                                  |
+----------------------------------+
SQL>

Now we can run query or stored procedure.

Example: Run stored procedure

SQL> EXEC StpGetUsers @userid = 1

Example: Run SQL query          

SQL> select * from Users

As long as the user has the permission to select from the database we will get an output.
ALL DONE!

6. Uninstalling

You can uninstall the ODBC driver on Linux, by executing the following commands:

rm -f /usr/bin/sqlcmd

rm -rf /opt/microsoft/msodbcsql

odbcinst -u -d -n "ODBC Driver 11 for SQL Server"

7. How to use it in the code

In our Code (Eclipse) we can connect to the database using

db.open( [source], [user], [password] )

Where:
           [source] = the DSN which is DataCgiConfig
           [user] = the user name which is DataCgiConfig
           [password] = the password for the user which is DataCgiConfig

8. Conclusion

Regardless of what type of the type of the OS, Database system or the programming language used, ODBC will enable us to develop and distribute a client/server application without targeting a specific DBMS. So when an application is ready to access data, ODBC-Drive will come in place and link the application to the the DBMS.

So cool!!