LocalDB shared instance not supported

[gkeongit]

Connection to a shared instance of localdb fails. Shared instances use names in format '.\name'. Hence, a connection string contains a datasource like '(localdb)\.\name' with two backslashes.

But here the assumption is made, that splitting the datasource value by backslash yields exactly two tokens.

string[] tokensByBackSlash = workingDataSource.Split(BackSlashCharacter);

error = false;

// All LocalDb endpoints are of the format host\instancename where host is always (LocalDb) (case-insensitive)
if (tokensByBackSlash.Length == 2 && LocalDbHost.Equals(tokensByBackSlash[0].TrimStart()))
{

Exception message

Cannot connect to SQL Server Browser. Ensure SQL Server Browser has been started.

The exception message is a bit misleading. The initial error is, that the datasource name is not recognized as a LocalDB instance.

Stack trace

System.Exception: Cannot connect to SQL Server Browser. Ensure SQL Server Browser has been started.
       ---> System.Net.Sockets.SocketException (11001): No such host is known.
         at System.Net.Dns.GetHostEntryOrAddressesCore(String hostName, Boolean justAddresses)
         at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)
         at System.Net.Sockets.UdpClient.GetEndpoint(String hostname, Int32 port)
         at System.Net.Sockets.UdpClient.SendAsync(Byte[] datagram, Int32 bytes, String hostname, Int32 port)
         at Microsoft.Data.SqlClient.SNI.SSRP.SendUDPRequest(String browserHostname, Int32 port, Byte[] requestPacket)
         at Microsoft.Data.SqlClient.SNI.SSRP.GetPortByInstanceName(String browserHostName, String instanceName)

To reproduce

Try to connect to a shared instance. From what I saw, it does not even have to exist to let you reproduce the error. Just use a connection string with the '.\name' syntax:

var _connectionString = "Data Source=(localdb)\\.\\MyLocalDB_Shared;Initial Catalog=MyTable;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;"

var connection = new SqlConnection(_connectionString);
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);

Expected behavior

The data source should be recognized as LocalDB instance.

Further technical details

Microsoft.Data.SqlClient version: 3.0.0
.NET target: 5.0
SQL Server version: 15.0.4153 with cumulative update SQLServer2019-KB5004524-x64.exe installed
Operating system: Windows 10 Pro, version 2004, build 19041.1165

[JRahnama]

@gkeongit sorry for the delay. I am looking into this issue and will provide more information shortly.

[JRahnama]

Here is a description on how I ran the code for those who want to follow:

1. Make sure you have installed SqlLocalDB
2. Open Developer Command Prompt
3. Type: SqlLocalDB create "" (you can choose any name)
4. Type: SqlLocalDB share " your instance name " "provide a name for shared instance"
5. SqlLocalDB start "name your instance"

Continue either on SSMS or CLI with SqlCmd: for SSMS use (localdb)\your instancename
Create LOGIN [Domain\User]; (This section fails since the user name exists)

@gkeongit please make sure you have done the following:

More immportant part
GRANT LOGIN TO [Domain\User]

 class Program
    {
        static async Task Main(string[] args)
        {
            var builder = new SqlConnectionStringBuilder()
            {
                DataSource = "(localdb)\\.\\OurInstance",
                IntegratedSecurity = true,
                ConnectTimeout=30,
                Encrypt=false,
                TrustServerCertificate=false,
                ApplicationIntent=ApplicationIntent.ReadWrite,
                MultiSubnetFailover=false
            };
            using var connection = new SqlConnection(builder.ConnectionString);
            var cts = new CancellationTokenSource();
            await connection.OpenAsync(cts.Token).ConfigureAwait(false);
            Console.WriteLine(connection.State);
        }
    }

And this will work perfectly with no issue.

In VS2019 you can go to view tab and select SQL Server Object Explorer
click on Add SQL Server and on the opened window select local. You will see the shared instance name there as well with provided connection string.

Please let me know if there is any issue after granting access to user, otherwise we can close the issue.

[gkeongit]

Thank you very much for your support. I have tried to follow the proposed steps, because I set up my user with SSMS and wanted to see, if it makes a difference using SQL CLI. But both SQL commands give me syntax errors. E.g. to create the login, I have to append from Windows:

Create LOGIN [...] from Windows;

And I could not figure out, what the exact syntax of the GRANT command would be.

Anyway: in my case, I think I already did the required steps in SSMS. Hence, I can use Server Object Explorer in VS2019 and also SSMS with my shared instance and with the original instance.

And I can confirm, that your code example is running fine on my machine. But it is obviously running with a different user account (in my case: the DB owner account), what seems to change the outcome.

So here are details about the user that runs into the error. It is an IIS AppPool user ('Token Service'), since my application is an ASP.NET Core web application hosted in IIS. It has a login on the server:

And it is linked to a database user, to which I now granted a lot of rights just to test this issue:

But I still have the same error. Please let me know, if I am missing a thing here.

Also, I assume your test case cannot pass the SqlClient code I mentioned, because if you try this (based on original SqlClient condition)...

           const string LocalDbHost = "(localdb)";
            string[] tokensByBackSlash = "(localdb)\\.\\OurInstance".Split('\\');

            // All LocalDb endpoints are of the format host\instancename where host is always (LocalDb) (case-insensitive)
            if (tokensByBackSlash.Length == 2 && LocalDbHost.Equals(tokensByBackSlash[0].TrimStart()))
            {
                Console.WriteLine($"Recognized localdb instance '{tokensByBackSlash[1]}'");
            }
            else
            {
                Console.WriteLine("Not a localdb instance");
            }

...you will see, that (localdb)\.\OurInstance is not recognized as LocalDB instance. In that case, SqlClient takes the string as servername, which leads to System.Net.Sockets.SocketException (11001): No such host is known.

[JRahnama]

@gkeongit I think the length of tokensByBackSlash would be 3 in here not 2. Accordingly the we need to change it to:

 if (tokensByBackSlash.Length == 3 && LocalDbHost.Equals(tokensByBackSlash[0].TrimStart()))
            {
                Console.WriteLine($"Recognized localdb instance '{tokensByBackSlash[2]}'");
            }
            else
            {
                Console.WriteLine("Not a localdb instance");
            }

Is this what we are looking for or I missed something in the explanation?

[JRahnama]

one more question are you using managed SNI by any chance?

[gkeongit]

@JRahnama About the code change: It should be

tokensByBackSlash.Length >= 2

Otherwise we would exclude the non-shared instance names that resolve to two tokens.

About the managed SNI: Yes, you are right! I forgot about it, but in an attempt to solve another issue, I followed an advice to use:

AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true);

And this was still in my code. After removing this, I can connect to the shared instance with my IIS AppPool user, too.

[JRahnama]

@gkeongit I noticed the issue in managed SNI. Basically it assumes the array is always in a size of 2 and that creates the problem.
we have different versions, (localdb). will go to default I am going to expand the tests and do some modifications on PR #1237 to cover all possible areas. I will mention you in the PR to give it a try and provide a feedback shortly.

[JRahnama]

@gkeongit can you test with PR #1237 and check if the issue is resolved please?

[gkeongit]

@JRahnama my test failed, but it looks like we just need to retain the leading '.\' of the shared instance name. I could connect to

• "(localDb)\instance" with instanceName = "instance"
• "(localDb)\.\shared_instance" with instanceName = ".\shared_instance"
• and non-localdb "localhost" with instanceName = null

with this change:

diff --git a/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs b/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs
index bf4831f7..52bc18b2 100644
--- a/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs
+++ b/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SNI/SNIProxy.cs
@@ -493,13 +493,9 @@ namespace Microsoft.Data.SqlClient.SNI
                 // When netcoreapp support for netcoreapp2.1 is dropped these slice calls could be converted to System.Range\System.Index
                 // Such ad input = input[1..];
                 input = input.Slice(LocalDbHost.Length);
-                if (!input.IsEmpty && input[0] == '\\')
+                if (!input.IsEmpty && input[0] == BackSlashCharacter)
                 {
                     input = input.Slice(1);
-                    if (input.Length >= 2 && input[0] == '.' && input[1] == BackSlashCharacter)
-                    {
-                        input = input.Slice(2);
-                    }
                 }
                 if (!input.IsEmpty)
                 {

[JRahnama]

@gkeongit, let's review the case again:
possible scenarios. from what I could understand, are:

• (localDb)\instance with instanceName = "instance"
• (localDb)\.\shared_instance with instanceName = ".\\shared_instance"
• (localdb)\.

These three scenarios should be working with the change and the failure is about the 3rd part in your list? is that correct?
Can you explain more about

• and non-localdb "localhost" with instanceName = null

like we provide server=localhost; or Data Source = localhost;?

[ErikEJ]

What is (localdb). ??

[JRahnama]

@ErikEJ I forgot to wrap it as code and GitHub do not show it. (localdb). and (localdb)\. are typed the same if you try to edit this conversation.

More to add if you use <> in your comment, the insid it will be hidden if you do not wrap it as code.

There is a comment here if you try to edit :=====>
but it shows here: <This is hidden>

[ErikEJ]

OK, but what is (localdb)\. - can localDB have a blank instance name??

[JRahnama]

@ErikEJ there are two types of instances (MS doc):

1. Automatic
2. Named

When we use (localdb)\. the connection will be established with the automatic (default) LocalDB instance.

[ErikEJ]

Interesting!

[gkeongit]

@JRahnama

Can you explain more about

• and non-localdb "localhost" with instanceName = null

like we provide server=localhost; or Data Source = localhost;?

Yes, exactly. It is the case, which must not be recognized as localdb instance name. Hence, the method must return null here. But this is just a non-regression test.

the failure is about the 3rd part in your list?

The failure is on the explicit shared instance syntax only: (localdb)\.\shared_instance. Since I did not know about (localdb)\. either, I did not include this in my tests.

[JRahnama]

@gkeongit server=localhost is different than SqlLocalDB. Localhost is working from the code in a different context and the SSMS as well when I try to connect to the local server on my machine. local host is your computer (127.0.0.1) If you follow the code pattern at the line 146 we have a function named CreateConnectionHandle if the localdb is returned as null, it will take the server's full name which could be localhost. Now, if we look back at our code the first thing we check is input.StartsWith(LocalDbHost and the constant is defined with the value of private const string LocalDbHost = "(localdb)";. So there is no way that something like localhost could be returned from SqlLocalDb part of the code as far as I could see. The pr #1237 clearly states that with the changes and I think that PR will fix the issue when it merges to the main branch, however you can test it from the built artifact here. Please correct me if I am mistaken.
the only way we return a not-null value from GetLocalDBInstance function is that the string start with (localdb).

[gkeongit]

@JRahnama sorry, if I added confusion with my testcases. I absolutely agree to your explanation and I am aware of localhost being a servername, not an instancename. Also, the result of returning null is the expected behavior. So, no changes required to handle localhost correctly!

Let us focus on the (localdb)\.\shared_instance instead. I made a test with the artifact you provided. It still cannot connect to the shared instance. As far as I can tell, with PR #1237 the shared instance name is still returned as

shared_instance

but it should be

.\shared_instance instead. The shared instance name must include the prefix ".\".

Please compare to my proposal, where I removed the unwanted final input.Slice(2) case, because it dropped the required prefix:

-                    if (input.Length >= 2 && input[0] == '.' && input[1] == BackSlashCharacter)
-                    {
-                        input = input.Slice(2);
-                    }

I think, if you remove this block in PR #1237, it should work.

[JRahnama]

@gkeongit,

I can confirm your suggestion was correct. I have applied your requested changes to the PR and it should be working. Please let me know of the results.

For those who are following:

if you run sqllocaldb info "instance name" you will get a list of the instance properties. Driver gets the Instance pipe name from the localdb folder inside Microsoft SQL Server folder which currently is in folder 130 in SQL 2019. for example:
your Instance pipe name may be as np:\\.\pipe\LOCALDB#SH74550E\tsql\query, but the number in the name (here is SH74550E) might change due to different reasons, providing the instance name or shared instance name will make sure we will get the correct pipe name. Alternatively, that pipe name could be used to get connection from shared instance.

[gkeongit]

@JRahnama

Thank you, that's it! With this artifact I could successfully connect to shared instance.