Can't connect to SQL Azure from app, but can from SSMS

Question

I have a SQL Azure instance set up and can connect to it with no issue from SQL Server Management Studio. However when my app tries to connect, this error occurs:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm using Entity Framework Code First 4.1 and ASP.NET MVC 3. My app was originally developed successfully using SQL Express. Now I'm using this tutorial to move the database to SQL Azure (the app will move there eventually as well but development is still continuing).

Since SSMS works OK I'm guessing it comes down to web.config? I've tried every combination of connection string name:

<connectionStrings>
    <add name="ApplicationServices" connectionString="Server=tcp:suppressed.database.windows.net,1433;Database=EventsTest;User ID=suppressed;Password=suppressed;Trusted_Connection=False;Encrypt=True;PersistSecurityInfo=True;" providerName="System.Data.SqlClient" />
    <add name="DomainContext" connectionString="Server=tcp:suppressed.database.windows.net,1433;Database=EventsTest;User ID=suppressed;Password=suppressed;Trusted_Connection=False;Encrypt=True;PersistSecurityInfo=True;" providerName="System.Data.SqlClient" />
    <add name="Events.DataAccess.EntityFramework.DomainContext" connectionString="Server=tcp:suppressed.database.windows.net,1433;Database=EventsTest;User ID=suppressed;Password=suppressed;Trusted_Connection=False;Encrypt=True;PersistSecurityInfo=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

I've also tried Wireshark which I know very little about but seems to suggest some activity (192.168.1.101 is my machine, 207.46.63.13 is the SQL Azure server):

1   0.000000    192.168.1.101   207.46.63.13    TCP [TCP segment of a reassembled PDU]
2   0.116269    207.46.63.13    192.168.1.101   TCP ms-sql-s > bmc-net-adm [ACK] Seq=1 Ack=2 Win=8444 Len=0
3   2.091928    192.168.1.101   207.46.63.13    TCP [TCP segment of a reassembled PDU]
4   2.209371    207.46.63.13    192.168.1.101   TCP ms-sql-s > kmscontrol [ACK] Seq=1 Ack=2 Win=5969 Len=0
5   2.352974    192.168.1.101   207.46.63.13    TCP [TCP segment of a reassembled PDU]
6   2.469444    207.46.63.13    192.168.1.101   TCP ms-sql-s > vaultbase [ACK] Seq=1 Ack=2 Win=8625 Len=0

Any ideas what might be happening?

Answer 1

I ramped up debugging within the DomainContext : DbContext class and found that the connection string always pointed to SQL Express even though there were all those entries in web.config pointing elsewhere. Then I realised that the problem was the parameter passed to the base class in the constructor.

Somewhere along the line I thought this parameter was the name of the database that was going to be used. Now I realise that its the name of the connection string entry (or the connection string itself).

Answer 2

I set my connection string explicitly when instantiating my DBContext:

public class DB: DbContext ..

var databaseConnectionString = 
  "Server=tcp:private.database.windows.net;Database=privateDB;UID=private@private;Password=private;Trusted_Connection=False;Encrypt=True"

var db = new DB(databaseConnectionString);