SQL Azure: Securing SQL Azure

Security is just as essential to hosted solutions, although the tools and tactics are certainly different.

Joshua Hoffman

Cloud computing has recently become fundamental to the nature of IT infrastructure. While on-premises computing certainly maintains an important role, hosted solutions are gaining prominence thanks to their scalability, elasticity and cost savings that have become so critical to agile organizations. This is particularly true when it comes to data management.

SQL Azure is a good example of such a cloud platform. SQL Azure is a powerful, familiar infrastructure for storing, managing and analyzing data. It also conveys the benefits of cloud computing. Shared, hosted infrastructure helps reduce both direct and indirect costs, the pay-as-you-go model helps achieve greater efficiency, and high availability and fault tolerance are built in.

Despite the numerous benefits of cloud computing, migrating your data to a cloud-based solution like SQL Azure introduces changes in the way you approach IT management concepts like security. The security model may be different, but there are still many factors you must consider to ensure your data is as secure as possible, including methods of access control, authentication and encryption.

Get Started with SQL Azure

If you’re new to SQL Azure, getting started is simple. This is especially true if you’re already familiar with traditional SQL Server management tools like the SQL Server Management Studio (which, beginning with the SQL Server 2008 R2 version, you can also use to manage SQL Azure instances).

The first step is to set up an account for the Windows Azure platform by visiting the Microsoft Online Services Customer Portal. This gives you access to Windows Azure services including Windows Azure, SQL Azure and Windows Azure AppFabric (which provides a means for developers to connect applications and services across organizational and network boundaries).

Once you’ve established an account, you can access the SQL Azure control panel by visiting sql.azure.com. Click New Database Server to create a new instance of SQL Azure.

Network Access Control

To protect your data, the SQL Azure Firewall prevents all access to your databases until you grant access from specific computers. Access via the SQL Azure Firewall is based on originating IP address. You can manage this via the Windows Azure Platform Management Portal (see Figure 1) or directly in the master database with the stored procedures.

Configure the SQL Azure Firewall rules by following these steps:

  1. Sign in to the Windows Azure Platform Management Portal.
  2. From the left pane, click Database.
  3. From the left pane, expand your subscription and click the SQL Azure server for which you want to configure firewall rules.
  4. Click Firewall Rules to list the rules.
  5. Click Add to create a new firewall rule. To allow other Windows services to access this SQL Azure server, select “Allow other Windows Azure services to access this server.” It creates a rule with the IP range “0.0.0.0 – 0.0.0.0.” You can use the same process to update and delete rules.

Note: Every firewall rule must have a unique name (which is not case-sensitive).

Figure 1 Configuring a firewall rule in SQL Azure.

For more details on configuring the firewall rules via SQL stored procedures, see Changing the Firewall Rules using T-SQL.

The SQL Azure service is available over TCP port 1433. Depending on your existing firewall policies, you may also need to configure client computers to allow outbound access over that port. See Configuring the Client-Side Firewall for more details on this process.

SQL Authentication

The authentication method in SQL Azure is based on traditional SQL Server authentication. It doesn’t support Windows authentication (integrated security). You must provide credentials every time you connect to SQL Azure.

When you create a new database server in SQL Azure, you’re promoted to be able to create a server-level principal login and password. This is equivalent to the SQL Server system administrator account. You can create additional logins using T-SQL, via either the Database Manager for SQL Azure or the SQL Server Management Studio 2008 R2.

You create and drop logins via the master database, using the CREATE LOGIN command. For example, connect to your master database using either the Database Manager for SQL Azure or the SQL Server Management Studio, and execute the following query:

--create a login named "testlogin"
CREATE LOGIN testlogin WITH password='Passw0rd!';

You can query for existing logins using the command shown in Figure 2.

Figure 2 Querying SQL Server logins using the Database Manager for SQL Azure.

Once you create your logins, you can create database-level users associated with those logins, and assign specific database-level permissions to each user. To create a user for a specific database, connect to the appropriate database and execute the following command:

CREATE USER testuser FROM LOGIN testlogin;

You can then assign permissions using the following command:

EXEC sp_addrolemember 'db_datareader', 'testuser';

For a full list of roles, see Database-level roles. Note that when you execute the CREATE/ALTER/DROP LOGIN statements or CREATE/ALTER/DROP USER statements (with the FOR/FROM LOGIN option), each of these statements must be the only statement in the T-SQL batch. Otherwise, there will be an error.

Connection Encryption

SQL Azure transfers data using tabular data stream (TDS) over an SSL connection. SQL Azure doesn’t support unencrypted connections, and has a signed certificate issued by a Certificate Authority. These factors all help ensure secure data transmission and can help prevent man-in-the-middle attacks.

The encryption handshake happens in the PRELOGIN stream of the TDS protocol. Any client communicating with SQL Azure will require this, including SQL Server Management Studio and applications via ADO.NET.

To encrypt connections from SQL Server Management Studio, use the following steps:

  1. Open SQL Server Management Studio.
  2. From Object Explorer, click Connect, then click Database Engine.
  3. From Connect to Server, click Connection Properties.
  4. Select Encrypt connection (see Figure 3).

Figure 3 Encrypting a connection to SQL Azure in the SQL Server Management Studio.

To validate certificates with ADO.NET application code, set Encrypt=True and TrustServerCertificate=False in the database connection string.

Data Encryption

SQL Azure does not currently support the standard data-encryption mechanisms that SQL Server supports. These include transparent data encryption, asymmetric keys, symmetric keys, and Transact-SQL functions such as ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE, CREATE/ALTER/DROP DATABASE ENCRYPTION KEY or CREATE/ALTER/DROP MASTER KEY.

With SQL Azure, you need to encrypt or decrypt at the application level. In other words, your application is responsible for data encryption and decryption when sending or retrieving data from SQL Azure.

As you begin hosting certain applications and data in the cloud, you have to remain cognizant of your data security. You must still be involved in your cloud-based security architecture. The need for security is ever-present—only the techniques change with the cloud-computing platform. For additional resources on this topic, be sure to see the article SQL Azure Security Guidelines and Limitations.

By following best practices for account and connection management, you can ensure that you realize the full benefits of migrating your data to the cloud. You don’t have to compromise the safety and security of your critical business information.

Joshua Hoffman

Joshua Hoffman is the former editor in chief of TechNet Magazine*. He’s now an independent author and consultant, advising clients on technology and audience-oriented marketing. Hoffman also serves as editor in chief of ResearchAccess.com, a site devoted to growing and enriching the market research community. He lives in New York City.*