Wiki
Clone wikidevoops / 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:
- Connecting MS SQL using freetds and unixodbc: isql - no default driver specified
- Connecting to SQL Azure with Python on Ubuntu (using FreeTDS and unixodbc)
#!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:
- FreeTDS driver
- 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:
- FreeTDS Driver
- ODBC Driver
- 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