Can't connect to SQL LocalDB from Windows Service, WPF app & SSMS work fine?

Question

Having some trouble with a system I'm developing. The layout as far as this question is concerned is a WiX built msi installer which installs SQL LocalDB 2012, a WPF app, and a Windows Service. Both the Windows Service and the WPF app are to communicate with the same database, the app driven by user interaction and the service on a timer.

Everything gets installed cleanly, the DB instance gets installed in mixed mode and so my database initializer creates a new login and user for the instance and db, and in SSMS they both appear to be configured correctly. I can log in and query the tables just fine using the new login. Here is the code I used to create the login:

IF NOT EXISTS 
(SELECT loginname 
 FROM master.dbo.syslogins WHERE name = 'BP_SERVICELOGIN') 
 BEGIN 
   CREATE LOGIN[BP_SERVICELOGIN] WITH PASSWORD = 'pw';
   CREATE USER[bpUser] FOR LOGIN[BP_SERVICELOGIN] WITH DEFAULT_SCHEMA = dbo;     
   USE DatabaseName; 
   EXEC sp_addrolemember 'db_owner', 'bpUser' 
 END

So I reiterate; this login works just fine in SSMS and allows me to log in and access the database that was created by the WPF app's initializer.

However, I am NOT able to login to this database from the Windows Service. I am making sure to use the new login in my connection string and everything is properly set up there. How do I know this? Because when I copy the connection string to my WPF app and use that instead of the Windows auth, it WORKS!?!

The service constantly fails with the message:

"System.Data.Entity.Core.EntityException: The underlying provider failed on Open. in EntityFramework:File: Method:Open Line:0 Column:0   System.Data.SqlClient.SqlException: Login failed for user 'BP_SERVICELOGIN'. in .Net SqlClient Data"

Here is the connection string I'm using:

<add name="ConnStringName" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=DbName;User Id=BP_SERVICELOGIN;Password=pw;MultipleActiveResultSets=True;Application Name=ServiceName" />

It seems like there is some barrier that is keeping the connection from the Windows Service from connecting properly while others have no such problem. The LocalDB installation is fresh and other than the DB and the Login/User created, has nothing done to it from an OOB state.

Can anyone help?

Answer 1

I'm not a professional in windows services.

I think the fault is in the windows service you have written. With the session zero isolation you may have to run the windows service in the specified user account.
It would be helpful if you can give the user account which the windows service runs. In default the service runs in a different user account called SYSTEM account. This account may not have permission to access the database. This might cause the problem.