Using LocalDB with Full IIS, Part 2: Instance Ownership

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Pierre-Henri Barralis — Thu, 06 Dec 2012 11:13:22 GMT

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

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Ward Beattie [MSFT] — Tue, 20 Nov 2012 21:24:13 GMT

Dave Sexton: could you provide the error.log from \users\MyName\appdata\local\microsoft\microsoft sql server local db\instances\v11.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.

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Gregory — Sun, 18 Nov 2012 23:42:23 GMT

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.

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Dave Sexton — Tue, 25 Sep 2012 13:52:00 GMT

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.

Any ideas? Thanks.

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Ward Beattie [MSFT] — Fri, 01 Jun 2012 16:29:08 GMT

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.

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Giant Cookie — Fri, 01 Jun 2012 08:28:56 GMT

you really think it is a smart idea to do

exec sp_addsrvrolemember N'IIS APPPOOL\ASP.NET v4.0', sysadmin

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Pleasant Holiday — Tue, 01 May 2012 23:00:06 GMT

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

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Ward Beattie [MSFT] — Wed, 21 Mar 2012 01:52:34 GMT

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.

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Ward Beattie [MSFT] — Tue, 20 Mar 2012 17:22:15 GMT

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

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'

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.

re: Using LocalDB with Full IIS, Part 2: Instance Ownership

Josh — Tue, 20 Mar 2012 06:14:00 GMT

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!