Problem solve Get help with specific problems with your technologies, process and projects.

Securing SQL Server data in the cloud: a few pointers

Few concerns swirling around Microsoft cloud computing are more consuming than securing SQL data in the cloud. Can it be done? Certainly, but it’s not to be taken lightly.

Moving data into the cloud is a big talking point these days, and it’s no wonder. You can spin up a new SQL Server instance within Microsoft's SQL Azure cloud database in just a few minutes and quickly begin loading data for a new transactional application or for reporting or other data analysis uses. 

But migrating data to the cloud, be it in SQL Azure or Amazon.com’s Elastic Compute Cloud (EC2), needs to be done carefully. Securing SQL Server data in the cloud is hugely important -- you don’t want to expose client data to people who shouldn’t have access to it.

Securing data in SQL Azure

Azure’s firewall. The first thing to remember when getting started with SQL Azure is to not open the database up to the outside world. By default, the SQL Azure database is accessible only to Microsoft’s internal Azure servers. To connect directly to your SQL Azure instance, configure your SQL Azure firewall to allow your home or office IP addresses to connect. Begin at the Windows Azure management portal. Once you are logged on, you should see your SQL Azure subscriptions under the Subscriptions tab, as shown in Figure 1. (Your service name will be different, as will your subscription list.)

Figure 1. This screenshot shows Windows Azure subscriptions and firewall rules.

Figure 1. This screenshot shows Windows Azure subscriptions and firewall rules.

Select the server -- in my case “fz9fnjspok” -- from the Subscriptions menu to see the Firewall Rules button, which is on the right-hand side in Figure 1. Click on it and you can view and edit the firewall rules for the SQL Azure instance.

As shown in Figure 2, this instance only has two firewall rules. Best practices say that you want as few firewall rules as possible. If you need to add connectivity later because you are, say, at a conference or on vacation, add the rule, and then remove it when you are done. This will help prevent unauthorized people from breaking into the SQL Azure instance. Leaving a firewall down for even a short period of time can be very dangerous. Just read about the recent problems security firm Barracuda had.

Figure 2. This screen shot shows firewall rules on this instance of SQL Azure.

Figure 2. This screen shot shows firewall rules on this instance of SQL Azure.

Object-level permissions. In addition to securing your SQL Azure instance’s firewall you will also want to follow the best practice of only granting users, or logins, the minimum rights that they need to perform their jobs. The current client tools don’t exactly make this easy to do -- there is no user interface for modifying rights in SQL Server Management Studio when connected to a SQL Azure database. However, SQL Azure does support database roles as well as object-level permissions, so configure the account that an application uses to connect to the SQL Azure database with the lowest possible number of permissions. That’s so that users cannot access objects they don’t need, and this is exactly what you’d do in a traditional SQL Server database instance.

SQL injection and Azure. Applications hosted by Azure are just as susceptible to SQL injection attacks as a traditional Web application is. The data being returned from the end users browser should never be trusted, and all database calls should be made to be parameterized (instead of doing string concatenation within the application). If you are using an object-relational mapping (ORM)-like entity framework (EF) to write the application code, the ORM will parameterize the database calls for you. However, if you aren’t using an ORM, the application calls to the database must be parameterized to be safe. All it takes is a single unparameterized application call to allow an attacker into your database. (Read about MySQL.com’s SQL injection issue.)

Securing SQL Server data in Amazon’s EC2

A SQL Server instance running under Amazon’s EC2 is very easy to secure; basically, it is a traditional virtual machine running a normal SQL Server instance. Amazon has a firewall that should be configured to disallow access to the server, allowing it just when needed. Within the SQL instance, all the normal best practices need to be followed. For example, don’t allow application accounts to be members of the sysadmin fixed server role or the db_owner fixed database role.

The application accounts should only have the minimum rights needed to carry out basic functions, such as executing the stored procedures that make up the application or querying the tables directly if you are using an ORM. If tables don’t need to have data deleted by the application, make sure that the account the application uses doesn’t have delete rights to the table within the database. And just as with SQL Azure, if you are using an ORM-like EF to write the application code, the ORM will parameterize the database calls for you. Also as with Azure, if you aren’t using an ORM, make sure that all application calls to the database are parameterized as safe.

Admin access to the servers. In both EC2 and Azure there currently isn’t much you can do to keep the administration support staff from logging into your database and looking at your data. For this reason, it is highly recommended that you encrypt any sensitive data contained within your cloud database. This way, if an admin were to look within your database, the data he sees would be worthless.

That said, the odds of an admin or other support staffer looking to steal data from your application’s database are slim to none. If you aren’t comfortable with encrypting data, then that data probably shouldn’t be stored in the cloud to begin with.

Conclusion

Storing data in the cloud is a perfectly safe operation, provided that the correct precautions are put into place. From a DBA point of view this is all normal stuff. The issues with securing SQL Server data in the cloud will come from end users or business unit people putting data within the cloud without understanding how to secure it properly.

ABOUT THE AUTHOR
Denny Cherry has more than a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation. Cherry specializes in system architecture, performance tuning, replication and troubleshooting. Check out his blog at SQL Server with Mr. Denny.

This was last published in May 2011

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close