SQL localdb shared instance login failed for user

Question

Edit: Solution

I have figured out a solution by creating a login. Now each user can access that same shared instance.

C:\Windows\system32>sqlcmd -S (localdb)\v11.0
1> use master
2> go
Changed database context to 'master'.
1> create login [NT AUTHORITY\Authenticated Users] from windows;
2> exec sp_addsrvrolemember N'NT AUTHORITY\Authenticated Users', sysadmin
3> go
1> exit

Hopefully it'll help others. If anyone has a better solution please let now.

---

I'm trying to create a shared instance SQL localdb that can be accessible by multiple users on the same system. Only problem that I'm facing is login error when trying to connect from different user account that does not owned that instance.

Step performed to create shared instance using admin cmd

C:\Windows\system32>sqllocaldb h v11.0 sharedv110
Private LocalDB instance "v11.0" shared with the shared name: "sharedv110".

C:\Windows\system32>sqllocaldb i
.\sharedv110
MSSQLLocalDB
v11.0

C:\Windows\system32>sqllocaldb i v11.0
Name:               v11.0
Version:            11.0.3000.0
Shared name:        sharedv110
Owner:              HOME-PC\usman
Auto-create:        Yes
State:              Running
Last start time:    11/8/2014 6:11:41 AM
Instance pipe name: np:\\.\pipe\LOCALDB#47BC4657\tsql\query

C:\Windows\system32>sqlcmd -S (localdb)\v11.0
1> use master
2> go
Changed database context to 'master'.
1> exit

C:\Windows\system32>sqllocaldb p v11.0
LocalDB instance "v11.0" stopped.

C:\Windows\system32>sqllocaldb s v11.0
LocalDB instance "v11.0" started.

C:\Windows\system32>sqlcmd -S (localdb)\.\sharedv110
1> use master
2> go
Changed database context to 'master'.
1> exit

https://i.stack.imgur.com/HXRkc.png (Administrative cmd screenshot)

Switching to a different user account and accessing the shared instance give login error.

C:\Users\UserA>sqllocaldb i
.\sharedv110
MSSQLLocalDB
v11.0

C:\Users\UserA>sqllocaldb i .\sharedv110
Name:               v11.0
Shared name:        sharedv110
Owner:              HOME-PC\usman
Instance pipe name: np:\\.\pipe\LOCALDB#SH9AA791\tsql\query

C:\Users\UserA>sqlcmd -S (localdb)\.\sharedv110
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user '
HOME-PC\UserA'..

C:\Users\UserA>

https://i.stack.imgur.com/gy6EQ.png (Second User cmd screenshot)

Trying to access from SSMS also gives error

So, is it supposed to happen or I'm doing something wrong here? Any help appreciated..

Thanks

Regards

Usman

Answer 1

I also encounter this issue and resolve by the reference : http://social.technet.microsoft.com/wiki/contents/articles/4609.troubleshoot-sql-server-2012-express-localdb.aspx

The "HOME-PC\UserA" should be be added as a db login user, after you created the shared instance.

CREATE LOGIN [HOME-PC\UserA] FROM WINDOWS;
GRANT CONNECT TO [HOME-PC\UserA];