Starting with SQL Server 2008 R2, SQL Server Management Studio can connect directly to SQL Azure. The minimum install to do this is SQL Server Management Studio Express Edition (download here for free); however any version of SQL Server (except SQL Server Express) that includes the SQL Server Management Studio will work.
Here is how to us SQL Server Management Studio to attach to SQL Azure:
1. The first step is to figure out your server name, to do this go to the SQL Azure Portal.
2. Login with your Windows Live Id
3. Click on your project name.
4. You should be at the Server Administration page.
5. Highlight the server name in the browser and copy (Ctrl-C) in your clipboard.
6. Now open SQL Server Management Studio 2008 R2. By default it will open the Connect to Server dialog.
7. Paste in the full DNS of the server stored in the clipboard into Server Name.
8. In the login box enter in your Administrator username for SQL Azure, this was on the Server Administration page where you got the full DNS of the server from.
9. The password is your SQL Azure password.
10. Click on Options >>
11. This will take you to the Connection Properties tab, check the Encrypt connection box. You should always encrypt your connection, see this previous blog post for why.
12. Press the Connect button.
It is that easy to start administrating SQL Azure using SQL Server Management Studio 2008 R2. Do you have questions, concerns, comments? Post them below and we will try to address them.
For me this fails with the following message:
TITLE: Connect to Server
Cannot connect to oxpx6db3d0.database.windows.net.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'sys.configurations'. (Microsoft SQL Server, Error: 208)
For help, click: go.microsoft.com/fwlink
I've used Management Studio a fair bit with local and remote SQL Server instances. This is my first time using it with SQL Azure. My SQL Azure server looks OK - I can view and edit it in the Management Portal.
Looks like I'm running Management Studio 2008, not 2008 R2. Perhaps that's the problem?
I've connected successfully to my Sql Azure database using Sql Server Management Studio Express R2. However, when I attempt to double click on any of the objects under the security tab to review the permissions nothing happens. On my local SqlExpress database it works fine. Any idea what this isn't working and how to get around this limitation? Thanks.