Windows Azure SQL Database Security Scenarios and Tips

  • Article

 

I’ve summarized Windows Azure SQL Database security topic in my previous post Secure Your Windows Azure SQL Database.

 

In this article, I’ll show you interesting scenarios that you can use with your applications or services. I’ll share you how you can apply the following scenarios.

1. Read only user/application databases : This scenario is useful for creating a read-only data repositories. Your applications can only read the data but will not be able to modify it.

Use this scenario if you don’t want your users to be able to modify the data, even after losing their credentials.

a. Create your server and database

b. Create a new login in master database

c. Create a user from previously created login

d. Grant the user with db_datareader role (sp_addrolemember)

db_datareader role grants your users read-only access to the data. User who only has db_datareader role can’t modify the data.

Scenarios : Reporting applications, publicly distributed applications (risk of decompiling or other kind of tracing), shared databases supporting multiple applications, etc.

 

2. Write only user/application databases : This scenario is useful for creating a raw data collecting database.

Use this scenario if you don’t want your users to be able to read existing data

a. Create your server and database

b. Create a new login in master database

c. Create a user from previously created login

d. Grant the user with db_datawriter role (sp_addrolemember)

 

db_datawriter role grants your users to insert new records but doesn’t grant to read or update the data.

Users can insert new records but can’t update or view existing records. Basically they don’t have SELECT permissions.

So you can collect the raw data, process it and show only processed data using another user who has read-only access to processed data.

Scenarios : Storing game scores, collecting auction bids (insert only, no one can read the data), message or chat queue, order forms, contact forms, collecting counters (performance, production), etc.

3. Combination of read-only and write-only databases : You can create two database. One of reading the data and the other for collecting the data.

a. Create your server and two database

b. Create a new login in master database

c. Create a user from previously created login

d. Grant the user with db_datawriter role (sp_addrolemember) in data collection database

e. Grant the user with db_datareader role (sp_addrolemember) in reporting database

Grant db_datareader role to one database and db_datawriter role to other database.

You can collect the data into data collection database, process it and insert it into reporting database.

This kind of scenario is really useful for all kind of applications directly accessing to your database.

4. Tips :

  • Do not use a wide IP Range in your firewall rules. Try to minimize directly accessing IPs to your database.
  • Remove any unnecessary firewall rule immediately.
  • If you have a scenario that needs access to your database with Dynamic IP please check my previous article Windows Azure SQL Database : How to manage your firewall rules with Windows Azure SQL Database Management API (REST)to see Create/Update a Server-Level Firewall with IP Detect. This REST API updates your firewall rule with the requestor IP. Very useful for administrative purposes.
  • Create a new Windows Azure SQL Database Server for new applications. Only use existing server for applications that has common data.
  • Always create a new login and user for each application.
  • It’s good to create a new user for each administrator.
  • Server roles are really helpful, use them whenever you can.
  • If you need to create a mobile application, always try to position Windows Azure Mobile Services. It is a really useful cloud backend. In this case you can manage the security and connection easily.
  • Try to keep your databases and your applications close in terms of networking to decrease latency. If you’ll need lots of access to your database consider positioning a service layer to increase performance.
  • Backup your data and schema regularly.

 

Start using your free Windows Azure trial Windows Azure Free Trial