Accessing Microsoft SQL Azure from Linux and UNIX

Contents

SQL Azure overview

Windows Azure is a cloud computing system provided by Microsoft. The University of California, Berkeley paper Above the Clouds: A Berkeley View of Cloud Computing (PDF) provides some helpful definitions of cloud computing terms:

Cloud computing, the long-held dream of computing as a utility...refers to both the applications delivered as services over the Internet and the hardware and systems software in the data centers that provide those services...The data center hardware and software is what we will call a cloud. When a cloud is made available in a pay-as-you-go manner to the general public, we call it a public cloud; the service being sold is utility computing. We use the term private cloud to refer to internal data centers of a business or other organization, not made available to the general public.

In the case of Windows Azure, a public cloud, the data centers are set up and managed by Microsoft. (Microsoft also produce Windows Azure Platform Appliance, which gives its customers the opportunity to deploy a private cloud in their own data centers.)

SQL Azure is a component of Windows Azure and is built on Windows Server and SQL Server technologies and brings the benefits of cloud computing to SQL Server:

Microsoft SQL Azure Database

SQL Azure provides a relational database service called Microsoft SQL Azure Database.

Because SQL Azure Database uses Tabular Data Stream (TDS), client applications can use the same tools and libraries to access SQL Azure Database as they do to access SQL Server. (TDS is the protocol that clients use to communicate with SQL Server.) So, for example, a Python application that uses Easysoft ODBC driver libraries to access data stored in an on-premises SQL Server database can continue to use the Easysoft ODBC driver to access that data if migrated to a cloud-based SQL Azure database.

Connecting to SQL Azure Database from Linux and UNIX

Easysoft produce an ODBC driver that connects applications on Linux and UNIX platforms to SQL Server 7–2022 databases. Because SQL Azure supports remote client tools and libraries that use TDS (and which must also support Secure Sockets Layer (SSL) encryption), the ODBC driver can also connect Linux and UNIX applications to SQL Azure Database. As the ODBC driver supports both products, you have the option of using the driver to:

Before you begin

Before attempting a connection, make sure you have a valid server name and login name for SQL Azure, and a password for the login name, which is both valid and strong.

To help protect your data, the SQL Azure firewall prevents all connection attempts to your SQL Azure server until you specify which machines have permission. Use the SQL Azure portal to configure the SQL Azure Firewall to allow access from your Linux or UNIX machine. You need to add a rule that allows the client machine on which you install the SQL Server ODBC driver to connect to SQL Azure.

When a machine attempts to connect to SQL Azure over Internet, the SQL Azure firewall checks the originating IP address of the request against the permitted IP address ranges specified in the firewall rules. If you attempt to connect to SQL Azure from a machine whose IP address is not included in a firewall rule, the connection will fail with an error similar to that shown in the following shell session:

$ /usr/local/easysoft/unixodbc/bin/isql.sh -v SQL-AZURE-DATABASE-DSN
[S1000][unixODBC][Easysoft ODBC-SQL Server Driver]'Cannot open server
'xyz12345yzx' requested by the login. Client with IP address
'198.51.100.24' is not allowed to access the server. To enable access,
use the SQL Azure Portal or run sp_set_firewall_rule on the master
database to create a firewall rule for this IP address or address range.
It may take up to five minutes for this change to take effect.'

Setting up an ODBC data source

  1. Download the SQL Azure ODBC driver for your platform.
  2. Install and license the SQL Azure ODBC driver on the Linux or UNIX machine.

    For installation instructions, refer to the SQL Azure ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, or SHLIB_PATH depending on the platform and linker).

  3. Create the following SQL Azure ODBC driver data source in /etc/odbc.ini: 
    [sql-azure-database-dsn]
Driver                  = Easysoft ODBC-SQL Server SSL
Server                  = serverfqdn
ServerName              = serverfqdn
User                    = login
Password                = password
Database                = database
Trusted_Connection      = No
Encrypt                 = Yes
TrustServerCertificate  = No
CertificateFile         = CApath

    where:

    • serverfqdn is the fully qualified domain name (FQDN) of your SQL Azure server.
    • login is a database login name that has permission to access database.
    • server is the first part of the SQL Azure server's FQDN (i.e. the part that precedes the first dot).
    • password is the password for login.
    • database is the name of the SQL Azure database you want to access. If you want to access the master database, you can omit the Database attribute from your ODBC data source.
    • CApath is the path to the directory on the SQL Azure ODBC driver machine that contains the public key certificates for the trusted certificate authorities (CAs).

    For example, this sample ODBC data source connects to the SQL Azure version of the AdventureWorks database, which is served by a Azure server named xyz12345yzx.database.windows.net, as the database login myuser:

    [sql-azure-database-dsn]
Driver                  = Easysoft ODBC-SQL Server SSL
Server                  = xyz12345yzx.database.windows.net
ServerName              = xyz12345yzx.database.windows.net
User                    = myuser
Password                = my5tr0ngp455w0rd
Database                = AdventureWorksLTAZ2008R2
Trusted_Connection      = No
Encrypt                 = Yes
TrustServerCertificate  = No
CertificateFile         = /usr/lib/ssl/certs
  4. Use isql to connect to the new data source. For example: 
    $ cd /usr/local/easysoft/unixODBC/bin
$ ./isql.sh -v sql-azure-database-dsn

Notes

Connection pooling

To reduce the cost incurred by establishing connections (SSL handshake, login process, and so on) to SQL Azure Database over the Internet, Microsoft recommend that you use connection pooling. The unixODBC Driver Manager, which is included in the SQL Azure ODBC driver distribution, provides a connection pooling mechanism. unixODBC connection pooling can speed up connection times for applications that run continuously, but repeatedly close and reopen the same connection.

Please refer to the notes in this Easysoft tutorial for information about unixODBC connection pooling and the types of application that can benefit from connection pooling.

To enable unixODBC connection pooling, you need to:

  1. Add the line Pooling = Yes to the ODBC section of /etc/odbcinst.ini. For example: 
    [ODBC] 
Trace             = No 
Trace File        = /tmp/sql.log 
Pooling           = Yes
  2. Add CPTimeout = seconds to the Easysoft ODBC-SQL Server SSL section in /etc/odbcinst.ini, where seconds is the number of seconds that the Driver Manager will wait before dropping an unused pooled connection. For example: 
    [Easysoft ODBC-SQL Server SSL]
Driver          = /usr/local/easysoft/sqlserver/lib/libessqlsrv_ssl.so
Setup           = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
Threading       = 0
FileUsage       = 1
DontDLClose     = 1
UsageCount      = 1
CPTimeout       = 120

Resources