MSSQLLocalDb - How to create custom authentication with localDb connection in ASP.Net MVC?

Question

I'm trying to make my own authentication service using MSSQLLocalDb. I have used this connection string to instantiate a local instance of the IdentityDbContext class.

connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-VeriSystems-20161025123601.mdf;Initial Catalog=aspnet-VeriSystems-20161025123601;Integrated Security=True;MultipleActiveResultSets=True

But whenever i'm publishing my project to IIS localhost i'm unable to execute the login service. And I get this error

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.
    )
Stack Trace: 


[SqlException (0x80131904): 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.
)]
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) +1418
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +470
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +70
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +945
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +114
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1637
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +117
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +267
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +318
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +132
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +246
   System.Data.SqlClient.SqlConnection.Open() +122
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +104
   System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) +442
   System.Data.Entity.SqlServer.<>c__DisplayClass33.<UsingConnection>b__32() +562
   System.Data.Entity.SqlServer.<>c__DisplayClass1.<Execute>b__0() +16
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +229
   System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act) +916
   System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +111
   System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +203
   System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) +129
   System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +172
   System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +110
   System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext) +118
   System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +284
   System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context) +185
   System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +72
   System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +502
   System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input) +177
   System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action) +273
   System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +38
   System.Data.Entity.Internal.Linq.InternalSet`1.Initialize() +77
   System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext() +21
   System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider() +56
   System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable`1 source, Expression`1 predicate, CancellationToken cancellationToken) +199
   System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable`1 source, Expression`1 predicate) +163
   Microsoft.AspNet.Identity.EntityFramework.<GetUserAggregateAsync>d__6c.MoveNext() +503
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
   Microsoft.AspNet.Identity.CultureAwaiter`1.GetResult() +48
   Microsoft.AspNet.Identity.Owin.<PasswordSignInAsync>d__29.MoveNext() +410
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
   VeriSystems.Controllers.<Login>d__11.MoveNext() +373
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
   System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) +93
   System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeAsynchronousActionMethod>b__36(IAsyncResult asyncResult) +22
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +42
   System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d() +72
   System.Web.Mvc.Async.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +386
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +42
   System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +38
   System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +186
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +38
   System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +29
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +65
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +53
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +38
   System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +44
   System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +65
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +38
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +399
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +157

I have used this code to implement the IdentityDbContext

 public class ApplicationUser : IdentityUser
    {
        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }

I want to know what i'm doing wrong. The following list is of the things I have tried:

1. I have tried configuring the IIS virtual Directory with no luck,i.e. tried changing the application pool to LocalSystem or LocalService instead of ApplicationPoolIdentity.

2.I changed the Virtual Directory to allow Read & execute permissions to both Desktop users and IIS Hosts.

Will appreciate any help.

Answer 1

Comparing your connection string to one here (that does work):

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Project.mdf

There is no backslash between the | and data file name.

Answer 2

the problem was that the private instance of MSSqlLocalDb was not shared over IIS. In order to achieve this we have to do the following steps :

1. Execute the following command in an administrator command prompt

   sqllocaldb share mssqllocaldb 'name of your choice'

2. create a custom login for the hosted app pool by executing this query in the SQL Server Object Explorer of Visual Studio in the Database of your choice

create login [IISAPPPOOL\'Application Pool name'] from windows;

exec sp_addsrvrolemember N'IISAPPPOOL\'Application Pool name', sysadmin

3. Modify the connection strings in your web.config files with the data source as

   connectionString="Data Source=(LocalDb).\'Instance name';AttachDbFilename=|DataDirectory|\project.mdf;Initial Catalog=project;Integrated Security=True;MultipleActiveResultSets=True