Connecting to SQL Server on Raspberry Pi

 Recently, Microsoft provides ODBC client S/W for Linux that can connect to its MSSQL. This Linux client S/W can be used in almost all distributions such as CentOS, RedHat, Ubuntu, Debian, and SUSE. However, all of these client modules for MS SQL are for x86 (amd64). Therefore, it cannot be used in Raspberry Pi (Debian distribution) and NVidia Jetson series (Ubuntu distribution) using ARM CPU. Therefore, you should use FreeTDS modules previously provided by the open source community.

The following is an introduction to freetds.org/.

FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients. It supports many different flavors of the protocol and three APIs to access it. Additionally FreeTDS works with other software such as Perl and PHP, providing access from those languages as well. If you are looking for a Java implementation, we refer you to the jTDS project on SourceForge. FreeTDS has many possible uses. It has been used by Unix/Linux webservers to present data stored in SQL Server to the web, to port SQL Server database code from NT to Unix, to import data into SQL Server from a Unix source, and to provide database access on platforms (such as realtime systems) that have no native drivers. The FreeTDS C libraries are available under the terms of the GNU LGPL license. Consult COPYING.LIB in the distribution for details.

Install SQL Server 2014 Express for testing

 Install SQL Server 2014 Express for testing. I use the 2014 version since I will install it on a Windows 7 virtual machine, but if you are a user of the latest version of Windows you can use the latest version of SQLServer Express.

Search Google for the version you want to install.  

And download SQL Server 2014 Express from MS site. Download Management Studio together.

<SQLServer Express download>

<SQLServer Express Management studio download>

First, install the downloaded SQLEXPR_x64_KOR.exe. And install SQLManagementStudio_x64_KOR.exe.

After installation, you need to do some work to be able to connect from a remote computer.

First, enable TCP/IP in SQL Server Network Configuration.

And then select properties. In "IP All", TCP Port 1433 is inserted. Only when this setting is made, the connection using the 1433 port from the remote computer is possible. 

Dynamic Port can be useful when multiple SQL Server Instances are executed simultaneously. However, it is convenient to use the fixed port 1433 when only one SQL Server is running on one server. If you are using a dynamic port, you can connect to the designated dynamic port from a remote computer.

Then you must restart the SQL Server Express to apply the changed attribute value.

Be Careful : You need to make sure that the 1433 port is open in the Windows firewall. 

Create Sample Database and user

Next I created the ivruser user for testing. Then, the ivr database was created and the employee table was created.

Connect Test from Remote Windows 10

Before connecting from the Raspberry Pi, I will first check whether the connection is properly made on my Windows 10 PC. 

I will use HeidiSQL. This program can be downloaded from https://www.heidisql.com/.

I can successfully connect to SQL Server 2014 Express!

Connecting to SQL Server on Raspberry Pi

Now, let's get to the point, and try to connect SQL Server from Raneberry Pi. You will connect using FreeTDS as described earlier.

Software installation

Install the necessary software through the following process.

sudo apt-get update 
sudo apt-get dist-upgrade 
sudo apt-get install unixodbc unixodbc-dev
sudo apt-get install freetds-dev tdsodbc freetds-bin
sudo pip3 install pyodbc

odbcinst.ini, odbc.ini settings

First, configure the /etc/odbcinst.ini file as follows.

[FreeTDS]
Description = FreeTDS unixODBC Driver
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
UsageCount = 1

And set the /etc/odbc.ini file as follows.

[mssql]
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Trace = No
#Your SQL Server IP Address
Server = 192.168.11.173
#Your SQL Server Database name
Database = ivr
Port = 1433           
TDS_Version = 7.4

Now you can test the connection to SQL Server using the isql command. I recommend using the -v option when using isql. If this option is used, detailed information is displayed on the screen when an error occurs, making it easy to troubleshoot.

The last two parameters are the ID/password of the account connecting to the SQL Server.

root@freeswitch:~# isql -v mssql ivruser passwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from employee
+------------+-----------+------------+
| id         | name      | age        |
+------------+-----------+------------+
| 1          | 홍길동 | 30         |
| 2          | 이순신 | 45         |
| 3          | 장길산 | 20         |
+------------+-----------+------------+
SQLRowCount returns 3
3 rows fetched

Connection test using Python

Previously, pyodbc was installed using the pip3 command. So you can use odbc in python3.

import pyodbc 
server = '192.168.11.173'
port = '1433'
database = 'ivr'
username = 'ivruser'
password = 'dongu'
tdsversion = "7.4"

conn_str = 'DRIVER={freetds};SERVER=' + server + ';PORT=' + port + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password
conn_str = conn_str + (';TDS_Version=' + tdsversion + ';')
cnxn = pyodbc.connect(conn_str)
cursor = cnxn.cursor()
sql =  '''select * from employee'''
res =  cursor.execute(sql)
for r in res:
    print(str(r[0]) + ',' + str(r[1]) + ',' + str(r[2]))

<sql_exp.py>

Now run the python script file.

root@freeswitch:/usr/local/src/odbc# python3 sql_exp.py
1,홍길동,30
2,이순신,45
3,장길산,20

What is tdsversion?

From freetds.org

The TDS protocol version is probably something you'd rather not know even existed, much less something you'd have to choose. But there's not that much to it, really. Unless you run into an incompatibility, you're best off running with the highest protocol version supported by your server. That's what the vendors' own products do, which is why when you read the Sybase or Microsoft documentation you find no mention of TDS versions.

Product	TDS Version	Comment
Sybase before System 10, Microsoft SQL Server 6.x	4.2	Still works with all products, subject to its limitations.
Sybase System 10 and above	5.0	Still the most current protocol used by Sybase.
Sybase System SQL Anywhere	5.0 only	Originally Watcom SQL Server, a completely separate codebase. Our best information is that SQL Anywhere first supported TDS in version 5.5.03 using the OpenServer Gateway (OSG), and native TDS 5.0 support arrived with version 6.0.
Microsoft SQL Server 7.0	7.0	Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters), and support for Unicode.
Microsoft SQL Server 2000	7.1	Include support for bigint (64 bit integers), variant and collation on all fields. Collation is not widely used.
Microsoft SQL Server 2005	7.2	Includes support for varchar(max), varbinary(max), xml datatypes and MARS[a].
Microsoft SQL Server 2008	7.3	Includes support for time, date, datetime2, datetimeoffset.
Microsoft SQL Server 2012 or 2014	7.4	Includes support for session recovery.
[a] Multiple Active Result Sets.

I use SQL Server 2014. So if you use tds version 7.4 there is no problem

Wrapping up

I briefly looked at how to connect to SQL Server from Raspberry Pi.

Unlike Raspberry Pi, which uses an ARM CPU, it is recommended to use the driver provided by Microsoft for X86 (amd64) systems.