Getting Started With SQL Azure series:

1. What is SQL Azure? 2. Creating First SQL Azure Database 3. Provisioning and Billing Model 4. SQL Azure Architecture 5. SQL Azure's security model 6. Migrating Databases to SQL Azure 7. Administration Tasks - I 8. Administration Tasks - II 9. Developing SQL Azure applications 10A. Getting started with SQL Azure reporting and SQL Azure Data SYNC 10B Conclusion

Now in this article, we are going to explore the security model of SQL Azure. Following are the topics that we are going to cover: 1. SQL Azure Firewall 2. Authentication 3. Authorization [Logins and Users] 4. Encryption and SQL Azure

First is the SQL Azure Firewall.

This is a new feature in SQL Azure. Its aim is to protect data by blocking IP addresses that are not granted permission to access the SQL Azure server.

Now, there are two types of connection request

1. From Azure platform. E.g.: An app deployed on Azure platform that uses SQL Azure.
2. From Internet. E.g.: An mobile app deployed outside of Azure platform that uses SQL Azure as the database.

Now, how to give firewall access to connection request originating from Azure? To allow Azure services to access the SQL Azure service, just add the firewall rule 0.0.0.0 – 0.0.0.0 in the firewall rules or alternatively just check the box – “allow other Windows Azure services to access this server”. To do so, go to Azure management portal >> select your server >> click on ‘firewall rules : [number] ’ which is visible in the center pane along with server information. You will see this:

Now, let’s grant access to requests originating from Internet. This time click the Add button in the firewall pane:

After clicking on Add, you will see:

Give a name to the rule and input the start range and the end range. Also, note that your current IP address is shown in the panel. Input this in the start and the end range to give access to the connection request originating from your current machine. Then press Ok. You will be able to this rule in the firewall panel now. After adding the rule, allow some time for the SQL Azure firewall settings to take effect. Also after adding the firewall rule, you can update and delete it if you wish so through the Azure portal itself from the Update and Delete button respectively.

So, this is how you could block requests based on IP address via SQL Azure firewall.

Now, next is Authentication.

SQL Azure supports SQL server authentication and so a user must select a username and a strong password. Note that the strong password (8 characters long with combination of symbols, numbers and letters) is enforced. Moreover, it is the best practice to select a strong password. Note that login names like admin, guest, sa are not available. So you are forced to select a username other than the names that are not allowed.

In addition, SQL Azure does not support Windows Authentication . Next is Authorization.

Authorization is the definition of which users can access which objects in a database.

With SQL Azure, we have two new roles.

1. DBManager role: This role allows user to create new databases from the master database.
2. LoginManager role: This role allows user to create new logins in the master database.

Now, let me give you a demo by creating a login, then grant that login the DbManager role.

Before we create a Login, here are few things to note

1. Create a strong password
2. We must be connected to the master database to manage logins

So let me create a Login named MyFirstLogin

* I have executed the TSQL using the SQL Azure manage tool - You can do so by connecting to SQL Azure via SSMS too.

Now let’s create an User named MyFirstLoginUser

And now let’s grant this user the DbManager role:

Thus, we have a user in the master database that can now create new databases in master.

In addition, schema (container that holds database objects) is also supported. So one can manage access rights through schema too instead of users.

This is how you create users, logins and assign role with SQL Azure. It is similar in the experience with SQL server. Just remember that we have two new roles and few login names like sa, guest, admin are not allowed. Also we need to have a strong password.

Now, next is Encryption and SQL Azure.

All traffic between application and the SQL Azure is SSL encrypted all the time.

Moreover, as of this writing, the SQL Azure does not support encryption out of the box – it should be done at the application level.

Thus, in this article we saw the SQL Azure security model and are half way through our journey of getting started series!

Update:

Part 6: http://beyondrelational.com/blogs/parasdoshi/archive/2011/11/21/getting-started-with-sql-azure-part-6-lt-lt-paras-doshi.aspx