1

I am using localDB 2012 to do CRUD operations on a Db. I want the DB to be shared across the users on the system. Hence I have done the following.

`

sqllocaldb.exe create myinstance
sqllocaldb.exe share myinstance myinstance.shared
sqllocaldb.exe start myinstance
... added SQL admin user to the instance to access from all the users

`

I have put this stuff in the installer of the application which uses the NT AUTHORITY\ADMIN account to do that.

Then connected from my application to local db using entity framework, and the shared instance path 

`(localDb)\.\myinstance.shared; with attached file; and new SQL admin credentials`

This connection does work initially for a while and strangely after some time it stops working! This has happened on 3 PCs, so it is a repeatable scenario.

I am getting a following messages: localdb named pipes provider could not open the connection Connection could not be established.

If anyone has encountered this issue / knows about it, please help.

krafty
  • 425
  • 4
  • 16

2 Answers2

4

Note: You really want to use regular SQL Server Express here, which runs as a service and never goes away. See this post for comparison between LocalDB and regular SQL Server Express.

LocalDB instance is not a service, it is a regular Windows process. It belongs to a user, can only be started by this user, and shuts down after some period of inactivity. Shared instance can be connected to by other users (only locally), but they will not be able to start it if it is shut down.

So what happens in your case is that the instance is created and started by the setup process, owned by whoever ran the setup, and shared with other users. They can connect to it and do their work initially. As soon as nobody is using it for long enough the instance shuts down. From now on it is inaccessible from any users, until the owner starts it again.

Community
  • 1
  • 1
Krzysztof Kozielczyk
  • 5,887
  • 37
  • 28
  • That was my conclusion as well :-) I was trying to stretch the envelope of LocalDb, but allowing sharing of instances got me thinking it will be supported. I have since moved to SQLExpress... Thanks – krafty Jul 07 '14 at 05:52
  • @KrzysztofKozielczyk, is there a way to stop a localDB instance from shutting down due to inactivity? I'm using version 12. – Zephyr was a Friend of Mine Feb 12 '15 at 20:37
  • Yes, there seem to be a few options in those links that I could try. But maybe I'm having a different problem. I had a localDB instance running and suddenly received a connection failure. Running `sqllocaldb info myinstance` showed that it was stopped. According to the links, the instance should have started up when it received a connection request. That didn't happen. – Zephyr was a Friend of Mine Feb 12 '15 at 21:06
  • I kind of see that this is a problem with having shared the instance. It's not starting up, because the client connection does not own it. I'm going to try preventing the `auto_close` feature and seeing if that helps. – Zephyr was a Friend of Mine Feb 12 '15 at 21:45
0

I'm super late but this post helped me figure out a way to solve it so might as well reply for other people out there.

I had the same problem and it took me a while to figure out that the user that owns the instance is actually closing the connection.

Anyway here is what I did in my case. It looks like the instance is per user, so basically I created an instance (in my case MSQLLocalDB) with the same name for every user that needs to access the database. I also placed the mdf file in a location that all the users can access (C:\Program Data). I think you can just create the instance using the SqlLocalDB.exe create "instance name". I actually installed the localdb again to the the user where I initially wanted to share the instance to. This would have created a separate instance of the default instance (MSSQLLocalDB).

Boggs
  • 133
  • 9