I created a VS setup project in which I added a custom action to create an SQL Server database. I used the code from here https://daoudisamir.com/install-sql-server-database-with-visual-studio-setup/ that is itself a c# adaptation of the Microsoft Docs code here: https://learn.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2010/49b92ztk(v=vs.100). I made a little adaptation - the entire connection string is in the code:
string connStr = "Data Source = (localdb)\\MSSQLLocalDB; Initial Catalog = master; Integrated Security=True";
This way, I'm not using any dialogue during the installation and it starts the custom action without any input from the user.
But when trying to the installation file, I get each time a "Failed to connect to server (localdb)\MSSQLLocalDB" error. Some of the times, the inner exception message that I have in the log file said "Login failed for user 'NT AUTHORITY\SYSTEM'", sometimes just "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found".
I know for a fact it's not the code in the custom action - I copied it to a form and added a button for the db creation via the application itself - no problem, no connection problem, db created right away.
I must add, that previously, I had a prerequisite for installing SQL Server Local DB during the application setup. That ended up miserably - it blocked me from having ANY access to the server - I had to uninstall everything VS and reinstall again before it went back to normal.
It seems to me that for some reason, instead of using the default windows user that is running the setup file, for some reason it switches to a different user. Is running the code via an msi file causing this? How can I solve it? It's been 2 full days, I read many threads and didn't find the answer.
Thank you for any help.
Edit: The LocalDB documentation states specifically that
An instance of LocalDB owned by the built-in accounts such as NT AUTHORITY\SYSTEM can have manageability issues due to windows file system redirection. Instead use a normal windows account as the owner.
HOWEVER, I didn't choose to run under SYSTEM. For some reason it switches to this user, while after the installation when the application is running with the same connection string, it uses the normal Windows user as expected.
Why is it switching to this user? That's what I'm trying to figure out. I have windows 10 if it matters.