Monday 27 February 2012

The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.

This error is caused when the configuration for the local ASP.NET application providers (security, etc) are configured badly. It may be caused because ASP.NET was initially configured to run with SQLExpress, which was subsequently uninstalled.

Mine was:
<add connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true;" name="LocalSqlServer" providerName="System.Data.SqlClient" /> 

and I no longer had SQLExpress installed.

The solution is to find the machine.config / web.config that refers to the bad connection string. This can be a bit of an adventure. I searched all of the config files (machine.config, web.config) for all of the .NET versions at C:\Windows\Microsoft.NET\Framework\xxxx\Config\. In the end I discovered that because I had a 64-bit machine there were 64 bit versions also at C:\Windows\Microsoft.NET\Framework64!

 There is of course a simple way of fixing the problem. Open up Inetmgr, and select the "Connection Strings" section. The connection string LocalSqlServer is in there. Edit this to point to your DB (or file). If you need to reinstall the providers into SQL Server, use aspnet_regsql.exe.

To understand which is the underlying file that is being edited, there are two rules:

Are you running 64-bit? If so the config file will be located under C:\Windows\Microsoft.NET\Framework64, and for 32-bit it will be C:\Windows\Microsoft.NET\Framework.

What .NET version is the default IIS server running under? If it is v2.0, then the subfolder will be v2.0.50727, and for 4.0 it will be v4.0.30319.

Then check the machine.config and web.config.

Update
Modifying the web.config did not fix the problem. It was even more complicate than that.

The local application had a <clear/> element in its <connectionstrings> section. It also had RoleManager and Profile sections that each had the <clear/> child element. But it did not clear down all of the necessary sections. A search of LocalSqlServer in the web.config showed that it was being used by other providers, such as the healthMonitoring element.

The clearing of the connectionstrings element in the applications config, would have cleared the LocalSqlServer connection string and thus invalidated these elements inherited from the root web.config.

No comments:

Post a Comment