question

atiyar avatar image
1 Vote"
atiyar asked JohanGreefkes-2613 edited

Cannot connect to shared instance of LocalDB 2017/2019

As a part of my VS2019 installation, I had LocalDB v13 (SQL Server 2016) on my machine. I could share a named instance and I could -

  1. login to the shared instance (in SSMS) with Server Name - (LocalDB)\.\SharedName

  2. access a database from my .NET Core app with connection string -
    "Server=(LocalDB)\\.\\SharedName;Database=MyDB;Trusted_Connection=True;"

  3. host the app in a Windows Service running under a different user account and access a database with above connection string

Recently, I uninstalled the v13 and installed v15 (SQL Server 2019). Created new instance and shared it as before, but I cannot do anything listed above.

When I try to login through SSMS, I meet with the following message -

43268-ldbshared-failed.png

When I try to access the database from my .NET Core app, an exception says -

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

and similar message in Event Viewer with Window Service hosting.

I tried v14 (SQL Server 2017) too. Same result as v15.

Is there something new with LocalDB v14 and v15 when sharing an instance which I might be missing? I checked the documentation(Connect to a shared instance of LocalDB) but couldn't find anything helpful.

How do I solve this issue? Thanks.


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered atiyar edited

Hi @atiyar,

access a database from my .NET Core app with connection string -"Server=(LocalDB)\.\\SharedName;Database=MyDB;Trusted_Connection=True;"

The server name should be “(LocalDB.\SharedInstancename)”. And Please make sure the shared Instance name is correct.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

Please make sure the LocalDB is running.
You can use SQLLocalDB info <instancename> in cmd to view the state of LocalDB.
If the LocalDB is stopped, you can use SqlLocalDB start <instancename> in cmd to start the specified instance.
In addition, SQL Server express LocalDB doesn't accept remote connections, and it supports only local. You cannot connect to SQL Server LocalDB from remote computer.

If you have any other question, please feel free to let me know.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AmeliaGu-msft,
1. please take a look at the this documentation where it clearly instructs to add \.\ (backslash + dot + backslash) to the connection string, and my connection string -

 "Server=(LocalDB)\\.\\SharedName;Database=MyDB;Trusted_Connection=True;"

contains double backslashes because its a string value and I had to add one extra escaping backslash for each, and the exact same thing worked for LocalDB v13
2. of course I did make sure that LocalDB is running
3. I wasn't trying to connect to the LocalDB from a remote machine, but from a different user account on the same machine

1 Vote 1 ·

So I had never heard of shared localDB instances until I saw your post. I have been trying to create one, but I have not been successful to connect to them. And I have tried using both SqlLocalDB from SQL 2019 and from SQL 2014. So it may not be exactly the SQL Server version that matters.

But what the problem might be, I don't know. The Docs suggests that with an older version of .NET you need to specify the named pipe explicitly, and it's different each time. What I can't figure out is how the API would find the named pipe. I found the shared instance in the registry, but all there is the instance name, the name of the localdb instance and the owner.

Amelia suggested a different connection model, but I don't know if she had tested it.

0 Votes 0 ·

Hello @ErlandSommarskog ,
- Yeah there is such a thing :) and you'll need it if you want to access a database on LocalDB from a service running with a user account other than the current user.
- From my experience, even with LocalDB 2016, you'll be able to connect to the shared instance if you don't have any other (higher) version(s) installed. It seems the SQLLocalDB utility always uses the highest version available. If you create an instance mentioning version number, you'll get the instance of that specific version, but everything is done using the highest version binary available, and things get messed up when the binary used is higher than 2016.
- Regarding how the API finds the named-pipe name, I think the "UPDATE" part of this answer here have a clue.


0 Votes 0 ·
Show more comments
AmeliaGu-msft avatar image
2 Votes"
AmeliaGu-msft answered JonathanReis-3007 commented

Hi @atiyar,
Sorry for my mistakes.
After doing some tests, starting from SQL Server 2012 to 2016 SP2, we can use “(LocalDB).\sharedinstancename” to connect it.
44393-1.jpg
But since SQL Server 2017 and higher, there is a known issue that we cannot connect to localdb shared instance name.
44412-2.jpg

Please refer to this feedback.
I’m afraid we need to use SQL server 2016 LocalDB shared instance temporarily.
Best Regards,
Amelia



1.jpg (110.1 KiB)
2.jpg (63.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your reply.

(Not related to the problem)
Not sure if you've noticed, you are missing a \ (backslash) every time you are writing a server name. It got me confused :)
Issue with the Q&A editor here.

0 Votes 0 ·

Sadly the Connection Issue for SQLServer2017 and newer has been open for two years and does not even have the[UNDER REVIEW] tag yet :/ If anyone has the connections to ask a Developer about it or raise the visibility of the Issue otherwise please do. Shared Instance is a very useful feature and it would be a shame if it stays broken going forward.

0 Votes 0 ·

We have reported this to Microsoft a number of times, but they don't seem to care about it. Very frustrating!

1 Vote 1 ·
gustav avatar image
1 Vote"
gustav answered

Oh my ... spent a day struggling with LocalDb 2019, only finally to bump on this frustrating answer.

Couldn't this important info be raised to the Doc pages of LocalDb?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JohanGreefkes-2613 avatar image
0 Votes"
JohanGreefkes-2613 answered JohanGreefkes-2613 edited

This may be a temporary workaround, but is good enough for my testing:

  1. As the user that owns the LocalDB database, run the command SqlLocalDb info <instancename>

  2. from the output, copy the Instance pipe name. This will look something like this: np:\\.\pipe\LOCALDB#8DF00B42\tsql\query

  3. Use this instance pipe name as the SQL instance name for the other users on the same computer.

Note that the instance name can change after a reboot. With this as the instance name, I'm able to connect using PowerShell and SQL studio.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.