SQL Server shared LocalDB instance is not automatically starting

Question

SQL Server shared LocalDB instance is not automatically starting

Cory Riddell 0

I have an application that uses LocalDB. The named instance "PrivDb" is owned by whatever account installed the application. Part of the setup process is to grant "NT AUTHORITY\Authenticated Users" the sysadmin role so that any authenticated user can connect and the instance is shared as "SharedDb".

When another user on the machine tries to connect to the shared instance, it fails with this error:
User's image

If I manually start the instance as the user who installed it, other users on the machine can connect to the instance.

I'm running SQL Server Express 2022 with cumulative update 15 installed.

Is there something else that I have to configure so that the shared instance will automatically start when other users connect to it?

My problem seems identical to this one:
https://support.microsoft.com/en-us/topic/kb5003342-fix-sql-server-express-localdb-can-t-start-or-connect-to-shared-instances-of-sql-server-express-localdb-2019-or-2017-29147198-d431-47a4-80f0-ece917f28859

except I'm on the 2022 release.

Cory Riddell 0 Reputation points

2024-10-11T13:00:37.3766667+00:00

Why do my posts get automatically deleted for violation of the Code of Conduct? There's nothing I'm writing that seems like a violation.

3 answers

Your answer

Cory Riddell 0 Reputation points

2024-10-11T13:00:37.3766667+00:00

Why do my posts get automatically deleted for violation of the Code of Conduct? There's nothing I'm writing that seems like a violation.

Answer 1

Cory Riddell 0

I ended up writing a simple service to start the LocalDB instance. It creates a named pipe that clients can send a message over to start the instance.

Unfortunately it doesn't work when running as SYSTEM. I'm calling LocalDBStartInstance and it returns S_OK, however the instance isn't actually started.

If I modify the service to run as the user who owns the database, it works. This isn't a viable workaround though because it's too fragile. For example, if AD is configured to force users to change their password, the service will break because the stored password is now stale.

When I use Process Monitor I can see my service starting SQLLocalDB and I can see it find the shared instance in the registry, find the owner and name of the instance, then it basically ends.

Either I totally misunderstand shared instances and they are not intended to automatically start for other users on the machine, or it's a bug similar to the on in KB5003342. Is there any way to contact the developers who worked on that KB fix?

Cory Riddell 0 Reputation points

2024-10-11T13:01:42.59+00:00

@ZoeHui-MSFT Is getting in touch with the people who worked on KB5003342 something you can help me with?
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2024-10-11T21:18:20.62+00:00

The only regular way to do that is to open a support case. You would probably need a fairly hefty agreement to get this issue escalated. The important thing to stress is the business needs.

Then again, have you tried to run this service under Managed Service Account?

Also, are you sure that LocalDB is the best fit here? Why not a regular Express instance?
Cory Riddell 0 Reputation points

2024-10-14T00:20:08.4033333+00:00

have you tried to run this service under Managed Service Account?

No, I haven't and I will look into that.

Since it seems unlikely that I'll get this in front of the right people at Microsoft, I'm reconsidering the decision to use a shared LocalDB. Our users are almost never going to have multiple accounts on one machine running our application, so each user having their own (private) instance is going to work for the overwhelming majority of our users. If I'm wrong about that and we need the shared database, we can drop back to SQL Server 2019.

We are migrating from a MS Access database because quite a few of our users cannot install the 64-bit Access Database Engine package.
Cory Riddell 0 Reputation points

2024-10-14T14:24:10.61+00:00

It looks like Managed Service Accounts require Active Directory and that's not something all of our users have.
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2024-10-14T20:21:26.7933333+00:00

That's correct that gMSA (group Managed Service Accounts) requires Active Directory, but that was not what I had in mind. You can also have machine-local MSA, and this is an older feature that has been around for a while. When you install SQL Server, the service accounts for the Database and Agent defaults to local MSAs.
Cory Riddell 0 Reputation points

2024-10-15T17:52:58.6233333+00:00

If you can point me to a page that describes how to create machine-local MSAs, I'd appreciate it. I looked around and found information on standalone managed service accounts but these still seem to be based on AD and require Windows Server. Our application is a desktop application installed on end user machines.
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2024-10-15T21:11:32.9933333+00:00

Obviously it is possible to create standalone MSAs on a desktop OS - SQL Server is doing it.

But how? Well, I'm an SQL Server guy, not a Windows guy. So I know that it can be done, but not how. I tried Google, but the result was very much focused on AD and Windows Server. But I learn that the term is standalone MSA, or sMSA.

I would suggest that you ask a question with a Windows tag. And stress that you need a solution that works on a desktop OS without a domain. And a solution that is programmatic, since you will add it to an installer.

Answer 2

Olaf Helper 47,616

The server name "(LocalDB).\ShareDB" is wrong, exactly the addition slash+dot, remove "." =>

"(LocalDB)\ShareDB"

Cory Riddell 0 Reputation points

2024-10-02T12:16:10.09+00:00

I believe the \.\ is correct.

See: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver16#connect-to-a-shared-instance-of-localdb
Cory Riddell 0 Reputation points

2024-10-02T12:16:53.1+00:00

[deleted]

Answer 3

ZoeHui-MSFT 41,551

Hi @Cory Riddell,

What about creating a batch file containing only the command starting SQL Server Express and schedule a task via Task Scheduler.

https://dba.stackexchange.com/questions/52697/how-to-auto-start-instance-of-sql-server-2012-localdb-on-startup

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Cory Riddell 0 Reputation points

2024-10-02T12:22:19.54+00:00

The problem is a LocalDB will automatically stop after some period of inactivity.

The only workaround I've been able to come up with is to write a service whose only job is to start the instance. When our application starts, it could ask the service to start the instance.

It seems too hacky though. I'd like to know exactly what was fixed in KB5003342 because I suspect something similar is happening with v2022.
Cory Riddell 0 Reputation points

2024-10-02T12:26:48.88+00:00

[deleted]

Share via

SQL Server shared LocalDB instance is not automatically starting

3 answers

Your answer