CODEWITHSUNDEEP
wixwindows-installerlocaldb
denalisk
denalisk

Reputation: 193

SQL localdb instance not accessible after creation during install

I've got a windows service that creates a named sql localdb instance during installation. I execute commands as the local system account during installation and can create, info query, and start the instance. I'm creating it using a wix custom action run as the localsystem account, it kicks off without a hitch:

<!-- Custom action for creating a named localdb instance -->
<CustomAction Id="CA_SetPropertiesCreateLocalDbInstance" Property="CA_CreateLocalDbInstance" Value="InstanceName=MyLocalDbInstance" />
<CustomAction Id="CA_CreateLocalDbInstance" BinaryKey="BIN_CustomActions" DllEntry="CreateLocalDbInstance" Execute="deferred" Impersonate="no" Return="check" HideTarget="no" />

<!-- Schedule the Custom Actions -->
<InstallExecuteSequence>
    <Custom Action="CA_SetPropertiesCreateLocalDbInstance" Before="CA_CreateLocalDbInstance">NOT Installed</Custom>
    <Custom Action="CA_CreateLocalDbInstance" Before="InstallFinalize">NOT Installed</Custom>
</InstallExecuteSequence>

The logs from that custom action running during install:

Executing command 'sqllocaldb c MyLocalDbInstance'
command output>>    LocalDB instance "MyLocalDbInstance" created with version 14.0.3162.1.
command exit code>> 0
Executing command 'sqllocaldb s MyLocalDbInstance'
command output>>    LocalDB instance "MyLocalDbInstance" started.
command exit code>> 0
Executing command 'sqllocaldb i MyLocalDbInstance'
command output>>    Name:               MyLocalDbInstance
command output>>    Version:            14.0.3162.1
command output>>    Shared name:        
command output>>    Owner:              NT AUTHORITY\SYSTEM
command output>>    Auto-create:        No
command output>>    State:              Running
command output>>    Last start time:    8/8/2019 11:40:23 PM
command output>>    Instance pipe name: np:\\.\pipe\LOCALDB#3FB77699\tsql\query
command exit code>> 0

However, when I try and access the localdb instance during the application runtime, I get the error error: 50 - Local Database Runtime error occurred. The specified LocalDB instance does not exist.

If I use psexec.exe (MSFT tool for powershell) to run sqllocaldb queries as the localsystem account, I can see the localdb instance I created:

PS C:\WINDOWS\system32> whoami
nt authority\system
PS C:\WINDOWS\system32> sqllocaldb i
MSSQLLocalDB
MyLocalDbInstance

I can access info on the default instance no problem, but trying to query the instance created results in an error:

PS C:\WINDOWS\system32> sqllocaldb i MyLocalDbInstance
Printing of LocalDB instance "MyLocalDbInstance" information failed because of the following error:
LocalDB instance "MyLocalDbInstance" doesn't exist!

Interestingly, if I run "sqllocaldb c MyLocalDbInstance" again from the localsystem powershell instance, it creates a new instance. Subsequent executions of the "sqllocaldb c MyLocalDbInstance" command simply overwrite the newly created one:

PS C:\WINDOWS\system32> sqllocaldb c MyLocalDbInstance
LocalDB instance "MyLocalDbInstance" created with version 14.0.3162.1.
PS C:\WINDOWS\system32> sqllocaldb c MyLocalDbInstance
LocalDB instance "MyLocalDbInstance" created with version 14.0.3162.1.
PS C:\WINDOWS\system32> sqllocaldb i
MSSQLLocalDB
MyLocalDbInstance
MyLocalDbInstance

I'm pretty stumped, and am probably missing knowledge of one of the following to trace the issue:

-weird windows-user compartmentalization things preventing access

-internal complexities in sqllocaldb

-artefactual interactions with psexec.exe masking the real problem

My next debugging step is probably to make the instance "Shared" during installation, but that's undesirable for security reasons.

I can try and put up a dummy project with a dummy installer if anyone really wants to dig in.

Upvotes: 1

Views: 494

Answers (1)

Christopher Painter
Christopher Painter

Reputation: 55600

I've only used localdb in an installer once. This was for a stand alone "demo" version of an application where they didn't want to install IIS and a full blown SQL instance. Basically on a laptop used by one person.

My custom actions to create and start the database were set with Impersonate="yes" so that the commands would be run as the user performing the installation and in that scenario the database was available to the user when they tried to start the application. I don't think we tested multiple users using this database but I'm pretty sure the database would have to be "shared" to do so which means my commands would need to run without impersonation so it ran with SYSTEM/Admin privs.

https://learn.microsoft.com/en-us/sql/tools/sqllocaldb-utility?view=sql-server-2017

See: Working with a Shared Instance of LocalDB

Reading the doco on the share command it seems you have to explicitly share it to everyone you want to have access. This suggests to me it might be most flexible to create an API layer running as a service account and only share it to that account.

Upvotes: 1

Related Questions