Why can't I connect to a SQL Server 2012 LocalDB shared instance?
I'm trying to set up a SQL Server 2012 LocalDB (RTM, x64) shared instance on my Windows 7 x64 machine and I can't seem to connect to the shared instance. I'm using an Administrator command prompt for all of the setup. Here's how I'm creating the instance:
sqllocaldb create MyInstance
Which yields the response:
LocalDB instance "MyInstance" created with version 11.0.
So far so good. Now I share the instance:
sqllocaldb share "MyInstance" "MySharedInstance"
Which results in:
Private LocalDB instance "MyInstance" shared with the shared name: "MySharedInstance".
Still looking good. At this point, I the info command yields:
.\MySharedInstance
MyInstance
v11.0
Connecting to the instance from the owner account (which is an admin) using both an admin or non-admin command prompt seems to work fine. Things come off the tracks, though, when I log in as a regular user (not a windows admin) and try to connect:
sqlcmd -S (localdb)\.\MySharedInstance
results in:
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Increasing the login timeout using the "-l" switch does not help. I can connect to the default v11.0 instance, which is not shared. The info command for the non-admin user yields the same as above except withouth "MyInstance" since it's a named instance owned by the admin user. The following command (which works for the admin user/instance owner):
sqllocaldb info ".\MySharedInstance"
also results in an error:
Windows API call "FileTimeToSystemTime" returned error code: -2147024809.
So the question is why can't my non-admin user connect to my shared instance? This seems to defeat the whole purpose of shared instances. And what's with the "sqllocaldb info" command throwing an error when I try to query about the shared instance?
Answer
ANOTHER EDIT
Cory, if you have previous versions of SQL Server installed (e.g. 2008), that is the version of sqlcmd you are using. In order to connect to LocalDb you need to be using the SQL Server 2012 version of sqlcmd. So your instructions to your users must ensure that they use the SQL Server 2012 version by running:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd -S "(localdb)\.\InstanceName"
This worked for me. What I haven't verified is whether this path and version of sqlcmd is available to users who have only installed the sqllocaldb.msi. Sorry but I don't have any naked machines without SQL Server 2012 installed (or with only previous versions installed) to try this out thoroughly. But please let me know if explicitly calling the 110 version of sqlcmd does the trick.
I think you may also be able to instruct users to alter their system variables so that the 110 versions come first (which IMHO should be the case automatically).
The FileTimeToSystemTime has been confirmed as a bug by one of Krzysztof's co-workers. So there is still no fix that I know of for non-owners to connect via sqllocaldb. But I've shown that both SSMS and sqlcmd can be made to work, so I hope that gets you closer to running.
EDIT
You need to add any non-owner users to the instance, e.g. CREATE LOGIN [MyDomain\OtherUser] FROM WINDOWS; and any appropriate permissions as well. In my test login was failing and generating the wrong error message (the "FileTimeToSystemTime" error message is a bug). You also need to GRANT CONNECT. Once you do this, you will be able to connect from the second user using Management Studio with this connection (the only one I tried):
(localdb)\.\MySharedInstance
But from sqlcmd, I still I get an error no matter how I try to connect:
sqlcmd -S "(localdb)\.\MySharedInstance"
sqlcmd -S ".\MySharedInstance"
sqlcmd -S "(localdb)\MySharedInstance"
sqlcmd -S "GREENHORNET\MySharedInstance"
sqlcmd -S ".\LOCALDB#SH04FF8A"
sqlcmd -S "GREENHORNET\LOCALDB#SH04FF8A"
All yield:
HResult 0xFFFFFFFF, Level 16, State 1 SQL Server Network Interfaces:
Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
Though I have verified that the instance is set to accept remote connections. So there is some other hoop that sqlcmd must be going through.
And regarding the sqllocaldb exe, how does this follow any logic? I can see the instance is there via info, I get a proper error message when I try to stop it, I get a message that it is [already] started when I try to start it, but I can't connect to it?
So unless you need sqlcmd access, in the short term I would have the secondary users do their thing with SSMS (once you've granted adequate permissions) and hopefully Krzysztof will have more info on the other items.
Regarding the 4.0.2 update, from http://connect.microsoft.com/SQLServer/feedback/details/723737/smo-cant-connect-to-localdb-instances:
We made an explicit decision not to include .NET Framework 4.0.2 in LocalDB installer. Installing the .NET Framework update would increase the size of the LocalDB installer and cause a likely reboot. Since LocalDB is built to be independent of the .NET, we didn’t think we should take this cost for every LocalDB installation. Future .NET versions (including .NET 4.5, now in CTP) will support LocalDB out of the box. Some developers may also want to opt in for ODBC, PHP Driver/PDO, and probably JDBC in the future. Those developers will not be interested in updating .NET.