EF6 and azure worker role : underlying provider fail on open

Question

I’m struggling with EntityFramework 6 and a PaaS architecture. I have a repository project that calls a DAL project to execute some EF6 imported stored procedures. Until recently we were going for a IaaS architecture but for some reasons we switched to PaaS. This same repository was successfully used by a WCF service. This WCF service has been converted to a Web Role and works like a charm. I now use this same repository in Worker Role to de-queue a service bus and process data (en-queued by the Web Role). But then I got the error while using the repository on my first call to a stored procedure via EF6 (a Get request)

Schedule worker error with inner exception : 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The underlying provider failed on Open. 
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass45`1.b__43()
at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ExecutionOptions executionOptions, ObjectParameter[] parameters)
at XXX.DBContext.XXXEntities.GetTrades(Nullable`1 id, Nullable`1 entityBuy, Nullable`1 entitySell, Nullable`1 sessionId, Nullable`1 orderBuy, Nullable`1 orderSell)
at XXX.RepositoryServices.MarketPlaceService.GetTradeInstances(Nullable`1 EntityBuy, Nullable`1 EntitySell, Nullable`1 SessionId, Nullable`1 OrderBuyId, Nullable`1 OrderSellId)
at WorkerRole1.WorkerRole.Run()

(XXX and YYY are namespaces but for policy reasons I cannot display them) I tried to set a firewall exception for azure on the Db (hosted in IaaS) for IP from 0.0.0.0 to 0.0.0.4. I added a configuration class that inherit from DbConfiguration that configures like this in ctor :

this.SetExecutionStrategy("System.Data.SqlClient", () => SuspendExecutionStrategy
? (IDbExecutionStrategy)new DefaultExecutionStrategy()
:new System.Data.Entity.SqlServer.SqlAzureExecutionStrategy(5,TimeSpan.FromMilliseconds(25)));

(using SuspendExecutionStrategy = true) I made sure EntityFramework and EntityFramework.SqlServer dlls of the right version are copied in the cspkg.

My connections strings are also good (with credentials inside). I'm sure of the last part because I can successfully use ADO.NET sql queries in my worker role Run method as well as in the classes that use this repository. I tried with the latest version of EF6 (ie. 6.1) and it doesn't work. I tried to put my worker in the same subnet as the web roles (doesn't work). I tried to use the IP address of the SqlServer in the connection string but didn't worked. The same connection string is used for ADO.NET and EF6.

    <add name="XXXEntities" connectionString="metadata=res://*/XXXContext.csdl|res://*/XXXContext.ssdl|res://*/XXXConte xt.msl;provider=System.Data.SqlClient;provider connection string=&quot;data  source=negobdd1.YYY.com;initial catalog=XXX;user id=[User];password= [Password];MultipleActiveResultSets=True;App=EntityFramework&quot;"  providerName="System.Data.EntityClient" />

(sorry for all the red-tape but it's a client requirement. also all the XXX are all exactly the same string) I tried to connect from the azure VM machine hosting the worker role to connect to the db with a .udl file using the address "negobdd1.YYY.com" and the user and password of the connection string with success. I can ping the SqlServer machine with success too.

EDIT

the context is created like this

    public partial class NegotiationsPlatformEntities : DbContext
    {
        public NegotiationsPlatformEntities()
        : base("name=NegotiationsPlatformEntities")
        {
        }

    // auto-generated methods here

    }

with this instanciation

     internal NegotiationsPlatform.DBContext.NegotiationsPlatformEntities db = new NegotiationsPlatform.DBContext.NegotiationsPlatformEntities();

I don't set any special parameters except for the name of the connections string.

RE-EDIT

After looking at the DbContext.Database.Connection.Datasource I found that apparently EF6 is targeting the local staging db server, not the Azure IaaS SqlServer. I'll investigate and post-back.

Any help would be greatly appreciated.

Thanks.

Answer 1

It turns out that the worker roles don't deal with connection string file the same way as a web role. Because there are multiple deployement environements, we have multiple files named "connectionStrings" + [target] + ".config" .

Inside the OnStart method of a web role we use a .bat file to delete any unnecessary config file and rename the desired file as "connectionStrings.config". That way in a webrole deployed on Azure, it keeps only the PaaS config file and then use it. But apparently it doesn't work the same way in a worker role.

The cleanup ".bat" file is executed and leaves only one config file with the right content but what is used is what was on the default config file. So I guess the worker loads the config file before it calls the OnStart method, and therefore any changes won't matter. ( I haven't tried to kill the worker process to see if after a reboot it loads with the only good file remaining from the first deployement )

So here's my solution : delete all the config file in the worker project except the PaaS ones and don't rely on a ".bat" file used during OnStart.

Thanks a lot to Dean Ward for putting me on the right path :) All in all it was "just" a matter of connection strings.