Using SQL Server Client APIs with SQL Azure Version 1.0

This post describes some of the issues that the application developer should be aware of when writing ODBC, ADO.NET SQLClient, BCP, and PHP code that connects to SQL Azure. There is no intention here to replace the SQL Azure documentation available on MSDN (https://msdn.microsoft.com/en-us/library/ee336279.aspx). Rather, the intent here is to provide a quick discussion of the primary issues impacting applications ported to or written for SQL Azure vs. SQL Server. Developers should reference the MSDN documentation for more detailed description of SQL Azure functionality.

 

Using supported SQL Server client APIs (ODBC, BCP, ADO.NET SQLClient, PHP) with SQL Azure is relatively easy, however, SQL Azure, while very similar, is not SQL Server. Because of this there are some pitfalls that need to be avoided to have the best experience when writing an application that connects to SQL Azure.

Supported components

The first thing to ensure is that you are using a supported component to connect to SQL Azure.

· Microsoft SQL Server Native Client 10.0 ODBC Driver or newer. The SQL Server 2008 R2 version of the SQL Server Native Client ODBC Driver is preferred due to some minor updates to work better with SQL Azure. This is a free download.

· SQL Server 2008 BCP.EXE or newer. The caution here is that the queryout option is not fully supported for SQL Server 2008 RTM. Queryout is fully supported in the SQL Server 2008 R2 release via the addition of the "-d" database parameter.

· ADO.NET 3.5 SqlClient or newer.

Any other components are not currently supported for connecting to SQL Azure. In particular it should be noted that OLEDB connections are not currently supported from any OLEDB provider, even SQL Server 2008 Native Client OLEDB provider. ODBC connections using the on-the-box ODBC driver “SQL Server” are not currently supported. 

 

Also, using the OLEDB Provider for ODBC Drivers (MSDASQL) cannot be supported with SQL Azure because the only ODBC driver supported for SQL Azure is the SQL Server 2008 Native Client ODBC driver or newer, and SQL Server Native Client is not supported with MSDASQL (https://msdn.microsoft.com/en-us/library/ms131035.aspx).  

Connecting

Before attempting a connection, make sure you have a valid server name, user ID, and password for SQL Azure. On the SQL Azure portal, set the firewall rules to allow your client IP address to connect to SQL Azure. Additionally, in some corporate environments, you might need to install Microsoft Firewall Client for ISA Server or Forefront TMG client on the client machines or connections will not succeed. And in those environments, using IIS might require the default IIS settings for Application Pool Identity to be changed. Here is how that setting is changed:

 

Open IIS Manager in Admin Tools, select Application Pools, Pick the pool, Advanced Setting and under Process Model change Identity from BuiltInAccount\ApplicationPoolIdentity to Custom account domain account. Restart IIS.

 

SQL Azure supports only TCP/IP connections using port 1433, which is the default for most SQL Server APIs and so this generally does not need any special handling by the application developer. But it is good to be aware of this in case you are expecting other protocol choices to work, such as named pipes or using other ports. Additionally, most client APIs do not send the server name when connecting, thus the server name should be included as part of the user ID. Currently the only exception to this is .NET 4.0 which does send the server name. ODBC, BCP, PHP, and .NET 3.5 all require the server name to be sent as part of the user ID. This may change in a future release. Connection strings are all similar for each API stack and require the server name, user ID, password, and optionally the database. Trusted connections (also known as Integrated Security or Windows Authentication) are not supported.

SQLCmd example:

 

sqlcmd -S o8uyxb64fe.ctp.database.windows.net -U <MyUserID>@o8uyxb64fe -P <MyPassword>

 

Note the required server name (in yellow) included as part of the user ID.

Creating and Connecting to a Database

In the above example the database was not specified. In that case the default database “master” is used. You must connect to the master database to create databases or drop databases.

Since the “USE database” command is not supported, you must disconnect after creating a database and reconnect directly to the user database. In this example we connect directly to the Northwind database.

 

sqlcmd -S o8uyxb64fe.ctp.database.windows.net -U <MyUserID>@o8uyxb64fe -P <MyPassword> -d Northwind

 

It should be noted that the set of databases viewable by the user is different in a user database versus master database. In master, you can see all of your databases for your server, but in a user database only the current user database and master. 

Creating tables

Table creation is restricted to user databases; you cannot create tables in the master database. Table creation is substantially the same as on SQL Server, with the exception that certain data types are not supported, for example CLR User Defined Types, Filestream, and row guid types.

Also, it should be mentioned that SQL Azure requires a clustered index or primary key (which uses a clustered index by default) before data can be inserted into a table. For this reason, a clustered index or primary key must always be created for any table intended to contain data.

Example:

Create table employees(EmployeeID int primary key, EmployeeName varchar(30))

Or

Create table employees(EmployeeID int, EmployeeName varchar(30))

Create clustered index empIndex on employees(EmployeeID)

Unsupported features

Unsupported features are features that SQL Server supports and are not deprecated but SQL Azure does not support. Applications that require this functionality cannot work against SQL Azure unless they can be rewritten in such a manner that the unsupported features are not required. The main unsupported features that impact applications are:

· “USE database” is not supported

· 3-part names are not supported unless the database being referenced is the current database.

· Creating or dropping a database when not in the master database

· Creating or dropping a user when not in the master database.

· Table creation in the master database.

· Insertion of data into a table without a clustered index.

· Trusted connections

· CLR UDTs

· XML schema collections

· Distributed transactions (local transactions are supported)

· AttachDB

· Certain stored procedures, for example sp_who and undocumented stored procedures used to support OLEDB schema rowsets.

Deprecated functionality

Some features are deprecated in SQL Server and should not be used in new application development. In most cases SQL Azure does not support deprecated SQL Server features. Some examples of these are:

· System tables such as syslanguages. Use the new system views instead. See https://msdn.microsoft.com/en-us/library/ms187997.aspx for a mapping to the new system views.

· Numbered stored procedures

· “select into” syntax

· “COMPUTE BY” clause

· Two-part drop index statements

 

See https://msdn.microsoft.com/en-us/library/ee336281.aspx for more detailed Transact-SQL support.

Connection behavior

SQL Azure connections will behave somewhat differently than SQL Server connections. They can be dropped for a number of reasons:

· Excessive resource usage

· Long-running queries

· Long-running single transactions

· Idle connections

· Failover due to server failures

For this reason it’s best to avoid caching connections for later use. A better strategy is to open the connection when needed, use it, and then close it. Additionally, it is recommended that applications provide retry/reconnect logic to recover from dropped connections.

Latency

Latency may be a concern when using SQL Azure unless the SQL Azure server is located near the application (“code near” scenario), such as with Windows Azure and a co-located SQL Azure. Making many round trips to the server can introduce noticeable delay in an application that previously performed adequately when running against a SQL Server on the intranet. Ensure the application connects to the nearest SQL Azure server and that round trips are minimized.

 

SUMMARY

ü Use only supported client components to connect

ü Connect using TCP/IP and port 1433

ü Use SQL Authentication only, not Trusted Connections/Integrated Authentication/Windows Authentication.

ü Specify the server name in the user ID.

ü Ensure the firewall rules in the SQL Azure portal permit your IP address.

ü Install Microsoft Firewall Client for ISA Server if needed.

ü Change the IIS Application Pool Identity if needed.

ü Connect to the master database to create or drop databases

ü Create a user database for your application since tables cannot be created in master

ü Disconnect and reconnect to change databases

ü Create a clustered index or primary key on a table before inserting data.

ü Remove any unsupported or deprecated features from the application.

ü Avoid caching connections. Open the connection, use it, and then close it.

ü Avoid making numerous round trips to the server.

ü Implement retry logic to recover from dropped connections.     

  

 Steve Hale (Microsoft) authored this article.

Comments

• Anonymous
  February 15, 2010
  Steve, Great post about API options.  Just one thing I noticed; you reference system views being used against SQL Azure.  I have been told by the team that DMVs and system views are not currently supported.  Do you have updated information for us? :) Thanks, Whitney

• Anonymous
  February 16, 2010
  Thanks Whitney.  Currently DMVs are not supported, but some system views are indeed supported.  Please refer to http://msdn.microsoft.com/en-us/library/ee336238.aspx for more detailed information. Here is a summary: System Views (SQL Azure Database) Catalog Views Partial Compatibility Views Partial Database Mail Views No Data Collector Views No Policy-Based Management Views No Dynamic Management Views and Functions No Information Schema Views Yes Replication Views No Steve

• Anonymous
  May 23, 2011
  Nice artical. Helped me to resolve the OLEDB issue.. Thanks. Is there any other way to connect to SQL azure vis ssms apart from Firewall settings?

• Anonymous
  May 23, 2011
  Nice article. Helped me to resolve the OLEDB issue.. Thanks. Is there any other way to connect to SQL azure vis ssms apart from Firewall settings?

• Anonymous
  June 16, 2011
  Sorry, not quite sure what you mean.  The Firewall settings on the client computer must let port 1433 through to connect to SQL Azure, and any corporate Firewall that may be between the computer and the internet may require TMG Firewall Client running on the computer to authenticate traffic so it can pass through the corporate Firewall.  This is true for any application, not just SSMS.

• Anonymous
  June 16, 2011
  Sorry, not quite sure what you mean.  The Firewall settings on the client computer must let port 1433 through to connect to SQL Azure, and any corporate Firewall that may be between the computer and the internet may require TMG Firewall Client running on the computer to authenticate traffic so it can pass through the corporate Firewall.  This is true for any application, not just SSMS.