次の方法で共有

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

  • Anonymous
    March 19, 2012
    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!

  • Anonymous
    March 20, 2012
    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    reconfigure    exec sp_configure    go 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    reconfigure    exec sp_configure 'user instance timeout'    go 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.

  • Anonymous
    March 20, 2012
    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.

  • Anonymous
    May 01, 2012
    The comment has been removed

  • Anonymous
    May 31, 2012
    you really think it is a smart idea to do exec sp_addsrvrolemember N'IIS APPPOOLASP.NET v4.0', sysadmin ?

  • Anonymous
    June 01, 2012
    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. Ward.

  • Anonymous
    September 24, 2012
    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 ... [snip] ... 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.

  • Anonymous
    November 18, 2012
    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.

  • Anonymous
    November 20, 2012
    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.

  • Anonymous
    December 05, 2012
    Hi, 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 ! Thanks

  • Anonymous
    August 13, 2013
    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: Graeme

  • Anonymous
    October 15, 2013
    The comment has been removed

  • Anonymous
    October 16, 2013
    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.

  • Anonymous
    November 04, 2013
    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!

  • Anonymous
    December 07, 2013
    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. ..

  • Anonymous
    May 26, 2014
    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"

  • Anonymous
    May 28, 2014
    The comment has been removed

  • Anonymous
    June 02, 2014
    The comment has been removed

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

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

  • Anonymous
    August 06, 2014
    It worked...thanks for your solution!!

  • Anonymous
    December 25, 2014
    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'.

  • Anonymous
    April 16, 2015
    Awesome article. A few years on and still helping people. Thanks

  • Anonymous
    August 05, 2015
    The comment has been removed

  • Anonymous
    September 01, 2015
    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

  • Anonymous
    September 02, 2015
    Hello,              Don't get me wrong can any body please tell is local server database can access out of server?

  • Anonymous
    September 09, 2015
    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.

  • Anonymous
    September 11, 2015
    What a great post and big help. I appreciated it

  • Anonymous
    September 30, 2015
    Thank you for this post. It was very helpful for me.

  • Anonymous
    October 23, 2015
    "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?