Site navigation
Page navigation
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:
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.
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 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.'
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).
/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
$ cd /usr/local/easysoft/unixODBC/bin $ ./isql.sh -v sql-azure-database-dsn
Easysoft ODBC-SQL Server SSL), which can encrypt data transmitted across the network.
If you attempt to connect to SQL Azure with the non-SSL version of the SQL Azure ODBC driver (Easysoft ODBC-SQL Server), the SQL Azure Gateway, which performs the SSL handshake with the driver, will reject the connection, and you will get the error:
SSL requested but not supported by this driver
Note The SQL Azure ODBC driver distribution includes both versions of the driver.
PRELOGIN message from SQL Azure.
To request encryption, ensure that this line is present in your ODBC data source:
Encrypt = Yes
Add this line to your ODBC data source:
TrustServerCertificate = No
When TrustServerCertificate is set to No, the SQL Azure ODBC driver will terminate the connection process if it is unable to verify the SSL certificate it has been presented with. This setting ensures that the SQL Azure ODBC driver will only proceed with the connection if presented with an SSL certificate that has been signed by a trusted root CA, thereby guaranteeing the identity of the remote machine. Because the SQL Azure ODBC driver needs to verify the certificate, you need to tell the driver where to find the public key certificates for the trusted root CAs. To do this, use the CertificateFile attribute. For example:
CertificateFile = /usr/lib/ssl/certs
USE Transact-SQL (T-SQL) statement to be executed. You cannot therefore change the database context after connecting by executing USE database. You need to specify the target database in the ODBC data source. For example, Database = AdventureWorksLTAZ2008R2.
SQL Azure Database differs from SQL Server in this respect because a SQL Azure user's databases may not all be located on the same physical server.
If you do not specify a database in the ODBC data source, you will connect to the master database.
With SQL Server Authentication, database logins, which are not based on Windows user accounts, are created and stored in SQL Azure.
You specify the SQL Server Azure database login and password in the ODBC data source.
If you are connecting with a Azure Active Directory account user name and password, add this line to your ODBC data source:
Authentication=ActiveDirectoryPassword
Include the Azure AD domain name in the user name. For example:
User=myuser@mydomain.onmicrosoft.com
Here's a complete sample ODBC data source that connects through an Azure Active Directory account:
[sql-azure-database-dsn] Driver=Easysoft ODBC-SQL Server SSL Server=xyz12345yzx.database.windows.net Servername=xyz12345yzx.database.windows.net User==myuser@mydomain.onmicrosoft.com Password=mypassword! Database=mydb Authentication=ActiveDirectoryPassword
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:
Pooling = Yes to the ODBC section of /etc/odbcinst.ini. For example:
[ODBC] Trace = No Trace File = /tmp/sql.log Pooling = Yes
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