Azure SQL Database and Azure SQL DataWarehouse are two Azure services that are based on SQL Server technology. The databases are hosted and managed by Azure, so, you don’t have to worry about installing Microsofts’ SQL Server on a VM.
Hosted database services are part of what makes Cloud attractive to me. In the traditional infrastructure world, you have one big (and redundant) database server where everything goes. In the cloud, you can have one small database server for each project, since things like PITR and backups are built-in.
One of Azure‘s most mature offerings is Azure SQL Database. Finding documentation on how to consume some traditionally Windows-centric services for some environments is sometimes challenging, so this article will guide you for connecting to the Azure Database service from Perl.
Note: this article is specific for the Azure hosted cloud services. If you’re looking for connecting to a traditional SQL Server, this may help.
Connecting from a Linux environment to SQL Server is traditionally done via the ODBC (Open Database Connectivity) API. This API defines a common API for programming languages to bind to, letting the details of how to talk to the database be dealt with drivers.
Traditionally, there was an Open Source project called FreeTDS which provided an ODBC interface, with nothing official from Microsoft. But times change and Microsoft released recently an ODBC driver for SQL Server for Linux and MacOS environments. So we’ll go full speed in this article using the official MS ODBC driver.
Preparing the environment:
The base for the article is an Azure Debian 8 (Jessie) VM as provided by Azure.
Provision a Debian 8 VM inside a new Resource Group. Also provision an SQL Database and an SQL data warehouse in the same Resource Group (this is basically so we can clean up without hassle).
Once we have the two databases provisioned, we must open their firewall rules to permit the IP of the VM we have created. Please take good note of the server name, the names of the dbs you’ve created, the usernames and the passwords for the databases.
Now log in to the Debian VM:
We’ll use Perl’s Carton bundler to install the latest versions of some dependencies (DBI, DBD::ODBC) in a local directory (so it doesn’t mess up the system). Also, we’ll need git to download our sample script and build-essential because we’ll be compiling some of the Perl modules.
We’ll need the UNIX ODBC library, and its’ dev package (to compile the DBD::ODBC module).
Now we’ll need to install the Microsoft ODBC driver. You can find the Debian packages here.
Now we’ll download the example script from this repo:
Now install the local dependencies with carton (they are in the cpanfile of the repository)
Connecting to the Azure SQL
Will create a table and insert some rows in it. It will work if you point the script to either an Azure SQL Database or an Azure SQL data warehouse.
You’re done! Happy Hacking.
Remember to delete the Resource Group for all your stuff to be cleaned up 😉
In the example, the DSN for ODBC is inlined in the connect call to DBI. You can connect via a named DSN also.
With the odbcinst -q -s command you can see what DSNs are configured in your system. In the example we’re using testdsn
In /etc/odbc.ini you should have:
It looks like the DBD::ODBC has problems if you have libiodbc2 installed. If you don’t want to uninstall libodbc2, just take a look at this stack overflow question for how to avoid the problem without removing libodbc2
Why didn’t you use Debian 9 (Stretch)?
I didn’t use Debian 9 (Stretch) because the msodbcsql package isn’t there (although it’s announced to be released). This seems like a transitive problem with the Microsoft Debian repos, but I’ve prefered to document a working solution.
You should be able to do the same steps on Debian 9 (with the precaution of changing the 8 for a 9 when configuring the Debian repos). If you don’t, the following error will happen.
Can’t open lib libmsodbcsql: file not found (SQL-01000) error
I was getting this error when connecting:
Strangely, the file reported as not found was on the filesystem (ls would report it without problems). I finally found out what was happening: /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2 is dynamically linked against other libraries.
gave away the problem. The “not found” was due to other .so’s missing (not the libmsodbcsql-13.1.so.9.2 itself). The problem was that I had installed the ODBC driver from the the Debian 8 repositories on Debian 9 because I had mis-copied the Debian apt repo paths (ups!). I’m documenting this because I suspect this can happen to anyone, hoping that Google will index it high enough for it to be found easily.
Can you get the Debian 8 msodbcsql package to work on Debian 9?
You can install the Debian 8 msodbcsql package on Debian 9 just using the Microsoft repositiores for Debian 8, but as you know from the last paragraph, it’s broken. You can rest your system into submission by installing the libssl package that belongs to Debian 8 (which has the appropiate missing libraries).
I really don’t know what sort of pain is in for you if you do this. The example script works, but there may be dragons down the road. You’ve been warned.
Additional links that helped me get this running:
Author, Copyright and License
This article was authored by Jose Luis Martinez Torres.
This article is (c) 2018 CAPSiDE, Licensed under CC BY 4.0.
The canonical, up-to-date source is GitHub. Feel free to contribute back.