Export (0) Print
Expand All
6 out of 7 rated this helpful - Rate this topic

Security Guidelines and Limitations (Windows Azure SQL Database)

This topic describes the Microsoft Windows Azure SQL Database guidelines and limitations related to security. Consider these points when managing the security of your SQL Databases:

Firewall

The Windows Azure SQL Database service is only available through TCP port 1433. To access a SQL Database from your computer, ensure that your firewall allows outgoing TCP communication on TCP port 1433.

Before you can connect to the SQL Database server for the first time, you must use the Windows Azure Platform Management Portal to configure the SQL Database firewall. You will need to create a server-level firewall setting that enables connection attempts from your computer or Windows Azure to SQL Database server. Further, if you want to control access to certain databases in your SQL Database server, create database-level firewall rules for the respective databases. For more information, see Windows Azure SQL Database Firewall.

Encryption and Certificate Validation

All communications between Windows Azure SQL Database and your application require encryption (SSL) at all times. If your client application does not validate certificates upon connection, your connection to Windows Azure SQL Database is susceptible to "man in the middle" attacks. 

To validate certificates with application code or tools, explicitly request an encrypted connection and do not trust the server certificates. If your application code or tools do not request an encrypted connection, they will still receive encrypted connections. However, they may not validate the server certificates and thus will be susceptible to "man in the middle" attacks.

To validate certificates with ADO.NET application code, set Encrypt=True and TrustServerCertificate=False in the database connection string. For more information, see How to: Connect to Windows Azure SQL Database Using ADO.NET.

SQL Server Management Studio also supports certificate validation. In the Connect to Server dialog box, click Encrypt connection on the Connection Properties tab. 

noteNote
SQL Server Management Studio does not support Windows Azure SQL Database in versions prior to SQL Server 2008 R2.

Although SQLCMD supported Windows Azure SQL Database starting with SQL Server 2008, it does not support certificate validation in versions prior to SQL Server 2008 R2. To validate certificates with SQLCMD starting in SQL Server 2008 R2, use the -N command-line option and do not use the -C option. By using the -N option, SQLCMD requests an encrypted connection. By not using the -C option, SQLCMD does not implicitly trust the server certificate and is forced to validate the certificate.

For supplementary technical information, see Windows Azure SQL Database Connection Security article at the TechNet Wiki site.

Authentication

Windows Azure SQL Database supports only SQL Server Authentication. Windows Authentication (integrated security) is not supported. Users must provide credentials (login and password) every time they connect to Windows Azure SQL Database. 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 Windows Azure SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. This is different from the behavior of on-premise SQL Server. If the password has been changed, the request will fail and the session will disconnect (end). To authenticate using the new password, use the KILL command to terminate the session and reconnect. For more information on Kill, see KILL (Windows Azure SQL Database).

Logins and Users

When managing logins and users in Windows Azure SQL Database, there are restrictions.

  • For the server-level principal login, the following restrictions apply:

    • The database user in the master database corresponding to the server-level principal login cannot be altered or dropped.

    • Although the server-level principal login is not a member of the two database roles dbmanager and loginmanager in the master database, it has all permissions granted to these two roles.

noteNote
This login is created during server provisioning and is similar to the sa login in an instance of SQL Server. For more information about server provisioning, see Windows Azure SQL Database Provisioning Model.

For all logins, the following restrictions apply:

  • US-English is the default language.

  • To access the master database, every login must be mapped to a user account in the master database.

  • 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/ALTER/DROP DATABASE statements.

  • When executing the CREATE/ALTER/DROP LOGIN and CREATE/ALTER/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/ALTER/DROP DATABASE and CREATE/ALTER/DROP LOGIN statements, each of these statements must be the only statement in a Transact-SQL batch. Otherwise, an error occurs. For example, the following Transact-SQL checks whether the database exists. 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 Transact-SQL batch.

  • When executing the ALTER USER statement with the WITH LOGIN option, it must be the only statement in a Transact-SQL batch.

  • Only the server-level principal login and the members of the dbmanager database role in the master database have permission to execute the CREATE DATABASE and DROP DATABASE statements.

  • Only the server-level principal login and the members of the loginmanager database role in the master database have permission to execute the CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements.

  • 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. To resolve this issue, grant the role owner the VIEW DEFINITION permission on the user.

Best Practices

Consider these points to make your SQL Database applications less vulnerable to security threats:

  • Always use the latest updates: When connecting to your SQL Database, always use the most current version of tools and libraries to prevent security vulnerabilities. For more information about which tools and libraries are supported, see General Guidelines and Limitations (Windows Azure SQL Database).

  • Block inbound connections on TCP port 1433: Only outbound connections on TCP port 1433 are needed for applications to communicate with Windows Azure SQL Database. If inbound communications are not needed by any other applications on that computer, ensure that your firewall continues to block inbound connections on TCP port 1433.

  • Prevent injection vulnerabilities: To make sure that your applications do not have SQL injection vulnerabilities, use parameterized queries where possible. Also, be sure to review code thoroughly and run a penetration test before deploying your application.

See Also

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.