Cannot access SQL Azure DB from Azure website even though same connection string works from local website

Question

I've recently set up my first website on Azure. For now, it's basically just the standard MVC4 template website with the Home and Account controllers. In Azure, I have one website, and one SQL database. I can access the SQL Azure database from SSMS, and have set up a login and user to be used by my website.

In my development environment, pointing at my development database, I can access the /Account/Login page fine. I can register, and I can see the new user in my local db. I can also change the connection string to point my development website at my SQL Azure DB, and again I can access /Account/Login and register new users. I can then see those new users in the SQL Azure DB.

The problems happen when I deploy the website to Azure. I have a transform config associated with my publishsettings file, and I can see in the output window when publishing the site to Azure that this transform is applied during deployment. This amends the local development DB connection string to the SQL Azure connection string. I have also verified that this SQL Azure connection string is in the actual web.config file deployed (using FileZilla FTP to retrieve the actual web.config deployed). I can access the home page of my site on [mysite].AzureWebsites.net, but when I click the login link to go to the /Account/Login page, I get the following error:

[ArgumentException: Format of the initialization string does not conform to specification starting at index 0.]
System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue) +5313265
System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +124
System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +95
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +59
System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +24
System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +167
System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) +61
System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +66

System.Data.Entity.Internal.LazyInternalConnection.InitializeFromConnectionStringSetting(ConnectionStringSettings appConfigConnection) +122
System.Data.Entity.Internal.LazyInternalConnection.TryInitializeFromAppConfig(String name, AppConfig config) +32
System.Data.Entity.Internal.LazyInternalConnection.Initialize() +127
System.Data.Entity.Internal.LazyInternalConnection.get_ProviderName() +13
System.Data.Entity.Internal.LazyInternalContext.InitializeContext() +346
System.Data.Entity.Internal.InternalContext.CreateObjectContextForDdlOps() +17
System.Data.Entity.Database.Exists() +36
[MyWebsite].Filters.SimpleMembershipInitializer..ctor() +105

This seems to suggest that there is an issue with my connection string, but like I mentioned earlier, this exact same connection string worked from my local website.

I've considered that the issue might be firewall-related, but I've checked the settings in the Azure management portal, and the Windows Azure Services firewall rule is applied to allow that access. Also, I've tried removing the firewall rule for my local machine to access the SQL Azure DB, to see would I get a similar exception, but the exception thrown was very obviously firewall-related.

I have also tried to add the SQL Azure connection string through the Azure Management portal (though I didn't see how to specify the provider) - needless to say, I got the same "Format of the initialization..." exception mentioned above.

My connection string in the web.config is in the following format:

<add name="[my connection name]"
     connectionString="Server=tcp:abc123.database.windows.net,1433;Database=[my database];User ID=[my login]@abc123;Password=[my password];Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" 
     providerName="System.Data.SqlClient" />

Any suggestions would be very welcome.

Answer 1

I've FINALLY got to the bottom of it. What I didn't realise until now was that there were 2 connection strings in the web.config that ultimately gets deployed to the Windows Azure website - my own custom one, but another DefaultConnection connection string as well, which has the format:

<add name="DefaultConnection" connectionString="DefaultConnection_ConnectionString" providerName="System.Data.SqlClient" />

• obviously, not a valid connection string (and hence the format exception above). You can see this when you download the actual web.config from your Azure website using FTP.

This default connection string isn't anywhere in the web.configs or various transforms in my solution. Looking at the Output window during publishing, there are a number of transforms that get applied to the web.config. I've gone through the various versions of the files that get generated during the build / publish cycle, and none of them have the DefaultConnection connection string in them, not even in the obj\Release\Package\PackageTmp\ folder. So I'm guessing something in the Web Deploy phase is inserting it as the very last modification of the web.config. There is an MSDeployParameterValue element in the publishsettings file that mentions connection strings and the web.config - I guess it could be that.

In the AccountModels.cs file, a reference is made to this DefaultConnection:

public UsersContext()
: base("DefaultConnection")
{
}

This is how the particular connection string is chosen. Changing this parameter to your custom connection string name ensures that your custom database gets used for the various account stuff, and resolves the format exception seen above.