Issue connecting to shared instance from service

Issue

I'm using v3.2.0 of the NuGet package and SQL LocalDB 2019. I have a client and a service installed on a machine where the client runs as a user account and the service runs as NT AUTHORITY\Network Service. The client is able to update the configuration for the service and should be able to read results from the LocalDB that the service populates. LocalDB is one of the storage options available so when this is selected in the client I create the instance and the database. I also add Network Service as a login to the instance with sysadmin and as a user to the database with dbowner. I then share the instance. At this point if I do 'sqllocaldb i' I see both instances are created and inspecting both of them shows they are running and are using a named pipe of "np:\.\pipe\LOCALDB#SH0999EB\tsql\query". This is also the HKLM\Software\Microsoft\Microsoft SQL Server LocalDB\SharedInstances{SharedInstanceName}\InstanceName registry key value.

In the service I check that the shared instance exists using "InstanceExists" which returns true and then "GetOrCreateInstance" populates the instance. However it can't see the database that was created in the original instance. SELECT name FROM master.sys.databases only shows the system dbs. When I display the value of NamedPipe it is different from the original/shared instances and the registry key (np:\.\pipe\LOCALDB#27C663FC\tsql\query).

So am I connecting to a different instance even though originally it said it found the shared instance name and connected to it? Does this have something to do with the fact the code is running as Network Service (even though it was added to the original instance and the database)?

Expected behaviour

Service running as Network Service is able to connect to the shared instance and see the database

Actual behaviour

Service connects but cannot see the database and the named pipe value is different

Steps to reproduce

Process 1

Create an instance
Create a database
Add Network Service as a login with sysadmin and as a user with dbowner to the database
Share the instance

Process 2

Run as Network Service
Connect to the shared instance name
Cannot see the database created in Process 1
Named pipe value is different

Yeah it does sound like this is some weird nuance of using NETWORK SERVICE - I'm not sure of any way to configure it to make it work as the steps you've taken sound like the right thing to do. Maybe SQL LocalDB makes some assumptions somewhere about the file system and/or registry that's causing the second process to not be able to see the shared database from the first process?

I'm not sure that this is something related to the wrapper though, and is probably a question for Microsoft.

Can you replicate the same behaviour if you manually do the operations using SQLLocalDB Utility?
Even though the named pipes are different, are they definitely different databases? It could be that one is seeing the shared name and the other the private name maybe?

I'll try to see if I can replicate the behavior with the sqllocaldb utility (just have to use something like psexec to run as Network Service)

How can I tell if the databases that it sees are the same or different? I dumped out the rows of sys.databases in both and from the client it sees the newly created database but from the service it does not. Not sure if there is a row-level security that would cause the service to not see the new database row in sys.databases

I'm not sure to be honest - it was just a thought that occurred to me from seeing the private and shared names mentioned in the command line sample here from the docs:

SqlLocalDB.exe   
{  
      [ create   | c ] \<instance-name>  \<instance-version> [-s ]  
    | [ delete   | d ] \<instance-name>  
    | [ start    | s ] \<instance-name>  
    | [ stop     | p ] \<instance-name>  [ -i ] [ -k ]  
    | [ share    | h ] [" <user_SID> " | " <user_account> " ] " \<private-name> " " \<shared-name> "  
    | [ unshare  | u ] " \<shared-name> "  
    | [ info     | i ] \<instance-name>  
    | [ versions | v ]  
    | [ trace    | t ] [ on | off ]  
    | [ help     | -? ]  
}

From the client I create the instance "TestInstance" and then share it as "SharedTestInstance" and then from the service I connect to ".\SharedTestInstance"

I did see some SO articles that the user profile needs to be loaded in the service but not sure if that is just for IIS. Does that control what the name of the named pipe would be?

Well this is interesting. I tried reproducing the issue just using the sqllocaldb command line utility. Here were the steps I took:

From a command prompt running as the current user:

"sqllocaldb c TestInstance" to create the test instance
"sqllocaldb s TestInstance" to start the test instance
Created TestDB within TestInstance through Microsoft SQL Management Studio
Created the login/user for NT AUTHORITY\NETWORK SERVICE using these SQL commands (same as what I'm executing in my code):

Use master
GO

CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS
GO

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]
GO

EXEC sp_addsrvrolemember 'NT AUTHORITY\NETWORK SERVICE', 'sysadmin'
GO

USE TestDB
GO

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]
GO

ALTER ROLE [db_owner] ADD MEMBER [NT AUTHORITY\NETWORK SERVICE]
GO

"sqllocaldb h TestInstance SharedTestInstance" to share the instance
Check the information about the original and shared instance:

C:\WINDOWS\system32>sqllocaldb i TestInstance
Name: TestInstance
Version: 15.0.4153.1
Shared name: SharedTestInstance
Owner: JONSPC\Jon
Auto-create: No
State: Running
Last start time: 8/10/2022 9:37:38 AM
Instance pipe name: np:\.\pipe\LOCALDB#CB3B846B\tsql\query

C:\WINDOWS\system32>sqllocaldb i .\SharedTestInstance
Name: TestInstance
Version: 15.0.4153.1
Shared name: SharedTestInstance
Owner: JONSPC\Jon
Auto-create: No
State: Running
Last start time: 8/10/2022 9:37:38 AM
Instance pipe name: np:\.\pipe\LOCALDB#CB3B846B\tsql\query

Notice named pipe names are the same
"sqlcmd -S (localdb)\TestInstance -d master -E -Q "select name from sys.databases" to verify the database was added to the table:

name

master
tempdb
model
msdb
TestDB
(5 rows affected)

"psexec -i -u "NT AUTHORITY\NETWORK SERVICE" cmd.exe" to start a command prompt as Network Service

From Network Service command prompt:

"whoami" to verify running as Network Service
"sqllocaldb i .\SharedTestInstance" to get shared instance info:

C:\WINDOWS\system32>sqllocaldb i .\SharedTestInstance
Name: TestInstance
Shared name: SharedTestInstance
Owner: JonsPC\Jon
Instance pipe name: np:\.\pipe\LOCALDB#SH879AE8\tsql\query

Notice named pipe name is different from current Windows user command prompt
"sqlcmd -S (localdb).\SharedTestInstance -d master -E -Q "select name from sys.databases"" to list the databases:

name

master
tempdb
model
msdb
TestDB
(5 rows affected)

The database is there!
I then created TestTable from within SQL Management Studio and inserted a row and was able to query it from the Network Service window:

sqlcmd -S (localdb).\SharedTestInstance -d TestDB -E -Q "select * from TestTable"
column1 column2

test1 1

(1 rows affected)

So I guess its either something in the wrapper or (more likely) maybe I'm not performing all the steps correctly to add NETWORK SERVICE to the database from within my code (but I think if that was the case it would still be able to see TestDB in the sys.database table when I query that from the service right? Or if the access to the DB wasn't correct you wouldn't even see it listed when querying the sys.databases table?)

The only thing I can think of is maybe it has something to do with the owner SID when sharing.

There's a method on the interface where you can explicitly pass through the owner SID, or there's an extension method that doesn't take a SID that shares as the current user.

sqllocaldb/src/SqlLocalDb/ISqlLocalDbApi.cs

Line 98 in e5efb5c

    
           void ShareInstance(string ownerSid, string instanceName, string sharedInstanceName);

sqllocaldb/src/SqlLocalDb/ISqlLocalDbApiExtensions.cs

Lines 183 to 202 in e5efb5c

    
               public static void ShareInstance(this ISqlLocalDbApi api, string instanceName, string sharedInstanceName) 
        
               { 
        
                   if (api == null) 
        
                   { 
        
                       throw new ArgumentNullException(nameof(api)); 
        
                   } 
        
                   SqlLocalDbApi.EnsurePlatformSupported(); 
        
                   string ownerSid; 
        
           #pragma warning disable CA1416 
        
                   using (var identity = WindowsIdentity.GetCurrent()) 
        
                   { 
        
                       ownerSid = identity.User!.Value; 
        
                   } 
        
           #pragma warning restore CA1416 
        
                   api.ShareInstance(ownerSid, instanceName, sharedInstanceName); 
        
               }

Which of the two methods are you using? Does your issue fix itself if you try the other?

Sorry, just getting back to this now.

First off I verified through the sqllocaldb command line version that if a database that Network Service has no access to is created it still can be seen if you display all the rows in the sys.databases table.

Second I was using the version of ShareInstance that just took the instance name and the share name. I tried using the version that passes the SID but since I used the same function to get the SID of the current user as you do it gave the same results.

In debugging one thing I noticed was when creating the instance from the client the instance.OwnerSid was the Sid of the current Windows user. But then when connecting to the instance from the service I would call InstanceExists(@".\SharedTestInstance") which retuned true (I don't believe this call would create an instance if it didn't exist and verified that by calling it against a non-existent instance name and it returned false). I would then call GetInstanceInfo(@".\SharedTestInstance") which would succeed (well in a sense anyway since we couldn't see the database) but the OwnerSid was the well-known Sid of Network Service and I assume it should have been the Sid of the user that created the instance from the client.

In any case I think I found a workaround in that when connecting from the service I'm just calling connection = new SqlConnection() and opening that which gives me access to the database.

I'm going to close this as I think it's a usage and/or SQL LocalDB thing that you've managed to work around. Let me know if there's anything concrete this library could do to improve this scenario.

	public static void ShareInstance(this ISqlLocalDbApi api, string instanceName, string sharedInstanceName)
	{
	if (api == null)
	{
	throw new ArgumentNullException(nameof(api));
	}

	SqlLocalDbApi.EnsurePlatformSupported();

	string ownerSid;

	#pragma warning disable CA1416
	using (var identity = WindowsIdentity.GetCurrent())
	{
	ownerSid = identity.User!.Value;
	}
	#pragma warning restore CA1416

	api.ShareInstance(ownerSid, instanceName, sharedInstanceName);
	}