TransactionScope on Azure App Services with Sql Azure

Question

So I have the following trivial code in a WebAPI controller that is published to an Azure App Service website.

using (var tx = new TransactionScope())
{
   var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OrganizationManagement"].ConnectionString);
   connection.Open();
   return Enumerable.Empty<TimeSessionDTO>();
}

100% of the time this is giving me a transport error exception on the Open call:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I have tried using ReliablSqlConnection from the Transient Fault Handling EL block using an exponential retry policy, and I just end up with a transaction timeout with that.

If I remove the surrounding TransactionScope, it works and does not throw an exception.

If I run the same code on my local machine with the connection string still pointing to the SQL Azure database, it works fine with the TransactionScope.

What could be going on that I cannot open a database connection inside of a transaction, in an Azure website?

Update: I should also note that using an Entity Framework DbContext inside of a TransactionScope was working fine. It's just choking on plain ADO.NET for some reason.

FYI I also tried it on a new MVC application on Azure, with the same result. I just don't get it :)

Actually this is not working with Entity Framework anymore either. I really don't understand :) — Gerald

Gerald Gerald · Accepted Answer · 2015-04-23T01:21:13

Wow, so the problem seems to have been with the connection string. When I first deployed the database I let the database project build the connection string from the server/database/user info, which I later added to the Web.config file in the WebAPI project. Then when I deployed the WebAPI project I guess it saved that connection string in the publish profile.

It turns out that connection string used a little different format and different options than what is supplied when you view the connection strings in the Azure portal. I had already changed it in the Web.config file, but it seems that what is in the publish profile overwrites what is in Web.config, so the change never took effect on the server.

I guess that explains why it worked when I ran it locally, but I have no idea why it only failed when it was in a transaction.

TransactionScope on Azure App Services with Sql Azure

1 Answers