5

So I've been struggling on this for the past few days. I cannot connect to a SQL Server 2017 (14.0.1000.169) LocalDB shared instance. I've been searching and reading all over the net why but could not find any answer that worked.

I tried on a laptop that previously had some SQL Server Express and stuff that I all have uninstalled. And also on a fresh Win10 VM.

Both always have the same error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : 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..

This error is from SQLCMD but I've also tried with SSMS or visual studio connection string.

I tried connecting with the owner of the instance and could not. Also tried with a SQL Login with sysadmin right and could not.

I can only connect with the pipe name.

I also tried everything from these links

https://stackoverflow.com/questions/10214688/why-cant-i-connect-to-a-sql-server-2012-localdb-shared-instance

and

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1257bf26-6ab0-416d-bf26-34f128f42248/sql-2016-sp1-sqllocaldb-versions-errors-with-quotwindows-api-call-quotreggetvaluewquot?forum=sqlexpress

Why can't I can't connect?

  • is your instance "running" on services.msc? How is your Protocols on "QL Server Network configuration" on sql server configuration manager ? – Racer SQL Sep 18 '19 at 20:16
  • @RacerSQL My instance is running I can see it with Sqllocaldb.exe -i, and unless I am mistaken, there's no service running for LocalDB. The same can be said for SQL configuration manager. – Leojet Sep 18 '19 at 20:40
  • Were you able to find a solution? I am experiencing the same issue on a clean Windows 10 v1909 machine with LocalDB 2019. Followed all steps from SqlLocalDB Utility example B. Everything works, except the last part. – Aurora Feb 25 at 16:01
  • @Aurora nope, I changed my app to SQL express instead, sorry... – Leojet Feb 25 at 20:10
2

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:

Windows API call "RegGetValueW" returned error code: 0.

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 value InstanceName, which stores the pipe name, as visible in the following screenshot:

enter image description here

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.

| improve this answer | |
  • And it seems every time you start an instance it gets a new generated instanceName so shared instance after version 2016 do NOT work (tested with 2019 then reverted to 2016 using the same walkthrough) – gl0b Apr 8 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.