10

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?

hcp
  • 486
  • 1
  • 3
  • 11
  • 1
    Hi! Did you try [this](http://stackoverflow.com/questions/26597498/using-sql-localdb-in-a-windows-service)? – Sinix Nov 03 '15 at 06:58
  • Are you using multiple instances?! If you are, please try and add the instance name to the connection strings. – Cucu Nov 09 '15 at 12:36
  • SqlLocalDb info "MyInstance", what does it return? – Cucu Nov 09 '15 at 12:40
  • Most likely a permission issue... did you have a look here? http://stackoverflow.com/questions/26597498/using-sql-localdb-in-a-windows-service – fuchs777 Nov 09 '15 at 14:08
  • Cucu: I am not using multiple instances, this solution must work from a distributed installer, so I have just run the default installation path of LocalDB and am using the automatic instance running in mixed mode. – hcp Nov 09 '15 at 16:08
  • Sinix and fuchs777, the post you have linked is not quite the same as the problem i am having, because I CAN see the database instance from the Windows Service, but it is refusing my attempts to connect. I do however believe that the linked post may have something to do with the same root cause of this issue, even though it doesn't quite solve the issue I'm having... – hcp Nov 09 '15 at 16:19

3 Answers3

6

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.

Pulathisi Bandara
  • 98
  • 1
  • 12
  • The service is running as NT AUTHORITY/SYSTEM, which you're correct, doesn't have rights to connect or access the database. However, the connection string I've provided to the service specifies a Sql Authentication account which is correctly configured in the database, and based on the logs it's returning it is actually trying to use this account to connect instead of SYSTEM. – hcp Nov 04 '15 at 19:44
  • Did you try changing the connection string like this? `"Data Source=(localdb)\.\v11.0;Initial Catalog=DbName;User Id=BP_SERVICELOGIN;Password=pw;MultipleActiveResultSets=True;Application Name=ServiceName"` And also try using a shared instance of the LocalDB as shown in [this](http://stackoverflow.com/questions/21830727/how-to-access-localdb-from-windows-service) Somehow it has worked for him. – Pulathisi Bandara Nov 05 '15 at 09:43
  • 1
    Alexander Derck: Do you mean running the Windows Service as a network service? I can try this, but iirc all this would change would mean the service would be running as NT AUTHORITY/NETWORK SERVICE instead of NT AUTHORITY/SYSTEM which would have less permission if anything, no? And in any case, I am not trying to connect to the db server as the running account, I have provided valid SQL Authentication creds in the connection string and ensured the db is running in mixed auth mode. – hcp Nov 09 '15 at 16:06
  • 1
    I've chosen this as the correct answer, as I think Pulathisi is essentially correct, it's a problem of session isolation and LocalDB. LocalDB doesn't want to allow a process running under a non-user account to connect for some reason. I'm under time constraints, so in order to avoid further problems in the future I've just installed SQL Express instead, even though that increases the size of my installer. But I think that if I were to have set up my service to run in a User context, it would have been able to connect to the db. – hcp Nov 10 '15 at 14:51
0

Can you check if this is the problem you are experiencing, and the solution proposed helps you?

http://www.codeproject.com/Tips/775607/How-to-fix-LocalDB-Requested-Login-failed

What you are describing is odd, everything looks good from your description but the fact that is a localdb database is suspicious.

Erlis Vidal
  • 171
  • 1
  • 5
  • This is not the issue, there is nothing wrong with the instance installed, as a login from another application running on the desktop can login and access data with no issues. It definitely smells more like a permissions issue. – hcp Nov 09 '15 at 16:04
0

Have you tried adding the Integrated Security=True; in the connection string?

Kashif Khan
  • 685
  • 2
  • 11
  • 30
  • An answer should not contain a question. You might want to read this http://stackoverflow.com/help/how-to-answer – fuchs777 Nov 09 '15 at 14:25
  • All this does is change the user passed to the LocalDB installation back to NT AUTHORITY/SYSTEM, which is not going to work for this environment. – hcp Nov 09 '15 at 16:03