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

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.

I know this is an old post, but I wanted to share my findings. In my .pubxml file, it stored my localdb connection string, and would not update on any builds, or publishing. I had to manually update the publish file with my azure DB connection strings in order for it to work. Hope this helps save someone time.

About the Connection strings of SQL Azure

• The SQL Azure Database service is only available with TCP port 1433. Ensure that your firewall allows outgoing TCP communication on TCP port 1433.

• SQL Azure does not support Windows Authentication. The Trusted Connection will always be set to False.

• SQL Azure doesn’t support unencrypted connections. You need to specify in your connection string that you want to encrypt the connection.

• Connecting to SQL Azure by using OLE DB is not officially supported.

Standard way

Server=tcp:[serverName].database.windows.net;Database=myDataBase;User ID=[LoginForDb]@[serverName];Password=myPassword;Trusted_Connection=False;Encrypt=True;

Use 'username@servername' for the User ID parameter.

For more information check this out Connection strings for SQL Azure

I hope this will help to you.