Windows Service fails to connect to Microsoft's LocalDB

Question

GOAL:

I am trying to have my "Windows Service" connect to MS SQL Server's LocalDB. When I start my "Windows Service" via the Control Panel's Services, it fails. My same "Windows Service" works running under Visual Studio.

(My "Windows Service" uses LocalService according to Control Panel's Services window).

ATTEMPTED:

1) Fails with LocalService.

Error is: "Login failed for user 'NT AUTHORITY\LOCAL SERVICE' ".

My connection string for the error: add name="LocalDB" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=MyDatabase;Integrated Security=True"

2) I have added a SQL Server login of [NT AUTHORITY\LOCAL SERVICE] on (localDB)\v11.0 and granted db_owner, db_datareader, and db_datawriter access to MyDatabase via SSMS. (This automatically creates the database user in the Security\Users\ folder under MyDatabase). I still get the error.

3) I have added a SQL Server login of EngineUser on (localDB)\v11.0 and granted db_owner, db_datareader, and db_datawriter access to MyDatabase via SSMS with the password of the same.

I have changed my connection string of my WindowsService to:

add name="LocalDB" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=MyDatabase;Integrated Security=false;User Id=EngineUser;Password=EngineUser;Connection Timeout=30"

Get error "Login failed for user 'EngineUser' ".

I have gone out to command prompt running as administrator and confirmed that user and password are good by doing: sqlcmd -S (localDb)\v11.0 - U EngineUser -P EngineUser .

I have connected in SSMS to (localDb)\v11.0 using "SQL Server Authentication" with EngineUser and same password. Also confirmed that I can select and update in MyDatabase using this connection.

4) I have a working WPF application that uses MyDatabase on (localDB)\v11.0 with no issues, but remember that this is a traditional application rather than a "Windows Service".

5) Running the source code of my "Windows Service" in Visual Studio, the DB connection works fine and rows of data are selected and inserted.

6) I have tried making a shared instance of the localDB, but failed to get this to fully work and would not fit my business requirements.

7) Verified that my (localDb)\v11.0 has "SQL Server and Windows Authentication Mode" on (a.k.a. mixed mode).

8) Verified that the Login Properties of EngineUser and [NT AUTHORITY\LOCAL SERVICE] have "grant permission to connect to database engine" and Login is "enabled".

9) Used MS Sql Server's Profiler connecting to actively trace (localDB)\v11.0 and I see activity running my WindowService in Visual Studio and I do NOT see any activity starting my WindowsService in the Services screen of the control panel. Even if I turn on "all events" to see the full list of events and check mark all the error events and select with "Audit Login" and "Audit Login Failed" turned on.

10) I have looked at similar threads such as: The login failed. Login failed for user 'NT AUTHORITY\NETWORK

Answer 1

Found my answer. There are different LocalDb for each separate Windows account. Since I had installed the application when I was logged on, it installed the LocalDb under my CORP\ewood account. The Windows Service was running as "LocalService" it was looking for a different non-existing LocalDb database. My solution is to change the Windows Service when it installs to "Log On As" the user that originally installed the application and thus created the LocalDb. So I need to set ServiceProcessInstaller's Account, Username, and Password as installing user.