Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 293
Ronan Moriarty Avatar asked Feb 03 '13 02:02

Ronan Moriarty


People also ask

How do I get Azure SQL Database connection string?

Navigate to the database blade in the Azure portal and, under Settings, select Connection strings. Review the complete ADO.NET connection string. Copy the ADO.NET connection string if you intend to use it.

How can we connect Azure Web app to an Azure SQL Database?

Choosing the "Allow access to Azure services" option will allow the app service to connect to the MySQL server. On the MySQL server blade, under the Settings heading, click Connection Security to open the Connection Security blade for Azure Database for MySQL. Select ON in Allow access to Azure services, then Save.

Can we connect Azure SQL Database from local machine?

Connect to azure sql database from visual studio Click on Add SQL Server icon. Expand Azure node. You will see all databases in Azure. Click on the Azure database you want to connect.

Can not connect to the database in its current state Azure?

This usually indicates you may have hit a pricing/budget limit on your subscription/resource. Please reach out to your billing administrator to check if any budget restrictions were applied to this resource. If it is still not resolved, please reach out to Azure Support and submit this as a "Billing Issue".

Why can’t I connect to my Azure database?

The firewall on your client must allow connections to your database server. If you’re connecting from Azure VMs (virtual machines), verify that network security group (NSG) rules are not blocking the connection. Also review the route table to determine if there are any VPN devices that require configuration.

How to connect to Azure SQL Server from Azure VM?

Generally connecting from Azure VM to Azure SQL is a bit different then connecting from outside of Azure. To allow applications hosted inside Azure to connect to your SQL server, Azure connections must be enabled. To enable Azure connections, there must be a firewall rule with starting and ending IP addresses set to 0.0.0.0.

Is it possible to connect OLEDB to Azure?

Some required OLE DB schema rowsets are not available from an Azure connection, and some properties that identify features in SQL Server are not adjusted to represent SQL Azure limitations. For most common connect/query/update tasks it seems to work fine. See the respective OLEDB provider's connection strings options.

How do I test connectivity between Azure application server and SQL database?

Test the connectivity between the application server and the Azure SQL Database by using SQL Server management Studio (SSMS), a UDL file, ping, or telnet. For more information, see Troubleshooting connectivity issues and Diagnostics for connectivity issues. As a troubleshooting step, you can also test connectivity on a different client computer.


1 Answers

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.

like image 70
Ronan Moriarty Avatar answered Sep 28 '22 21:09

Ronan Moriarty