Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > Pro Visual Basic 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro Visual Basic 2005 For advanced Visual Basic coders working in version 2005. Beginning-level questions will be redirected to other forums, including Beginning VB 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro Visual Basic 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 20th, 2007, 01:51 PM
Registered User
 
Join Date: Nov 2007
Location: Prince George, BC, Canada.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Accessing SQL Server from a Windows Service

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.

  #2 (permalink)  
Old November 21st, 2007, 10:19 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,409
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
  #3 (permalink)  
Old November 21st, 2007, 01:46 PM
Registered User
 
Join Date: Nov 2007
Location: Prince George, BC, Canada.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old November 21st, 2007, 04:18 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,409
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Reporting Service gunjan.sh Reporting Services 1 June 24th, 2008 02:47 PM
Problem in accessing DTS in SQL SERVER 2005 Jigna SQL Server DTS 1 September 12th, 2007 04:23 PM
SQL Server Analysis Service sankarkumar_d Reporting Services 0 January 8th, 2007 01:44 AM
Accessing Windows service from a windows app sajid08 C# 1 October 6th, 2006 10:25 AM
Sql Server Service Doesn't Start barissql SQL Server 2000 3 June 2nd, 2005 05:04 PM



All times are GMT -4. The time now is 04:17 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.