Illegal characters exception when calling connection string in Web.config

Question

I am creating a C# MVC4.5 project using entity framework with the following connection string set in my Web.config:

<connectionStrings>
    <add name="Entities" 
         connectionString="metadata=res://*/PersistenceModel.csdl|res://*/PersistenceModel.ssdl|res://*/PersistenceModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sqlexpress;initial catalog=TravPro;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" /> 
</connectionStrings>

I then access this string as follows:

string con = ConfigurationManager.ConnectionStrings["Entities"].ConnectionString;
WebSecurity.InitializeDatabaseConnection(con,
                                         "UserProfile", 
                                         "UserId", 
                                         "UserName", 
                                         true);

This throws an 'Illegal characters in path' exception:

[ArgumentException: Illegal characters in path.]
   System.IO.Path.CheckInvalidPathChars(String path, Boolean checkAdditional) +10631190
   System.IO.Path.Combine(String path1, String path2) +42
   WebMatrix.Data.ConfigurationManagerWrapper.GetConnection(String name, Func`2 getConfigConnection, Func`2 fileExists) +208
   WebMatrix.Data.ConfigurationManagerWrapper.GetConnection(String name) +91
   WebMatrix.Data.Database.OpenNamedConnection(String name, IConfigurationManager configurationManager) +18
   WebMatrix.Data.Database.Open(String name) +61
   WebMatrix.WebData.DatabaseConnectionInfo.Connect() +42
   WebMatrix.WebData.SimpleMembershipProvider.ConnectToDatabase() +25
   WebMatrix.WebData.SimpleMembershipProvider.CreateTablesIfNeeded() +35
   WebMatrix.WebData.WebSecurity.InitializeMembershipProvider(SimpleMembershipProvider simpleMembership, DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean createTables) +73
   WebMatrix.WebData.WebSecurity.InitializeProviders(DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables) +51
   WebMatrix.WebData.WebSecurity.InitializeDatabaseConnection(String connectionStringName, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables) +51
   TravPro.Web.Filters.SimpleMembershipInitializer..ctor() in c:\Work\trunk\TravPro\TravPro.Web\Filters\InitializeSimpleMembershipAttribute.cs:43

Can someone please assist and let me know which characters need to be escaped or how to fix this exception?

Answer 1

The connection string you've stored is for Entity Framework - it's not a "regular" ADO.NET connection string. The Entity Framework connection string does contain a regular ADO.NET connection string (the bits inside the provider connection string=.....) - but more stuff in addition to this.

WebSecurity on the other hand expects a regular ADO.NET connection string as input - NOT an Entity Framework connection string.

So to solve this, you need to add a second connection string for WebSecurity to your <connectionStrings> section in the config file:

<connectionStrings>
    <add name="Entities" 
         connectionString="metadata=res://*/PersistenceModel.csdl|res://*/PersistenceModel.ssdl|res://*/PersistenceModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sqlexpress;initial catalog=TravPro;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
    <add name="RegularADONET" 
         connectionString="data source=.\sqlexpress;initial catalog=TravPro;integrated security=True;MultipleActiveResultSets=True;" 
</connectionStrings>

and then use that second, "regular" ADO.NET connection string for WebSecurity:

string con = ConfigurationManager.ConnectionStrings["RegularADONET"].ConnectionString;
WebSecurity.InitializeDatabaseConnection(con, "UserProfile", "UserId", "UserName", true);