Connecting to SQL Azure with SQL Server Management Studio 2008 R2

Goal: Show a user how to connect to a SQL Azure Server from SQL Server Management Studio 2008 R2.  If you don't have a SQL Azure account you can treat this post as information to further your knowledge about Microsoft cloud storage
As of 11/10/2009, SQL Server 2008 R2 is feature-complete as well as SQL Azure.  The new version (PDC 2009) of SQL Azure has been provisioned on all of the production servers.  The current version of SQL Server 2008 R2 available is CTP which is a publicly available beta of the Enterprise Edition (Note: Enterprise Edition is not the highest edition anymore.  Since SQL 2008 R2, Microsoft has added Premium Editions that allow for massively-scalable database servers).
What you need to follow along:
- SQL Azure Account (active)
- SQL Server 2008 R2 Management Studio Installed (Download just the Management Studio tools here)
For the initial release being shown at the PDC 2009, SQL Azure includes some new configuration features that need to be enabled in order to connect:

1. Log in to http://sql.azure.com with your account information
2. Go to the SQL Azure tab to access your projects (If one doesn't exist, create one.)
3. Click on the project row to access the SQL Azure Server Administration screen.

4. If you do not have a server SQL Azure server provisioned, you will be asked to create one.
5. In the SQL Azure Administration Screen you can
   1. Drop/Re-create a server
   2. Reset the admin password
   3. Get a connection string to the database server
   4. Test the server connection (send a connection ping)
   5. Manage your databases
   6. Change the Firewall Settings

6. Create a sample database and click the "Test Connectivity" button to ensure you can connect via the network/firewall to the remote Azure server.  This step should be done to make sure that you do not have any network or firewall issues.  Furthermore, this ensures that the server and database were properly provisioned in the Azure Cloud and are fully functional.

7. Open SQL Server 2008 R2 Management Studio and in the Server Name text box, enter the name of the SQL Azure Server from the Server Administration screen (Shown in Step 5).  Make sure you copy the fully qualified DNS name (as shown in the screen below).  Ensure that the Server Type is set toDatabase Engine and the Authentication is set to SQL Server Authentication.

8. Try connecting by hitting the Connect button. If you get an error similar to the one shown below that states your IP does not have access, you need to configure your SQL Azure Firewall settings.
   1. SQL Azure (as of the October 2009 version) includes Firewall Settings manager that allows you to explicitly control which IP addresses are allowed to connect to your Azure database server.  This was done to alieviate customer concerns regarding placing sensitive data on Microsoft's server.  If you specify that only your network can access the database server, this gives you more piece of mind that a hacker hasn't hijacked the user/password and can connect.  Furthermore, it is more secure and a best practice to do this.

9. In order to fix this, we simply need to allow the IP address you are connecting from in the Firewall Settings manager.
   1. One question you may be asking is, what do I do if I have a dynamic IP"?  For example, if you have a broadband connection such as DSL or FIOS, your IP address can change over time.  This would be really annoying if you had to add a firewall exception every other day!  One way to get around that is to find your ISP's IP range and enter that in the exception.  It is not as secure as providing a single static IP address however.  It is much more secure than opening up SQL Azure to the entire Internet (full range of IPs). Most ISPs will provide the range for your area if you call them.  Some even publish this information on their knowledgebase/support.
10. Go back to the SQL Azure Server Administration screen and click the Firewall tab.  Clicking the Add Rule button will bring up a model popup where you can enter the name of the Firewall rule and enter either a range of IPs or a single IP:
    1. The popup lets you know what your current IP address is.  You can simply copy and paste it into both locations.  You can enter as many locations as you need.  For example, you may have a home network, wireless card with an IP range, work network, etc.
    2. If you have an ISP that has a dynamic list of IPs, enter the IP range that was provided to you.
    3. This is NOT recommended, but you could add a rule that opens up the entire IP stack of the Internet by entering 0.0.0.0 -> 255.255.255.255
    4. If you are not comfortable with an IP range, another solution is to use a "proxy service" and this will masquerade your IP to something else.  Static IP addresses cost money; however, they can be useful in these situations.
    5. The dialog mentions that it could take up to five minutes to connect. I tried this several times and after one minute, you should be able to connect.

11. You should be able to open up SQL Server 2008 R2 Management Studio and connect to your SQL Azure database. If you are successful, you will see your database in the Object Explorer (shown below)
 

Conclusion
You should now be able to configure an SQL Azure database and connect to it via SQL Server Management Studio.  Managing a SQL Azure is a little different and in my next post I will show some of the features of managing SQL Azure with SQL Server 2008 Management Studio.