I have two VB 2005 applications, one is a Windows Service and the other is a forms-based application. Both connect to the same SQL Server Express 2005 database.

Each program will successfully connect to the database if started by itself, but both will not connect simultaneously. If I copy the code from the service in to a forms-app then they both will connect simultaneously. The problem is when one program is a forms-based application and the other is a service.

The connection strings for both applications are the same:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\SmartTall yDE.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

The error message that I get when the service fails to connect when the other application is running is:

Service cannot be started. System.Data.SqlClient.SqlException: Cannot open database "C:\SMARTTALLYDE.MDF" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\SYSTEM'.

As I said before both programs work by themselves, and if both are a forms-based application they can both connect simultaneously. The problem is that they cannot connect when one is a forms-based app and the other is a service.

Any ideas? Thanks in advance.

When you manually start each program, they are running under your login credentials which may be a domain account. Those credentials has access to the SQL server.

When you run the program as a service it is running under the LOCAL machine account. The SQL server system doesn't know anything about this account so it is denying access.

You need one of the following changes:

- change the logon account the service runs under. This could be set to your personal account as you know that works. This can be problematic though if you have a domain password policy that requires regular changes (monthly for example). If you forget to update the service when you change your account password, the service will fail and potentially lock your account after several failed attempts.

- Change the database connection string to use SQL authentication. This is by far the easier approach as it removes windows/active directory authentication from the whole equation. Of course, this is slightly less secure because the SQL login and password is stored in your application configuration. However there are ways of encrypting it so it's not humanly readable.

-Peter

Hi Planoie,

Thank you for your response. I think I'll try using SQL Authentication and see how that works out.

You said something:
"When you run the program as a service it is running under the LOCAL machine account. The SQL server system doesn't know anything about this account so it is denying access."

The Windows Service does and will connect to SQL Server successfully--as long as the other app isn't running first. If I start the service first, then try to launch the other application it will fail to connect to the database. I don't know if this will be corrected with using SQL Authentication or not but I'll give it a shot.

Thanks again

Jeff

I'm not sure how that is possible. However, with windows handling the security there could be all kinds of magic going on that we don't see. Perhaps by making the first connection from the winform app with authorized credentials the subsequent requests from the windows service get accepted. Either way, you'll definately eliminate this behavior by using SQL authentication.

-Peter