Worker role won't connect to SQL Azure

Question

I'm working on an typical web application using cloud services to learn how to use windows azure. In my deployment, I have two roles, a web role running an ASP.NET MVC application, and a worker role that provides some backend services.

The database is hosted on SQL Azure (in the same subscription and datacenter) and is managed using Entity Framework Code First. The connection string is the one Azure provided.

The strange thing is that on the Web Role, Entity Framework is successful in connecting and executing queries against the SQL Azure Database. But when the same code is run in the Worker Role, it crashes with the following exception:

System.Data.SqlClient.SqlException: 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 verified that the connecting strings used are the same, but have been unsuccessful in determining why the Worker Role won't connect.

EDIT: Executed the role using the Compute Emulator and the SQL Azure connection string (with the correct firewall exception already configured) and the code didn't crash. So apparently it is only happening when the code is run in the Cloud.

EDIT 2: The SQL Azure firewall is correctly configured to allow connections from the Azure Datacenter, as evidenced by the Web Role's successful connection.

Answer 1

I had this issue. My web role was obtaining the database connection string from the Web.config file (because the XSLT transformations work well). In effect I was passing the name of my connection string into my context's constructor, not the connection string itself.

My worker role was using Azure's configuration, using RoleEnvironment.GetConfigurationSettingValue() to get the connection string and passing the connection string itself into the context's constructor. This worked in the emulator against a production database, but not in the Azure cloud itself.

My solution was to stop using the Azure configuration, and instead put the connection string into my App.config, and pass the name of the connection string into the context's constructor, like I was doing in the web role. This solved my problem and the worker role was able to connect to the database without any issues.

Of course, App.config files cannot have transformations out of the box, unlike Web.config files (because, hey, who'd need that?) so I had to follow the instructions here to make that happen.

Answer 2

Check your different cloud configurations. (In a default project template, you probably have one for Local & One for Cloud)

Also check your different release configuration if your using Config Transforms. i.e. under the App.Config / Web.Config, you might see App.Debug.Config / App.Release.Config.

Verify your not overwriting the connection string in release mode.

Answer 3

Make sure that under "Configure" for the database in the Azure Portal, in the section "allowed services", Windows Azure Services is set = YES. This is a special firewall rule (0.0.0.0) for Windows Azure services. Turn on this option to let Windows Azure services connect to your SQL Databases.