The casing of the instance name does not matter.
You need to try:
sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01
The "localdb" part needs to be enclosed in parenthesis as that is a special syntax that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole instance name needs to be enclosed in double quotes. The command-line shown above works for me, at least outside of PowerShell.
Is Navicat installed on the same computer as LocalDB? LocalDB doesn't allow for remote connections.
Assuming that Navicat is installed on the same computer: is Navicat a service or desktop application? LocalDB runs, by default, as a private user process that only your Login can see (no problem for a desktop application that you are running). If you want others (such as a service) to be able to access your LocalDB instance, you need to share it using the sqllocaldb.exe
command line utility.
Also #1: (localhost)\MSSQLLocalDB
is not a valid syntax. The reason for specifying (localdb)\InstanceName
is that the (localdb)\
portion of it is a special syntax that the underlying connection library understands and hands-off to the LocalDB library (it is special because it can auto start up that instance of LocalDB simply by trying to connect to it using that syntax).
Also #2: SQL Server Native Client is a connection library (ODBC is another type of connection library), and it is being used by Navicat to connect to SQL Server. If Navicat is saying that you need to install SQL Server Native Client, then you need to download it from Microsoft. And yes, the last version of it was for SQL Server 2012. But if Navicat requires it, then you kinda need it.
Now, it appears that Navicat Premium is a desktop app so you likely don't need to worry about sharing the instance, unless there are other users logged onto your computer that you want to be able to access this instance. But for now, I will assume that it is only you using it. Keeping in mind the fact that LocalDB isn't a service and so not always running, and that it can be auto-started by attempting to connect to (localdb)\something
, it should be understood that it takes several seconds for the LocalDB instance to start. The amount of time it takes is longer than the default connection timeout, at least in SQL Server Management Studio (SSMS). So, when initially connecting, it is expected to get the timeout error, so immediately try connecting again. If you wait too long (several minutes) to attempt connecting again, you will get the timeout again as the instance will auto-shutdown after several minutes of no connections.
So, again, assuming that both LocalDB and Navicat are running on the same computer AND by the same Windows Login, try connecting to (localdb)\MSSQLLocalDB
. When you get the connection timeout error, immediately try connecting again. If it doesn't work, then something else is wrong.
Best Answer
Seems to be a bug in LocalDb v2017 and higher. I tested all available versions, starting from v2012 SP4. Up to and including v2016 SP2, the friendly share name
"(localdb)\.\SHAREDNAME"
is accepted in the connection request. When using v2017 or higher, the request always results in an "instance not found" error.I've also observed that querying all installed LocalDb versions with
sqllocaldb.exe v
results in an error message for v2016 and higher:Not sure whether this behavior might be linked to the original issue.
UPDATE
Applying the registry fix linked in the question solves the version query.
After some more digging, I think I may have discovered the cause for the connection failure. When SQLLocalDb up to v2016 is installed on the local machine, the share feature works as follows:
In the registry, a new key is created in
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\{SharedName}
. This key contains a string valueInstanceName
, which stores the pipe name, as visible in the following screenshot:When the corresponding instance is started, it always picks this pipe name. The name stays persistent, i.e. when the instance is restarted, the pipe name is reused.
This logic is destroyed when LocalDb v2017 or higher is installed: When an instance with a shared name is started, the pipe name in the registry is ignored. Instead, a completely different pipe name appears when querying the instance via
sqllocaldb i {instance}
.This is probably also the reason why the connection can't be established, since the pipe names don't match. Editing the registry value to match the actual pipe name solves the issue, but only until the instance is restarted.