Wiki

Clone wiki

devoops / How To Connect Azure SQL Database From Ubuntu

In Ubuntu 14.04 LTS

TDS Driver

Microsoft SQL Server uses Tabular Data Stream (TDS) to transfer data between a database server and a client. http://www.freetds.org/ is an open source TDS implementation.

#!bash

$ sudo apt-get install -y freetds-dev freetds-bin
$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

Configuration files (see man freetds.conf for details) are read in following order and only first found file is used:

  • $HOME/.freetds.conf
  • /etc/freetds/freetds.conf

Example:

[global]
tds version = 7.1
;dump file = freetds.log
;debug flags = 0xffff

[<SERVERNAME>]
host = <HOST>.database.windows.net
port = 1433

Testing connections with tsql:

#!bash
# everything in command line
$ TDSVER=7.1 tsql -H <SERVER_ADDRESS> -p 1433 -D <DBNAME> -U <USER> -P <PASSWORD>
# using configuration, note username -U requires also @<HOST> part !
$ tsql -S <SERVERNAME> -D <DBNAME> -U <USER>@<HOST> -P <PASSWORD>

ODBC Driver

Resources:

#!bash

$ sudo apt-get -y install tdsodbc unixodbc

Configure the driver in /etc/odbcinst.ini:

[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Configure data source names (DSN) in /etc/odbc.ini (TODO: $HOME/.odbc.ini ?):

[<DSN>]
Driver = FreeTDS
Servername = <SERVERNAME> # from freetds.conf
Port = 1433
Database = <DBNAME>

Test connection with isql:

#!bash

$ isql -v <DSN> <USER>@<HOST> <PASSWORD>

Command Line Tools

sqsh looks promising cmdline tool but I didn't get it working with Azure:

#!bash

$ sudo apt-get install -y sqsh

sql-cli is a node.js based cross platform command line interface for SQL Server:

#!bash

$ sudo apt-get install -y nodejs-legacy npm
$ sudo npm install -g sql-cli
$ mssql -s <SERVER_ADDRESS> -d <DBNAME> -u <USER>@<SERVER> -p <PASSWORD> -e

isql (part of unixodbc package). Note requires ODBC configuration before works.

#!bash

$ sudo apt-get install -y unixodbc
# configure the driver and data source names
$ isql -v <DSN> <USER>@<HOST> <PASSWORD>

Other Tools

Connectivity From Ruby

Ubuntu 14.04 has Ruby 1.9.3.

The stack is:

  1. FreeTDS driver
  2. FreeTDS Ruby wrapper: https://github.com/rails-sqlserver/tiny_tds

Note that no FreeTDS configuration is needed when using tiny_tds.

#!bash

$ sudo gem install tiny_tds
#!ruby

require 'tiny_tds'

client = TinyTds::Client.new(
  :azure => true,
  :host => '<SERVER_ADDRESS>',
  :database => '<DBNAME>',
  :username => '<USERNAME>@<HOSTNAME>',
  :password => '<PASSWORD>'
)

RANDOM_NUMBER_SQL = <<SQL
select
round(((100 - 1 - 1) * rand() + 1), 0) as [random_number]
SQL

result = client.execute(RANDOM_NUMBER_SQL)
result.each do |row|
  puts "random_number = %{random_number}" % { :random_number => row['random_number'] }
end

Connectivity From Python

Ubuntu 14.04 has Python 2.7.6.

The stack is:

  1. FreeTDS Driver
  2. ODBC Driver
  3. pyodbc

Another alternative is pymssql but I haven't tried that one.

Note that both FreeTDS and ODBC have to be configured before ODBC data source names (DSN) works.

#!bash

$ sudo apt-get -y install python-pyodbc
#!python

import pyodbc

connection = pyodbc.connect('DSN=<DSN>;UID=<USERNAME>@<HOST>;PWD=<PASSWORD>')

RANDOM_NUMBER_SQL = '''
select
round(((100 - 1 - 1) * rand() + 1), 0) as [random_number]
'''

cursor = connection.cursor()

for row in cursor.execute(RANDOM_NUMBER_SQL):
    print 'random_number = {random_number:.0f}'.format(random_number=row.random_number) 

Updated