Web API and ASP.Net Identity with SQL Server and not LocalDB

Question

How to set ASP.Net Identity to use SQL Server database and not LocalDB?
How to customize ASP.Net Identity to use a custom data layer?

Using Microsoft.AspNet.Identity.Core 1.0.0 and EntityFramework 6.0.0 in Web API .Net 4.5

Web.config changes:

<connectionStrings>
<add name="myAppDB"
     connectionString="data source=.;Initial Catalog=mydb1;Integrated Security=SSPI;"
     providerName="System.Data.SqlClient" />
</connectionStrings>

Error when calling api/account/register

{"Message":"An error has occurred.","ExceptionMessage":"An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.","ExceptionType":"System.Data.Entity.Core.ProviderIncompatibleException","StackTrace":"   at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerSeres.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()","InnerException":{"Message":"An error has occurred.","ExceptionMessage":"The provider did not return a ProviderManifestToken string.","ExceptionType":"System.Data.Entity.Core.ProviderIncompatibleException","StackTrace":"   at System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection)
   at System.Data.Entity.Utilities.DbProviderServicesExtensions.GetProviderManifestTokenChecked(DbProviderServices providerServices, DbConnection connection)","InnerException":{"Message":"An error has occurred.","ExceptionMessage":"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.
)","ExceptionType":"System.Data.SqlClient.SqlException","StackTrace":"   .....

And Windows Application event log

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

@BrendanGreen I've updated the question with more detail. And the connection is correct because it's the connection that I'm using in my unit-test application. — Babak, Feb 17 '14 at 04:27
What connectionString is the aspnet.identity dbcontext using? — Brendan Green, Feb 17 '14 at 04:42
If you have not changed the basic configuration used by the MVC 5 Internet Template the name of the connection string for ASP.NET Identity is "DefaultConnection". Try creating a connection string with this name and configure it to point to sql server. — Kevin Junghans, Feb 17 '14 at 16:06
@Kevin I'm using WebAPI template and not MVC. Should I use that? — Babak, Feb 18 '14 at 05:21
@Babak - Please see the answer I posted. A proper response could not be done in comments. — Kevin Junghans, Feb 18 '14 at 13:48

score 6 · Accepted Answer · answered Feb 18 '14 at 13:45

If you have not changed the basic configuration used by the MVC 5 Internet Template the name of the connection string for ASP.NET Identity is "DefaultConnection". Try creating a connection string with this name and configure it to point to sql server.

If you used the Web API template then the default is set to "No Authentication" as shown in the picture below.

Web API Template without Authentication

If "No Authentication" is selected then the appropriate assemblies for ASP.NET Identity are not installed, an account controller is not created and the web.config is not updated with the connection string. Notice that MVC is still included in the template.

If you want to use ASP.NET Identity select "Change Authentication" and select "Individual Accounts"

Web API Template with Authentication

Now you have all the required assemblies, ASP.NET Identity is wired in the start-up configuration, and the default connection string is in your web.config.

  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=  (LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-TestWebApiTemplateWithAuth20140218082219.mdf;Initial Catalog=aspnet-TestWebApiTemplateWithAuth- 20140218082219;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Notice that you are also configured for Entity Framework and have the correct assemblies for that. By default ASP.NET Identity uses EF code-first for the data store. The default is set to use a local DB. Just change this connection string to use a SQL Server Database.

If you want to change the underlying data store to something other than SQL Server there is a tutorial you can follow here. This specific tutorial shows how to change it to MySql.

Check out these QA's on changing the connection string name. http://stackoverflow.com/questions/18297052/asp-net-identity-how-to-set-target-db http://stackoverflow.com/questions/19813374/asp-net-identity-in-mvc5-set-up-connection-string — Kevin Junghans, Feb 20 '14 at 13:19

Web API and ASP.Net Identity with SQL Server and not LocalDB

1 Answers1