Unable to use InitializeDatabaseConnection() in MVC

Question

Whenever I try to run WebSecurity.InitializeDatabaseConnection(), it fails with the error

Unable to find the requested .Net Framework Data Provider. It may not be installed.

Here is the method call:

WebSecurity.InitializeDatabaseConnection("DataContext", "Users", "Id", "UserName", autoCreateTables: true);

Here's my database connection string (which I use in the MVC application and works fine):

<add name="DataContext" connectionString="metadata=res://*/Entities.DataModel.csdl|res://*/Entities.DataModel.ssdl|res://*/Entities.DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=(LocalDB)\v11.0;Initial Catalog=DBName;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

And here's my :

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
      <remove invariant="System.Data.SqlClient" />
      <add name="SqlClient Data Provider"
       invariant="System.Data.SqlClient"
       description=".Net Framework Data Provider for SqlServer"
       type="System.Data.SqlClient.SqlClientFactory, System.Data,
     Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    </DbProviderFactories>
  </system.data>

Here's the callback:

[ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.]
System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName) +1426271
WebMatrix.Data.DbProviderFactoryWrapper.CreateConnection(String connectionString) +64
WebMatrix.Data.<>c_DisplayClass15.b_14() +16 WebMatrix.Data.Database.get_Connection() +19 WebMatrix.Data.Database.EnsureConnectionOpen() +12
WebMatrix.Data.d__0.MoveNext() +66
System.Linq.Enumerable.FirstOrDefault(IEnumerable`1 source) +164
WebMatrix.Data.Database.QuerySingle(String commandText, Object[] args) +103 WebMatrix.WebData.DatabaseWrapper.QuerySingle(String commandText, Object[] parameters) +14
WebMatrix.WebData.SimpleMembershipProvider.CheckTableExists(IDatabase db, String tableName) +57
WebMatrix.WebData.SimpleMembershipProvider.CreateTablesIfNeeded() +49 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
UPI.Web.MvcApplication.Application_Start() in c:\Users\danderson\Documents\UnitedPiping\LEMTracking\trunk\Source\Web\Global.asax.cs:36

[HttpException (0x80004005): Unable to find the requested .Net Framework Data Provider. It may not be installed.]
System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +9860225
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +118
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +172
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +336
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +296

[HttpException (0x80004005): Unable to find the requested .Net Framework Data Provider. It may not be installed.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9874568 System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +254

EDIT:

After following allo_man's instructions (sorta--that exact code didn't work so I looked up the class on MSDN and used their example), I'm getting a new error. "Illegal characters in path."

  // Specify the provider name, server and database. 
  string providerName = "System.Data.SqlClient";
  string serverName = "(LocalDB)\v11.0";
  string databaseName = "aoeu";

  // Initialize the connection string builder for the 
  // underlying provider.
  SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

  // Set the properties for the data source.
  sqlBuilder.DataSource = serverName;
  sqlBuilder.InitialCatalog = databaseName;
  sqlBuilder.IntegratedSecurity = true;

  // Build the SqlConnection connection string. 
  string providerString = sqlBuilder.ToString();

  // Initialize the EntityConnectionStringBuilder.
  EntityConnectionStringBuilder entityBuilder =
      new EntityConnectionStringBuilder();

  //Set the provider name.
  entityBuilder.Provider = providerName;

  // Set the provider-specific connection string.
  entityBuilder.ProviderConnectionString = providerString;

  // Set the Metadata location.
  entityBuilder.Metadata = @"res://*/Entities.DataModel.csdl|
                    res://*/Entities.DataModel.ssdl|
                    res://*/Entities.DataModel.msl";

  //var builder = new EntityConnectionStringBuilder();
  //string sqlConnString = builder.ConnectionString;
  WebSecurity.InitializeDatabaseConnection(entityBuilder.ConnectionString, "Users", "Id", "UserName", autoCreateTables: true);

Trace:

[ArgumentException: Illegal characters in path.]
System.IO.Path.CheckInvalidPathChars(String path, Boolean checkAdditional) +10667574 System.IO.Path.Combine(String path1, String path2) +42
WebMatrix.Data.ConfigurationManagerWrapper.GetConnection(String name, Func2 getConfigConnection, Func2 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() +149
WebMatrix.WebData.SimpleMembershipProvider.ConnectToDatabase() +56
WebMatrix.WebData.SimpleMembershipProvider.CreateTablesIfNeeded() +67 WebMatrix.WebData.WebSecurity.InitializeMembershipProvider(SimpleMembershipProvider simpleMembership, DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean createTables) +169
WebMatrix.WebData.WebSecurity.InitializeProviders(DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables) +99
WebMatrix.WebData.WebSecurity.InitializeDatabaseConnection(String connectionStringName, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables) +100
UPI.Web.MvcApplication.Application_Start() in c:\Users\danderson\Documents\UnitedPiping\LEMTracking\trunk\Source\Web\Global.asax.cs:73

Answer 1

Apparently, Entity doesn't work for this, even using the class allo_man suggested. It works, however, if I add another connection string to Web.config that uses System.Data.SqlClient and then use that with InitializeDatabaseConnection()

Answer 2

You cannot use an EntityFramework connection with WebSecurity.InitializeDatabaseConnection. If you want to have the same connection string you can use the EntityConnectionSTring Builder

var builder = new EntityConnectionStringBuilder(entityConnectionString);
string sqlConnString = builder.ConnectionString;

Then initialise SimpleMembershipProvider with that connection string

WebSecurity.InitializeDatabaseConnection(sqlConnString, "User", "UserId", "UserName", autoCreateTables: true);

Source

Answer 3

In previous versions of SQL CE you had to install both version, 32 and 64 bits in order to work.. Check in your machine.config file that the SQL CE data provider is registered properly.