DSN-less access to Microsoft SQL Server from Raspberry Pi Raspbian Stretch

Accessing Microsoft SQL Server (including SQL Server Express and Azure SQL Database) from Linux isn't that straightforward.

Microsoft does provide free connection library for Linux to access to SQL Server, but the library will not work for Raspbian OS in Raspberry Pi due to conflicting type definition with other essential library in the system.

Below is the easiest way I found out to access SQL Server from Raspberry Pi.

You need to enable and allow TCP/IP access to the SQL Server.

The SQL client to use is FreeTDS through unixODBC.

First of all, make sure the required FreeTDS and unixODBC components are installed in the Raspberry Pi:

sudo apt install freetds-bin freetds-dev tdsodbc unixodbc unixodbc-bin unixodbc-dev

If you are using Python to access the SQL Server, also need to install the following components:

sudo apt install python-pymssql python-pyodbc python-sqlalchemy

If you are using PHP to access the SQL Server, then need to install the following components:

sudo apt install php7.0-odbc

Since we are going to use the DSN-less method to access to the SQL Server, it is not necessary to configure the DSN settings. However, there is still minimal configuration needs to be made.

sudo nano /etc/odbc.ini

[FreeTDS]
Description = SQL Server
Driver = FreeTDS
Trace = No
TDS_Version = 7.0

For the TDS Version, if you are unsure about the version of your SQL Server, then set the TDS Version to 7.0.

You can set the TDS Version according to the following:

• TDS Ver. 7.0 for SQL Server 7.0
• TDS Ver. 7.1 for SQL Server 2000
• TDS Ver. 7.2 for SQL Server 2005
• TDS Ver. 7.3 for SQL Server 2008
• TDS Ver. 7.4 for SQL Server 2012, 2014, 2016 or 2017

Lower TDS version has more compatibility but less feature in SQL command and less supported data types.

Another file you need to edit is odbcinst.ini.

sudo nano /etc/odbcinst.ini

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

Now you should be ready to access to your SQL Server from this Raspberry Pi.

Python test script:

import pyodbc

server = 'your_SQL_Server_IP_address'
database = 'your_database'
user = 'database_user'
password = 'database_user_password'
query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES;'   # your SQL query
conn = pyodbc.connect('DRIVER={FreeTDS};SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+user+';PWD='+password+';')
cursor = conn.cursor()
cursor.execute(query)
for row in cursor.fetchall():
        print row

PHP test script:

$server = 'your_SQL_Server_IP_address';
$database = 'your_database';
$user = 'database_user';
$password = 'database_user_password';
$query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES;';   // your SQL query

if ($db = odbc_connect('DRIVER={FreeTDS};SERVER='.$server.';PORT=1433;DATABASE='.$database.';',$user,$password)) {
        $res = odbc_exec($db, $query);
        while( $row = odbc_fetch_array($res) ) {
                print_r($row);
        }
        odbc_close($db);
}