27

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?

Cory McCarty
  • 1,375
  • 4
  • 15
  • 23
  • Are you talking about a non-admin Windows user? So you have logged out and logged in as a different user? Or is the user trying to connect from a different machine? – Aaron Bertrand Apr 18 '12 at 17:37
  • Logged out and logged back in as a different user. I have no need to connect from a different machine. – Cory McCarty Apr 18 '12 at 17:55
  • Are you sure the instance persists beyond a logout event? What happens if you log out and log back in as the admin user? I suspect you'll hit the same problem. – Aaron Bertrand Apr 18 '12 at 18:00
  • Yes, it definitely persists. I just logged back in (after a reboot) and connected just fine as the primary user. Seems like there wouldn't be much point in a shared instance (or really any instance) if it wasn't persistent across logouts. – Cory McCarty Apr 18 '12 at 18:03
  • That depends on your interpretation of the purpose of LocalDB. It is not supposed to be a permanent instance of SQL Server, it is supposed to be an on-demand engine. My interpretation of that is it is made available when I am actively developing against it. How many different users do you typically have developing against the same database on the same machine? – Aaron Bertrand Apr 18 '12 at 18:09
  • Well, my understanding is that it's also supposed to be useful as an embedded database alternative to compact edition (which is how I'd like to use it). We're using SQL Express right now, but it's a pretty big download for some of our users and incurs more of a support burden in getting the permissions set up so non-admin users can run our app against it. If LocalDB shared instances work as advertised, it would make a perfect replacement since it runs in user mode and is only a 33MB download. – Cory McCarty Apr 18 '12 at 18:18
  • Don't you have to explicitly share it to each user? Have you tried `sqllocaldb share "otheruser" "MyInstance" "MySharedInstance"`? – Aaron Bertrand Apr 18 '12 at 18:20
  • It'll let you do that, but connecting to ".\MySharedInstance" in that case always fails. I believe it's actually supposed to be 'sqllocaldb share "instance_owner" "MyInstance" "MySharedInstance"'. If you don't specify instance_owner, it uses the currently logged in user. – Cory McCarty Apr 18 '12 at 18:28
  • Another possibility: do you have the .NET 4.0.2 update (which eliminated the need to use the pipe in your connection strings) and is it possible you are running RTM SqlLocalDb alongside a pre-RTM core engine (e.g. CTP3 or RC0)? – Aaron Bertrand Apr 18 '12 at 18:43
  • I'm not 100% sure whether I have the .NET 4.0.2 update installed. I think I'd figured it would have installed with one of the versions of SQL Server 2012 I have installed (LocalDB, Express, and Developer). I also figured that my app (which works when run as the instance owner) wouldn't work without the update installed. I'm just using the regular name "(localdb)/./InstanceName" in my connection string. I can try installing the update manually to see if anything changes. I never installed any of the pre-release versions of SQL Server. – Cory McCarty Apr 18 '12 at 18:58
  • If I remember right the pipe was only necessary when accessing as a different user than the owner. But again, I can't seem to track down my notes from that session. And I can't seem to get sqlcmd or sqllocaldb to connect with or without the pipe. – Aaron Bertrand Apr 18 '12 at 19:00
  • Ok, I installed the .NET update and there was no change. I even tried unsharing and resharing the instance. I got all the same errors. – Cory McCarty Apr 18 '12 at 19:22
  • I am trying to get further information from my MVP channels. Hope to have something for you soon. – Aaron Bertrand Apr 18 '12 at 22:08
  • `sqlcmd` uses ODBC and have no dependency on .NET Framework, so .NET 4.0.2 update is not needed for it to work. Can you try deleting the `MyInstance` instance and creating it again? – Krzysztof Kozielczyk Apr 19 '12 at 05:07
  • Krzysztof, I deleted MyInstance, recreated it, and reshared it. The results were identical to before. I can connect from the owner/admin account, but not from the regular user account. I get the same Windows API call error when I run "sqllocaldb info .\MySharedInstance" as the non-admin user.
     – Cory McCarty Apr 19 '12 at 12:09
  • Also, I tried creating an instance as the non-admin user and sharing it as an admin. The admin gets the same error when trying to connect to that shared instance via sqlcmd. – Cory McCarty Apr 19 '12 at 12:15
  • Ok, here's another additional piece of information: If I leave the instance owner (admin user) logged in AND connected to the shared instance via sqlcmd, and just switch users to the non-admin user, I get a different error when I try to connect via sqlcmd: `Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'MACHINE_NAME\UserTest'..` – Cory McCarty Apr 19 '12 at 12:23
  • @KrzysztofKozielczyk, could you confirm whether the following is true? If the instance is already running, another user can connect to it IF that user has been added to the instance. If the instance is running, but the user is not added to the instance, you get the error in my previous comment. But, localdb will not start the instance for anybody other than the owner, and you get the incredibly cryptic error from my OP. This makes shared instances virtually useless for anything other than debugging software that runs under another user ID. Is there any way to fix this? – Cory McCarty Apr 19 '12 at 12:33
  • 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. – Aaron Bertrand Apr 19 '12 at 12:34
  • @Aaron, yeah see my comment right above yours. I seem to have figured out most of what's going on (though the Windows API error still seems like a bug). I'm hoping Krzysztof can suggest a way to make it work, because it's hard to imagine that shared instances would be an advertised feature if it's as gimped as it seems to be. – Cory McCarty Apr 19 '12 at 12:42
  • I agree the API error is a bug - wrong error message is coming back. Now after adding the second user and granting connect, the error message is "Error Locating Server/Instance Specified [xFFFFFFFF]". Even after manually starting it (it was already started) and verifying connectivity by sqllocaldb, sqlcmd still seems unwilling to connect. I must be forgetting the secret handshake. – Aaron Bertrand Apr 19 '12 at 12:43
  • @CoryMcCarty Your statement is correct. Only the instance owner can start the instance, including shared instances. We're trying to emphasize that LocalDB is for single-user scenarios. Shared Instances are meant for testing and debugging web applications where the application often runs under different credentials (IIS) than the developer (VS). What worries me even more is the `FileTimeToSystemTime` error. This is a separate issue related to registry corruption, that's why I suggested recreating the instance. – Krzysztof Kozielczyk Apr 19 '12 at 17:13
  • @KrzysztofKozielczyk, thank you for your input here. I hope you guys will consider allowing automatic start for all users of a shared instance in the future. LocalDB seems like a fantastic alternative to Express for distribution/user-mode and to Compact for performance and functionality. But I can't imagine I'm the only developer whose Windows app needs to share data between users. Maybe fully shared instances don't need to be the default behavior, but a way to enable it seems to make good sense. – Cory McCarty Apr 19 '12 at 19:38
  • Cory, did you see my updated answer? Using the 11.0 sqlcmd after switching users worked for me. I did not test what happens if the admin user logs out. I agree with K though, if you want a true shared instance, install a real Express instance somewhere and have your devs connect to that. – Aaron Bertrand Apr 19 '12 at 20:16
  • @Krzysztof, Ward contacted me offline and suggested to me that the API error is simply a bug and not an indication of corruption. – Aaron Bertrand Apr 19 '12 at 20:17
  • @AaronBertrand This is what I meant by registry corruption, sorry for not being precise. It seems that for some reason we cannot decode a timestamp read from the registry key owned by LocalDB. Deleting and recreating the instance fixes it. – Krzysztof Kozielczyk Apr 20 '12 at 05:23
  • @Krzysztof, in my case, deleting and recreating the instance does not fix the Windows API error. – Cory McCarty Apr 20 '12 at 12:46
  • @Aaron, I did see your update. I am working in a brand new VM and the first thing I did on it (even before installing Visual Studio) was to install SQL Server 2012, so I don't have an earlier version of sqlcmd. The problem with installing a full instance of Express is that the download (x64) is about 133MB versus 33MB for localdb. Some of my users are on extremely slow connections and they have absolutely no need for all the extra stuff that comes with the full version of Express. If localdb shared instances were truly shared (ie, could be used by other users independently) it'd be perfect. – Cory McCarty Apr 20 '12 at 12:51
  • So you tried explicitly referencing the 110 path and it still couldn't connect? Or you're assuming you only have one copy of sqlcmd? Did you search? Anyway I think you'll have a hard time getting sympathy in 2012 about a difference in 100MB. Especially when that product is much more geared to your actual goals. – Aaron Bertrand Apr 20 '12 at 12:56
  • @Aaron, with the owner logged in and connected to the DB (so it's running), I am able to connect from the other user (see above). It is sqlcmd 11. The issue is that the shared instance won't start on a connection from any user other than the owner. This is confirmed by Krzysztof above. I would argue that there should probably be a more useful error message in that instance, but it's functioning as designed. If a shared localdb did start on demand for any authorized user, it would suit my goals perfectly. I'm not looking for sympathy, but my users deserve all the support we can offer. – Cory McCarty Apr 20 '12 at 13:16
  • Just a bit of further information: "Instance needs to be restarted after sharing before non-owner users can connect to it using the shared name. We don’t restart the instance automatically if it’s already running in order not to break the apps that are already running." So I think it's very much by design that the owner has to start the instance, but restarting might also be a step you need to perform. I still suggest that if you want your devs to work with SQL Server unattended/unassisted that a proper instance is more suitable than localdb. – Aaron Bertrand Apr 20 '12 at 14:41

5 Answers5

25

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?

enter image description here

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
9

As the original post suggested, this wasn't as straight forward as anticipated, but I was eventually able to connect via the named pipe.

Connecting to a LocalDB instance via named pipe

Russell Speight
  • 344
  • 3
  • 8
  • Weird, I had the same issue with the default MSSQLLocalDB instance and connecting via the instance pipe name worked (after I deleted the previously existing database MDF & LDF files under C:\Users\\). – Lars Kemmann Jan 19 '17 at 17:12
1

THIS ANSWER ASSUMES DELETING THE INSTANCE IS OK.
ie: all your data will be gone and that is okay.

I was having the same problem, after upgrading my SSMS. 

sqllocaldb i
.\MyCustomInstance

sqllocaldb d
LocalDb instance ".\MyCustomInstance" does not exist!

sqllocaldb i .\MyCustomInstance
Windows API call "FileTimeToSystemTime" returned error code: -2147024809.

In order to get rid of the offending instance I had to create another MyCustomInstance which I guess will overwrite what's already there, and now you can delete it

sqllocaldb c MyCustomInstance
LocalDB instance "MyCustomInstance" created with version 11.0.
sqllocaldb d .\MyCustomInstance
LocalDB instance ".\Octopus" deleted.

Then, start the instance and share it. Imperative you start the instance first.

sqllocaldb s MyCustomInstance
LocalDB instance "MyCustomInstance" started.
sqllocaldb h MyCustomInstance MyCustomInstance
Private LocalDB instance "MyCustomInstance" shared with the shared name: "MyCustomInstance".

Now, when you need to connect, you connect with (localdb)\.\MyCustomInstance

ton.yeung
  • 4,793
  • 6
  • 41
  • 72
0

Install the full .NET framework 4.5.2 or later, then reboot, you should then be able to connect using:

sqlcmd -S (localdb)\.\MySharedInstance

I have found that named pipes generate a new hash when the machine is rebooted, the named shared instance will persist after reboots.

Important to note it won't work until after a reboot.

Wili Whitelaw
  • 76
  • 11
-4

The problem is you need to quote the db name:

sqlcmd -S "(localdb)\.\MySharedInstance"
Richard Anthony Hein
  • 10,550
  • 3
  • 42
  • 62
  • 1
    I don't think this is the answer; I didn't need to quote once I was using the correct version of sqlcmd (the one that comes with Sql Server 2012). – Andy May 03 '13 at 16:13