• The SQL Azure service is only available with TCP port 1433. To access a SQL Azure database from your computer, ensure that your firewall allows outgoing TCP communication on TCP port 1433.
  
• Before you can connect to the SQL Azure server for the first time, you must use the SQL Azure portal to configure the SQL Azure firewall. You will need to create a firewall setting that enables connection attempts from your computer or Windows Azure. For more information, see How to: Configure the SQL Azure Firewall and SQL Azure Firewall.
  

SQL Azure supports only SQL Server Authentication. Users must provide credentials (login and password) every time they connect to SQL Azure. For more information about SQL Server Authentication, see Choosing an Authentication Mode in SQL Server Books Online.

For performance reasons, when a password is reset in SQL Azure, the connection will not be re-authenticated immediately, even if the connection is reset due to connection pooling. This is different from the behavior of on-premise SQL Server. Instead, SQL Azure relies on a re-authentication mechanism to disconnect stale sessions. When a new request is submitted on any connection, if more than 60 minutes has passed since the last re-authentication, a re-authentication is performed. If the password has been changed, the request will fail and the session will disconnect (end).

When managing logins and users in SQL Azure, the following restrictions apply:

• The initial server-level principal login created during server provisioning is similar to the sa login in an instance of SQL Server. (For more information about server provisioning, see SQL Azure Provisioning Model.) Only the password associated with this server-level principal login can be changed. To access the master database, every login must be mapped to a user account in the master database. The database user in the master database associated with this server-level principal login cannot be altered or dropped. Although the database user in the master database associated with this server-level principal login is not a member of the two server roles dbmanager and loginmanager, this database user has all permissions granted with these two roles.
  
• For all logins, US-English is the default language and if you do not specify a database in the connection string, you will be connected to the master database by default.
  
• You must be connected to the master database when executing the CREATE/ALTER/DROP LOGIN and CREATE/DROP DATABASE statements.
  
• When executing the CREATE/ALTER/DROP LOGIN and CREATE/DROP DATABASE statements in an ADO.NET application, using parameterized commands is not allowed. For more information, see Commands and Parameters (ADO.NET).
  
• When executing the CREATE/DROP DATABASE and CREATE/ALTER/DROP LOGIN statements, each of these statements must be the only statement in a SQL batch. Otherwise, an error occurs. The following Transact-SQL checks whether the database exists or not. If it exists, a DROP DATABASE statement is called to remove the database. Because the DROP DATABASE statement is not the only statement in the batch, executing this Transact-SQL will result in an error.
  
  IF EXISTS (SELECT [name]
             FROM   [sys].[databases]
             WHERE  [name] = N'database_name')
       DROP DATABASE [database_name];
  go

• When executing the CREATE USER statement with the FOR/FROM LOGIN option, it must be the only statement in a SQL batch.
  
• When executing the ALTER USER statement with the WITH LOGIN option, it must be the only statement in a SQL batch.
  
• Only the server-level principal login and the members of the dbmanager role have permission to execute the CREATE DATABASE and DROP DATABASE statements.
  
• Only the server-level principal login and the members of the loginmanager role have permission to execute the CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements.
  
• To access the master database, the login must be mapped to a database user in the master database.
  
• In this release, when the owner of a database role tries to add/remove another database user to/from that database role, the following error may occur: "User or role 'Name' does not exist in this database". This error occurs because the user is not visible to the owner. In order to resolve this issue, grant the owner with VIEW DEFINITION permission on the user.
  

For more information, see Managing Databases and Logins in SQL Azure.

SQL Azure provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all SQL Azure customers, your connection to the service may be closed due to the following conditions:

• Excessive resource usage
  
• Long-running queries
  
• Long-running single transactions,
  
• Idle connections
  
• Failover due to server failures
  

When this happens, you will receive one of the following errors:

We recommend that you implement the retry logic in your SQL Azure applications to catch these errors. When an error occurs, re-establish the connection and then re-execute the failed commands or the query.

In this release, SQL Azure provides two database sizes: 1 GB (Web Edition) or 10 GB (Business Edition). If the size of your database reaches its MAXSIZE, you will receive an error code 40544. When this happens, you cannot insert or update data, rebuild indexes, and create new objects, such as tables, stored procedures, views, and functions. However, you can still read and delete data, truncate tables, and drop tables and indexes. If you remove some data to free up your storage space, there can be as much as a fifteen-minute delay before you can insert new data.

Other Issues

Additional requirements for SQL Azure:

• You must always support encryption when connecting to SQL Azure.
  
• Before your computer can access SQL Azure, you may need to create a firewall exception on your computer for TCP port 1433. SQL Azure Firewall for more information.
  
• Only TCP-IP connections are allowed.
  
• Currently SQL Azure supports a maximum of five databases including the master database, so only four user databases can be created. 
  

Features of SQL Server that SQL Azure does not support:

Multiple Active Result Sets (MARS) is not supported. SQL Azure runs single batches on a single connection.

Because some tools implement tabular data stream (TDS) differently, you may need to append the SQL Azure server name to the login in the connection string using the <login>@<server> notation. See Managing Databases and Logins in SQL Azure for more information.

Windows Authentication (integrated security) is not supported. SQL Azure only supports SQL Authentication (user name and password). See Using SQL Authentication in Books Online.

• Certain user names are disallowed due to security reasons. You cannot use names that start with:
  
  • admin
    
  • administrator
    
  • guest
    
  • root
    
  • sa
    
• The SQL Server browser is not supported because SQL Azure does not have dynamic ports, only port 1433.
  

Note:
Maximum allowable durations are subject to change. In this release, idle connections and long running queries or transactions are closed after five minutes.

When writing applications for SQL Azure, you can use the following drivers and libraries:

• .NET Framework Data Provider for SQL Server (System.Data.SqlClient) from the .NET Framework 3.5 Service Pack 1.
  
• SQL Server 2008 Native Client ODBC driver.
  
• SQL Server 2008 Driver for PHP version 1.1.
  

SQL Azure supports tabular data stream (TDS) protocol client version 7.3 or later. Earlier versions of TDS protocol are not supported.

Connecting to SQL Azure by using OLE DB is not supported.

You can use the Configuring ODBC Data Sources to define user and system data sources for SQL Azure. To see the list of the user and system data sources, check the User DSN or System DSN tabs of the ODBC Data Source Administrator dialog box.

When using the data source name (DSN) wizard to define a data source for SQL Azure, make sure to click "With SQL Server authentication using a login ID and password entered by the user" option and select the "Connect to SQL Server to obtain default settings for the additional configuration options". Enter your user name and password to connect to your SQL Azure server as Login ID and Password, then uncheck “Connect to SQL Server to obtain default settings…”. Click the "Change the default database to:", and enter the name of your SQL Azure database even if it does not show up in the list. Note that the wizard lists several languages in the "Change the language of SQL Server system messages to:" list.

In this release, SQL Azure supports only English, so select English as a language. SQL Azure does not support Mirror Server or Attach Database, so leave those items empty. Finally, the Test Connection button may result in an error that "master.dbo.syscharsets" is not supported. Ignore this error, and save the DSN and use it.

SQL Azure does not support distributed transactions, which are transactions that affect several resources. For more information, see Distributed Transactions (ADO.NET). Starting with the .NET Framework version 2.0, applications that use the System.Data.SqlClient class to perform database operations in the context of a System.Transactions transaction may cause the transaction to be automatically promoted to a distributed transaction.

Transaction promotion occurs when you open multiple connections to different servers or databases within a TransactionScope, or when you enlist multiple connections in a System.Transactions object by using the EnlistTransaction method. Transaction promotion also occurs when you open multiple concurrent connections to the same server and database either within the same TransactionScope or by using the EnlistTransaction method. Starting with the .NET Framework version 3.5, the transaction will not be promoted if the connection strings for the concurrent connections are exactly the same. For more information about transactions and avoiding transaction promotion, see System.Transactions Integration with SQL Server (ADO.NET).

Note:
SQL Azure may not preserve the uncommitted timestamp values of the current database (DBTS) across failovers.

For this version of SQL Azure, you can transfer data to SQL Azure by using the following:

• SQL Server 2008 Integration Services (SSIS)
  
• The bulk copy utility (BCP.exe)
  
• System.Data.SqlClient.SqlBulkCopy class
  
• Scripts that use INSERT statements to load data into the database
  

SQL Azure does not support:

• The RESTORE statement
  
• Attaching a database to the SQL Azure server.
  

For more information about SSIS, see SQL Server Integration Services.

SQL Azure does not support tables without clustered indices. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

The following Transact-SQL creates a new table, creates a clustered index on the table, and then inserts data into the table. Performing the insert without creating the clustered index will result in an error.

CREATE TABLE Table1 (Col1 int, Col2 char(20))
CREATE CLUSTERED INDEX Table1_Index ON Table1 (Col1)
INSERT INTO Table1 VALUES (1, 'string1')

The default database collation for SQL Azure is SQL_LATIN1_GENERAL_CP1_CI_AS, where LATIN1_GENERAL is English (United States), CP1 is code page 1252, CI is case-insensitive, and AS is accent-sensitive.

When using on-premise SQL Server, you can set collations at server, database, column, and expression levels. With SQL Azure, you cannot set the collation at the server or database level. To use the non-default collation with SQL Azure, set the collation at the column level or the expression level. For more information on how to set the collation, see COLLATE (Transact-SQL) in SQL Server Books Online.

Concepts