You don’t want to miss this out,Serious SQLServer training – 24 Hours of PASS event !

Security is one of the most discussed topics for any cloud deployment plans and no organisation would like to compromise on security when they make that ‘final’ decision to move their database to the cloud.

In this blog post we will review how protected are our SQLDatabases within Windows Azure using firewalls.

I encountered couple of firewall related issues when I first started working with Windows Azure, and that motivated me to write this blog post.

One of the error message or a warning was

The error message was pretty much saying that the IP address (Hidden in the picture) is not configured to manage the SQLDatabase within the server.

Before we see how we can configure the IP address to grant access, we will need to review what levels of firewall rules can be set on Windows Azure.

We can set firewall rules at Server Level and Database Level

Server level rule simply means that a client within a particular IP or within a range can access all your databases within the same SQLDatabase server.

In case you would like to restrict a client with a specific IP or a range of IPs to access only specific databases within the SQLDatabase server, then the role of Database Level firewall rules pitch in.

SQLDatabase firewall is considered very important within Windows Azure architecture. Connection attempts from the Internet and Windows Azure must first pass through the SQL Database firewall before they can reach your SQL Database server or database.

Let’s consider a simple example to learn this in a better way -

When computer A tries to connect SQLDatabase Server from web, then the originating IP address is first checked by the SQLDatabase firewall against the server level firewall and grants access if the IP is within server level range. It will check the database level firewall rule in case the IP is not found within server level firewall range and connection is granted for the specific database in case the IP falls in database level rule.

Setting up server level rules

The easiest method to create a Server level firewall is via the Windows Azure portal itself.

We will need to select the server and choose the Config option to set a range of IPs or a single IP.

We have the option to check what all IPs or IP ranges are configured at server level using view sys.firewall_rules after connecting to master database.

Setting up database level rules

The recommended approach to create a database level firewall is to use the stored procedure sp_set_database_firewall_rule

We will need to connect to the exact database for which we need to create the rule and run the stored procedure with the parameters.

[The IP mentioned above is just a test case one]

The database level IPs or range can be checked using another view sys.database_firewall_rules and we will need to connect to the database and run this view to get the accurate details.

A request with IP 10.0.0.5 will be able to access only the database for which the rule was set and not all the databases within the SQLDatabase server.

Conclusion

There is good amount of protection available on Windows Azure platform for protecting your mission critical SQLDatabases and in this post we had a quick overview of firewall level protection.

You can read more about the comprehensive security concepts for a SQLDatabase on Windows Azure here -

Windows Azure SQL Database Connection Security

Keep checking this space for more and there are lots of new and exciting stuff coming up !

This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

SQLSailor is exploring(Part4) – Basic DML,DDL operations with SQL Databases on Windows Azure

SQLSailor is exploring(Part5) – SQLDatabase Dashboard on Windows Azure

Thanks for reading.

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

Posted on June 20, 2012 by Anup

This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

In this post we will check out how to design a SQL Database which is on Windows Azure.

Once the database is created we have the Manage button to start working on administration and design of the database (As mentioned in the earlier posts)

Clicking on Manage button will route us to another page where in we will have to enter the credentials(Set during database creation)

Once the credentials are validated we will have a page where in we can choose the design option.

Let’s start building a table for the database MySQLDatabase which we have already created earlier.

We have the option called New table which will help us to start designing the tables and the columns.

We can specify the table name, start building the columns, set Primary key, set Default values, specify Is Identity, Is Required etc.

Once the required details have been filled in we have the option to Save the structure.

We have the option to create and manage the indexes too. The Indexes and Keys section will display the existing indexes and we have the option to create new ones too.

Creating an index is also pretty easy and we have some advanced options like Auto Update Stats and Rebuild Index Online.

Once the database and table is ready, we will need some data to do some DML operation. We can insert some data via the Data section.

The portal is pretty smart as it does validation the same time you are entering the data.

Conclusion

Designing the tables,indexes,relations etc are really easy via the portal and we saw some real time validations too.

I will write about some DML operations during the coming days and there some exciting stuff coming up too.Please stay tuned.

Thanks for reading.

SQL Azure Compatibility Assessment Tool

Posted on January 4, 2012 by Anup

Today I was thrilled to hear about the new tool which was released by Microsoft yesterday(3rd Jan 2012).The code name for the tool is “SQL Azure Compatibility Assessment Tool” and this is the right tool to test compatibility if you are planning to move your databases to SQL Azure.

All the details of the release if available under

http://social.technet.microsoft.com/wiki/contents/articles/6246.aspx

I decided to test this wonderful tool which will in a matter of minutes will help me to understand any compatibility issues if I move my databases to the cloud(SQL Azure).

For the test to be successful you will need a LIVE ID and a .dacpac

I already had a live ID which I am using since many years and I decided to go ahead with the process of creating a .dacpac

How do I prepare a .dacpac -

1. You will need to download and install SQL Server Data Tools to proceed and create a .dacpac

SQL Server Data Tools needs to have Visual Studio 2010 with SP1,and as I already had them installed on my test server,the tool got installed correctly.

2. Opened SQL Server Data Tools GUI to Create a Database Project and Imported a database which I had already backed up from SQL Server 2005 and restored to SQL Server 2012 RC 0.