Securing SQL Server data in the cloud: a few pointers
Denny Cherry, Contributor
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.
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
This was first published in May 2011
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.
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.
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation