Using LocalDB with Full IIS, Part 2: Instance Ownership

This is the second post in a two-post mini-series on using LocalDB with Full IIS. Don’t miss the first post.

Quick Recap

In the first post of this mini-series we said there are two properties of LocalDB causing problems when running under full IIS:

  • LocalDB needs user profile to be loaded
  • LocalDB instance is owned by a single user and private (by default)

We saw how to properly load the user profile, now it’s time to tackle the problem of LocalDB instance ownership.

The Problem of the Private Instance

At the end of the previous post we left our Web Application in this state:

As we can see we are facing the following error:

System.Data.SqlClient.SqlException: Cannot open database “OldFashionedDB” requested by the login. The login failed.
Login failed for user ‘IIS APPPOOL\ASP.NET v4.0’.

This time the error is quite clear. LocalDB was started and the Web Application was able to connect to it, but the connection was then terminated due to login failure. The ApplicationPoolIdentity account for the IIS application pool (in this case IIS APPPOOL\ASP.NET v4.0) couldn’t login to LocalDB instance because the database specified in the connection string (OldFashionedDB) wasn’t found. How odd, since connecting from Visual Studio with the same connection string succeeds!

(In this screenshot I am using SQL Server Object Explorer, which is introduced by SQL Server Data Tools. As you can see it adds SQL Server tools right inside Visual Studio 2010.)

How is it possible that Visual Studio connects to LocalDB just fine, while the connection from Web Application fails? In both cases the connection string is the following:

Data Source=(localdb)\v11.0;Initial Catalog=OldFashionedDB;Integrated Security=True

The answer is that there are two different LocalDB instances here. Unlike SQL Server Express instances, which are running as Windows services, LocalDB instances are running as user processes. When different Windows users are connecting to LocalDB, they will end up with different LocalDB processes started for each of them. When we connect to (localdb)\v11.0 from Visual Studio, a LocalDB instance is started for us and runs as our Windows account. But when Web Application, running in IIS as ApplicationPoolIdentity, is connecting to LocalDB, another LocalDB instance is started for it and is running as ApplicationPoolIdentity! In effect, even though both Visual Studio and Web Application are using the same LocalDB connection string, they are connecting to different LocalDB instances. Obviously the database created from Visual Studio on our LocalDB instance will not be available in Web Application’s LocalDB instance.

A good analogy to this is My Documents folder in Windows. Say we open Visual Studio and create a file in our My Documents folder. Then we login to the same machine as a different user and go to My Documents folder again. We won’t find the file there as My Documents of the second user and our My Documents are two different folders. Similarly LocalDB instances (localdb)\v11.0 owned by two different users are two different processes with two different sets of databases.

This is also the reason the Web Application was able to connect to LocalDB from IIS Express. Just like LocalDB, IIS Express is a user process. It is started by Visual Studio and runs as the same Windows account as the Visual Studio process. Two different processes running as the same Windows account (Visual Studio and IIS Express, both running as our Windows account) connecting to (localdb)\v11.0 are connecting to the same LocalDB process, also started as the same Windows account.

Possible Solutions

Understanding the nature of the problem brings multiple approaches to solving it. As different approaches have different tradeoffs, instead of prescribing one solution, below I presented three approaches that seem most viable to me. My hope is to hear from you about the one that worked best for you! Here is the list:

Let’s take a closer look at each of them.

Approach 1: Run IIS as our Windows user

If different user accounts are the problem, why not try to run our Web Application under our Windows account? Web Application would connect to the same LocalDB as Visual Studio and everything should just work.

Making the configuration change is relatively easy, just start IIS Manager and find the right Application Pool:

Open Advanced Settings screen (available in the context menu):

Click the little button in the Identity property to bring up the Application Pool Identity screen:

Starting the Web Application again will confirm that the problem is solved:

What are the drawbacks of this approach? Of course running Web Application under our account brings certain security risks. If someone hijacks our Web Application they will be able to access all system resources our account can. Running the Web Application as ApplicationPoolIdentity provides additional protection since ApplicationPoolIdentity accounts have very limited access to local system resources. Therefore I cannot recommend this approach in general, but when used with care it is a viable option in some cases.

Approach 2: Use LocalDB Shared Instance

We could also use an instance sharing feature of LocalDB. It allows us to share a LocalDB instance with other users on the same machine. The shared instance will be accessible under a public name.

The easiest way of sharing an instance is to use SqlLocalDB.exe utility. Just start an administrative command line prompt, and type the following command:

sqllocaldb share v11.0 IIS_DB

It will share the private LocalDB instance v11.0 under the public name IIS_DB. All users on the machine will be able to connect to this instance, using (localdb)\.\IIS_DB as a server address. Note the \. before the instance name, indicating this is a shared instance name. We should replace the connection string in our Web Application with an updated one:

Data Source=(localdb)\.\IIS_DB;Initial Catalog=OldFashionedDB;Integrated Security=True

Before the shared instance can be used by the Web Application we need to start it and create logins for the ApplicationPoolIdentity. Starting the instance is easy, simply connecting to it from SQL Server Object Explorer will start it and keep it alive. Once we are in the SQL Server Object Explorer we can also create the login for ApplicationPoolIdentity. We could use the following query:

create login [IIS APPPOOL\ASP.NET v4.0] from windows;
exec sp_addsrvrolemember N’IIS APPPOOL\ASP.NET v4.0′, sysadmin

This script gives full administrative access to our LocalDB instance to the ApplicationPoolIdentity account. Whenever possible, I would recommend using more limited, database-level or even table-level permissions.

Now we can run our Web Application again. This time it should work just fine:

What are the drawbacks of this approach? The main one is that, before Web Application can connect to the shared instance, we need to make sure the instance is started. For that to happen the Windows account that owns the instance must connect to it and the connection must be kept open, or the LocalDB instance will shut down.

Approach 3: Use full SQL Server Express

Since full IIS runs as a service, maybe using traditional, service-based SQL Server Express is the right approach? We could just install SQL Server 2012 Express RC0 and create the OldFashionedDB database in it. We can even use our brand new SQL Server Data Tools to do it, as it works with any SQL Server version and edition. Our connection string would have to change to:

Data Source=.\SQLEXPRESS;Initial Catalog=OldFashionedDB;Integrated Security=True

Of course, just as in the previous case, we would need to make sure the ApplicationPoolIdentity account has access to our SQL Server Express instance. We can use the same script as previously:

create login [IIS APPPOOL\ASP.NET v4.0] from windows;
exec sp_addsrvrolemember N’IIS APPPOOL\ASP.NET v4.0′, sysadmin

After that, running our Web Application brings the happy picture again:

What are the drawbacks of this approach? Obviously we lose the benefits of using LocalDB. Installing SQL Server Express may take more time than LocalDB, and there may be some machine cleanup necessary for it to succeed. SQL Server Express Setup can be blocked by problems like corrupt WMI database, polluted registry or components left by SQL Server or Visual Studio CTPs and Betas. And SQL Server Express will continue running in the background even when not needed, as services do.

Other options

There are other approaches of using LocalDB under full IIS that are not covered here. We could embrace the Web Application’s private LocalDB instance and communicate with it through the Web Application by executing T-SQL scripts from ASP.NET code. We could also use AttachDbFileName option of ADO.NET connection strings and use a database file (.mdf) that would be attached to both our LocalDB during development and Web Application’s LocalDB for debugging. I tried both I found them too cumbersome to discuss further.

Try It Now!

We are eager to hear your feedback on LocalDB. Please share your thoughts with us. Did any of the approaches presented in this post work for you? Did you figure out a better one? Let us know!

You may also want to see other posts and materials about LocalDB:

– Krzysztof Kozielczyk

Share Your Feedback

Please share your feedback with us! Just start a thread on SQL Express Forum, hit the “Email Author” button on this post, or file a Connect item!

Comments (30)

  1. Josh says:

    Thank you very much for these posts. This has helped me get my site working.

    I am having an issue with the localdb instance not starting automatically.

    I have a WCF Data Service connected to a localdb shared instanced. Things all work fine when the localdb instance is running. After some time of inactivity, the localdb instance stops automatically. When the next request comes in through the data service, it does not ever start the localdb instance.

    Any help on this would be greatly appreciated. Thanks!

  2. By default, a LocalDB instance will terminate after 5 minutes with no connections. This can be changed, as follows:

    1. Connect to the instance as sysadmin.

    2. Run the following batch to show advanced options:

       exec sp_configure 'show advanced options',1


       exec sp_configure


    3 Run the following batch to change the timeout to 65535. This value is in seconds, but 65535 is the magic number meaning infinite:

       exec sp_configure 'user instance timeout',65535


       exec sp_configure 'user instance timeout'


    Once this is done, you will need to shut down the instance using SqlLocalDB.exe, or by connecting and using the T-SQL shutdown command. The instance will also shut down if the user that started it (and owns it) logs off.

  3. Correction: in my earlier post, I said that the timeout value was in seconds. It is actually in minutes (for values other than 65535, which still means "infinite"). Sorry for any confusion.

  4. Pleasant Holiday says:

    Hello and thank you for this post!!!

    I am trying to use Approach 2 (Use LocalDB Shared Instance) with one twist — my application running on my local IIS box needs to run under a network domain account.  (This duplicates the way our servers are set up, which is done so that various network actions are allowed.)

    I finally go this approach to work by realizing that the instance name I need to use is not actually "v11.0".  I am still new to SSDT and LocalDB, and I am not sure why or how my SSDT database project was deployed to a an instanced named (more or less) for my database project.  Once I shared that out *and* set that domain account as the server administrator, then I was able to connect.

    Your excellent open source tool at codeplex (localdbcompanion) was very interesting to look through, but I am puzzling through how that tool can be used if the instance name is not in fact always "v11.0".  

    Any thoughts you have on any of this would be great.

    Thanks, and thanks again for this post.  I didn't have a clue how to integrate a database project into my legacy web applications until I finally found this post.  Am I correct to wonder if I'd come out farther ahead if I just took the time to convert them from full IIS "web site projects" into web application projects (using the built-in IIS of Visual Studio)?

    Pleasant Holiday

  5. Giant Cookie says:

    you really think it is a smart idea to do

    exec sp_addsrvrolemember N'IIS APPPOOLASP.NET v4.0', sysadmin


  6. Hi Giant Cookie

    If you are referring to the fact that this gives full administrative access to the App Pool identity, then we would agree that this is definitely not the right thing to do in a production environment. However, this post was aimed at the developer who needs to use SQL Server on a development machine with full IIS. Security is a very large subject, and attempting to address it in this context would have made the post much more complex and perhaps less helpful.


  7. Dave Sexton says:

    Thanks for this post, but I'm still getting the same login failure with the first option.  I'm logged in as myself to Windows 8.  I've set my site's AppPool Identity in IIS to my account.  SQL Server Object Explorer in Visual Studio 2012 shows my table in (localdb)v11.0.  When I run in debug mode on the VS Dev Server my app connects to the DB successfully and it displays the data.  I published the site to my local IIS.  I've even enabled Windows authentication in web.config and verified that WindowsPrincipal.Current.Identity returns my account name (I printed it to the home page).  But I'm getting this exception when running in IIS:

    Message: The underlying provider failed on Open.

    StackTrace:    at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf


    InnerException: System.Data.SqlClient.SqlException

    Message: Cannot open database "MyDBName" requested by the login. The login failed.

    Login failed for user 'MyCompMyName'.

    Any ideas?  Thanks.

  8. Gregory says:

    Do you think it would be possible to create new instance of LocalDB as ApplicationPoolIdentity

    or change existing localDB instance owner? I wish to host localDB on my server without

    the need to connect to it with visual studio.

  9. Dave Sexton: could you provide the error.log from usersMyNameappdatalocalmicrosoftmicrosoft sql server local dbinstancesv11.0? If it is easier, you can send email.

    Gregory: this can be made to work, however, you cannot use Windows Authentication for your site. You must also ensure (via app pool options) that the user profile for your app pool identity is loaded.

  10. Pierre-Henri Barralis says:


    I am using shared instance (solution 2) localDB, and everything is working except, I can't start the instance if I'm not logged with the owner of the instance. What is the purpose of sharedInstance if we can't use it with multiple user account ? Is there a solution to this problem ?

    When I start the instance from the owner, then I switch to an other user, it works  ….  

    Please help me !


  11. Graeme says:

    These are both very good posts.  Use of an instance however comes very close to using SQL Express in the first place.  One of the more interesting options here would be to attach using the AttachDbFileName= option on the connection string.  That way the application can ship with the database.

    However When I do this I of course get the error

    Login failed for user 'IIS APPPOOLsomething'

    where 'something' is the name of the app pool.

    Kind of makes sense as it is the instance that manages login not the database itself but in this case the instance IS running as that app pool identity so how exactly do I tell the database what the permissions are for this identity?

    am I being dumb?  there are no other posts I can find that cover it.  

    Even if I run the SP to add the sysadmin role per the post I still fail the login.  I am sure the role exists if I was actually able to authenticate.

    Confused but hopeful:


  12. says:

    Great post.

    Was having this issue with a web api application and saved me a lot of head-scratching.

  13. MCKLMT says:

    After launched the command to share the instance, you should stop and start the main instance that the modifications be saved and the shared instance be accessible.

  14. Phil says:

    I thought using localDB was supposed to be easy!  I tried everything I could find.  Never got past the first part.  Very discouraging working hard to create an app, thinking you're home free, try to run it from IIS and SPLAT!

  15. AA says:

    Thank you!  You helped me solve my issue with LocalDB!  My local site is now working!  The only reason I installed IIS today was because I needed to have my site go SSL and I main way online I read to do this is with IIS.  But I couldn't get my application to work, I almost had given up hope when I ran across both your articles. ..

  16. Halabi says:

    In approach 2, the user must restart the instance to get the shared instance working, you can restart by "sqllocaldb stop instance" then "sqllocaldb start instance"

  17. Novaterata says:

    I have a Topshelf service that accesses a named localdb instance and an MVC web service that accesses the same named instance. Both process and AppPool run as the same user and load user profile and all that is already set. Everything works when the product is installed and both of these are running as LocalSystem by default, but as long as the users are the same, this works. This setup also works when debugging using IIS Express; however, when I switch to Full IIS while debugging (using the same user on the AppPool of course) I get the usual Error 50s one would expect if the AppPool was running as a different user or a user without permissions. I'm really scratching my head on this one. This even occurs when the processor is not running and I'm only debugging the MVC project, and also occurs using v11.0 with no other instances running. So the problem seems to be I can't connect to LocalDB why debugging Full IIS.

  18. Novaterata says:

    Turns out the culprit was Windows 7 SP1 setProfileEnvironment="true" not being set by the command line tool. Editing the applicationHost(s?).config file was not enough in this case, presumabely because the location had changed, I don't know. Running as LocalSystem does not require this flag apparently, and we normally test on 2k8r2.

  19. Mark says:

    On my Windows 7 x64 the relevent domainaccount is [IIS APPPOOLDefaultAppPool], not [IIS APPPOOLASP.NET v4.0]  

  20. Robert says:

    Hello, i've changed the app. pool user with the administrator user but still get the same error ! why?

  21. Anuj Jain says:

    It worked…thanks for your solution!!

  22. Ananth Kumbla says:

    Hey I am using Approach 2

    I get the following error

    Cannot attach the file 'C:inetpubwwwrootMvcApplication26App_Dataaspnet-MvcApplication26-20141226150050.mdf' as database 'aspnet-MvcApplication26-20141226150050'.

  23. GrazyGee says:

    Awesome article. A few years on and still helping people.


  24. I'm using approach #2.

    When I try to create the login as follows:

    create login [IIS APPPOOLDTC v4.0] from windows;

    exec sp_addsrvrolemember N'IIS APPPOOLDTC v4.0', sysadmin

    I get the following errors:

    Msg 15401, Level 16, State 1, Line 1

    Windows NT user or group 'IIS APPPOOLDTC v4.0' not found. Check the name again.

    Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 33

    'IIS APPPOOLDTC v4.0' is not a valid login or you do not have permission.

    Can anyone help?

  25. NotMoreChanges says:

    I agree with GrazyGee. Great article. I'm not sure people who have been helped by this article can truly convey the appreciation they feel for the help you have offered.

    It's a shame that one or two (ok, we all know it's one) of the comments seem more about seeming clever than offering constructive help.

    Thanks a lot

  26. Muni says:


                 Don't get me wrong can any body please tell is local server database can access out of server?

  27. Tom Wesson says:

    Thank you for this very much needed article.  One thing I like about open source is that complexity is not introduced for the sake of licensing.  You have helped shine a light on how to manage that complexity for those of us that must deal with SQL Server's various incarnations.  This obviously helps us spend more time on what we're actually good at, and less time on things we rarely have to deal with.  Thank you again.

  28. John says:

    What a great post and big help. I appreciated it

  29. Cerbundo says:

    Thank you for this post. It was very helpful for me.

  30. Andrew says:

    "My hope is to hear from you about the one that worked best for you!"…

    The first option works just fine!  Thank you so much.

    Security is not an issue for me.  This is just my own dev environment.

    Tiny tip for others.  When you specify your custom credentials you need domainusername setting up for the default pool.  Might be obvious for others?