7

I cannot connect to a SQL Server 2017 (14.0.1000.169) Local DB shared instance.

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 and 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:

Why can't I connect?

Improve this question
0

3 Answers 3

Reset to default
2

Seems to have been 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.

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
0
4

This issue is resolved by:

KB5003342 - FIX: SQL Server Express LocalDB can't start or connect to shared instances of SQL Server Express LocalDB 2019 or 2017

Assume that you use the SqlLocalDB feature in Microsoft SQL Server 2019 or 2017. In this situation, you can't start correctly or connect to a shared instance of SqlLocalDB.

This problem is fixed in the following cumulative updates for SQL Server:

Demo:

screenshot

Improve this answer
1

As Aurora suggested, this is a bug in LocalDB >= 2017 and Microsoft doesn't seem to care so much about it. The reason is probably because LocalDb is not supposed to be used in production environments. The suggested workaround is good, but it needs to be done manually.

To overcome this issue in your software, you can implement the following workaround:

  1. Create a helper process / background task that runs every 30 seconds or so.
  2. Check if the instance is started (using a SqlLocalDbApi class or by running sqllocaldb i {your_shared_instance_name} in a separate process and get the result). Also, record the named pipe at this point (format: np:\\.\pipe\LOCALDB#AC471EDC\tsql\query - to extract the pipe id value use this regex: #(.*?)\\ -> if matched use match.Groups[1].Value)
  3. Verify the instance pipe name by checking the following registry: SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\\{your_shared_instance_name}
  4. If the recorded pipe name from point 2 is different from the registry value, update the registry key InstanceName

(C# example)

Get the key:

using var myKey = Registry.LocalMachine.OpenSubKey($"SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\\{instanceName}", true);

If it's different from the pipe id:

myKey.SetValue("InstanceName", $"LOCALDB#{pipeId}", RegistryValueKind.String);

Conclusion

Now everything should work. Checking it every 30s or so ensures that in case of Db Process failure/system restarts/etc if will recover just fine. Sorry, but this seems to be the only solution to keep LocalDb alive after 2017 upgrade. The other solution would be to migrate to SQL Express server.

This is only a sample of the code, but hopefully, you get the idea. If not, please leave a comment and I will try to help you. I struggled with this for a bit but it finally runs as LocalDb 2014/2016 without any issue whatsoever.

Improve this answer
1
  • Thanks man, appreciate the info. We will keep it with this workaround for now as installing a huge CU is not an option. Will probably upgrade the whole LocalDb msi package on a new version release.
    – Suciu Eus
    Commented Jun 29, 2021 at 9:49

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