How to connect to a SQL Azure database

Connecting to a SQL Azure database is very new, confusing and uncomfortable territory for me and I found it tricky. However I managed to get my Entity Framework model connected to it after quite some faffing around. Here's how to do it:

Create you SQL Azure server and database

Go to the Windows Azure portal. Click Database at the bottom. Setting up you SQL server and database is fairly self explanatory and you're a clever person so I won't waste your time telling you step how to do this.

Keep your portal open since you will be setting the firewall up soon.

Create your entity model

Right click the project that you plan to have your Entity Model in and select Add then New Item. Then chose and ADO.NET Entity Data Model and name it something. Choose Generate from database and click Next.

Now time for some connection fun.

1. Click "New Connection..."
2. Your servername will be servername.database.windows.net Your servername will be the one seen from the Windows Azure portal e.g. ab12abcedef
3. Choose Use SQL Server Authentication
4. Username will be DBusername@servername
5. Password will be the password for this database that goes with the "DBusername" from above
6. Click Test Connection and....

FAIL! You will get a dialogue box saying that you have been blocked by a firewall and tells you your IP address that just got rejected. Keep all of this open as we will be coming back.

Make an exception in your firewall

Go to your Windows Azure portal and find your database server. You can then add a rule to its firewall. The range can begin and end on the IP address that Visual Studio complained about a minute ago. Give your rule a name and you're done. Well, you will be in about 5 minutes when the exception has propagated.

Connection strings

Back to your connection setup in Visual Studio. Clicking Test Connection should now test successfully (5minutes after adding the firewall rule). You will now have a connection string similar to this:

Data Source=ab12abcdef.database.windows.net;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=username@ab12abcdef;Password=MyPassword;MultipleActiveResultSets=False