and I get System.ComponentModel.Win32.Exception:the comparison between user names and security identifiers haven't been executed.

What do I do wrong?

Edit 3: I set my username and password in services.msc and processInstaller "Account" property I left with "Local System". It runs but when I change the image (and the event handler trying to access my database) I get:

Cannot attach file 'C:\Users\Aleksey\repos\working-copy\WebStore\WebStore\App_Data\WebStore.mdf' as database 'WebStore' because this file is already in use for database 'C:\USERS\ALEKSEY\REPOS\WORKING-COPY\WEBSTORE\WEBSTORE\APP_DATA\WEBSTORE.MDF',

In the connection string I added credentials of user which I've created in the database:

User Id=user1;PASSWORD=password1;

Besides, when I'm trying to add connection in server explorer and use Sql Server Authentication and I enter user1 and password1 I get the message from Edit 1.

Should I use in server explorer *Windows Authentication* and in windows service in the connection string: User Id=user1;PASSWORD=password1; ?

does the login exception still happen when you debug the code under your own credentials/identity? Also, I rarely use the @ directive on strings, does that change the behavior of the '\' such that '\v' is correct or should it be '\\v'? (Again, I rarely use the @ and do not know.)
–
Mike_Matthews_IIFeb 26 '13 at 16:15

2

Create new login in the database and pass user name and password in the connection string.
–
Shadow WizardFeb 26 '13 at 16:16

Have you tried opening it with the network service account? That account should bypass traditional logins. As it will act like the computer on the network, so it shouldn't have an issue interfacing with SQL. msdn.microsoft.com/en-us/library/windows/desktop/…
–
GregFeb 26 '13 at 16:16

I haven't tried to open it with the network service account. Will check the link
–
Aleksei ChepovoiFeb 26 '13 at 16:18

1

Does the System user have log in rights to your sql server, and does it have permission to the WebStore DB? Your windows service is running as this user and trying to connect with those credentials. I don't think the problem is two simultaneous connections. You can also change who your service runs as through its properties.
–
Brent StewartFeb 26 '13 at 16:19

1 Answer
1

This happens because your service is running with the LocalSystem account, and you're using integrated security to connect to SQL server, and SYSTEM shouldn't have access there. So threechoices:

Switch to SQL Server authentication. That includes creating a login in the database and modifying the connection string

Run the service with a particular user with the only the privileges it really needs (this is actually a good idea) and give that user access to the database so it can connect with integrated security. This is the easiest because it doesn't require any changes to the code

If you really need to run as SYSTEM, then can use impersonation to switch to another user (like the user from option 2) with LogonUser. That way you end up in option 2 just for the database access and retain SYSTEM priviliveges for the rest of the service code.

The second error

InvalidOperationException: Opened and available connection is needed for ExecuteNonQuery. Connection is closed.

Is caused by the first error, failure to open the connection brings this up. You can't execute anything without an open connection, ADO.Net makes sure of that.

So the first error is the only one that matters.

I recomend that you go with option two as is the best in terms of security practices, and since you are running in integrated security you don't have to store any password, and thats a plus when you have your application in several environments with different databases, users, passwords and what have you. Just a user per enviroment, permissions and you're set.

Don't set the user on the installation, run services.msc and edit the service properties there
–
Carlos GrappaFeb 27 '13 at 17:49

Can You describe the process step by step, cause I don't completely get how and what to do. Thanks in advance!
–
Aleksei ChepovoiFeb 28 '13 at 7:16

Run services.msc as an administrative user. Locate your service, double click, go to the log on tab, change from local system to "This Account", put a user that has access to sql server with integrated security
–
Carlos GrappaFeb 28 '13 at 15:16