Secure Your Windows Azure SQL Database

  • Article

 

Securing your data is important and you have lots of options in Windows Azure SQL Database according to your needs.

 

If you read my previous article Windows Azure SQL Database - How to create Windows Azure SQL Database you know basics of creating your Windows Azure SQL Database. In this article, I’ll focus on securing your database and data.

 

Windows Azure SQL Database is a relational database-as-a-service. So, it’s obvious that you are not managing your database server physically, you can’t touch it but it’s still secure and you can always manage security related features according to your requirements.

First of all, I want to say that there are two security layers in Windows Azure SQL Database service : Network Layer and Database Layer.

 

1. Network Layer Security: You can configure IP based firewall rules to grant or deny connections to your service. There are two types of rules : Server-Level Firewall Rules and Database-Level Firewall Rules.

 

image

 

a. Server-Level Firewall Rules : These rules are effective for all your databases in your database server. You can allow IP addresses allowed to connect to your server. It is also possible to allow your Windows Azure cloud services to access to your database server. These rules are stored in master database.

You can manage these rules using :

           

b. Database-Level Firewall Rules : Database-Level Firewall rules enable clients to access your databases individually. These rules are created per database and stored in the database itself including master database.

You can limit the connectivity of an IP to specific database by creating a Database-Level Firewall rule (do not add same IP to Server-Level Firewall Rules, in this case IP can reach to all of the databases in your server).

You can manage these rules using :

 

2. Database Layer Security

In order to increase the security of your data you can create multiple logins and multiple users with different roles.

You can create db-owners, read-only users and write-only users according to your needs.

Read-only and write-only users are really interesting if you can’t control the distribution of your app (Freely distributed apps to collect data, Store distributed apps, etc).

In order to do so you need to create a login in master database,

then users from logins in databases itself.

 

Run this query in your master database to create a login :

 CREATE LOGIN login1_master WITH password='PASSWORD';

Then create user( s ) in the databases itself you want to grant access :

 CREATE USER login1User_db FROM LOGIN login1_master;

Now, you can grant database roles to this user :

  

 --add

 exec sp_addrolemember 'db_owner', 'login1User_db';  --db-owner
exec sp_addrolemember 'db_datareader', 'login1User_db'; --read-only
exec sp_addrolemember 'db_datawriter', 'login1User_db';  --write-only
exec sp_addrolemember 'db_ddladmin', 'login1User_db'; --ddl admin

 --remove

 exec sp_droprolemember 'db_owner', 'login1User_db'; --db-owner
exec sp_droprolemember 'db_datareader', 'login1User_db'; --read-only
exec sp_droprolemember 'db_datawriter', 'login1User_db'; --write-only
exec sp_droprolemember 'db_ddladmin', 'login1User_db'; --ddl admin

 

All your Server-Level Firewall Rules and Server-Level Logins has been stored in master database. Database-Level Firewall Rules and Users has been stored in databases itself.

Please check my article Windows Azure SQL Database Security Scenarios and Tips to see how you can apply this information in your scenarios.

 

You can create your free trial subscription and test database-as-a-service. Your free trial includes 1 GB of free Windows Azure SQL Database. Test it yourself.