Logging into SQL Server from Windows Service

[Andy Baker]

I have an Windows forms application written in VB.NET that uses a SQL Server
2000 back end database with Windows authentication. There is no problem
accessing the database from my application, or from an installed version of
the application on another machine. My problem is that I would like to
access the database overnight unattended to perform some maintenance tasks.
With my fairly limited knowledge of SQL Server and VB.NET, I thought that
the best way to do this would be using a windows service with a timer, so
that it could be running all the time while windows is running and the timer
could perform the maintenance at the correct time. I created a new Windows
Service project and added a reference to the same data access .dll that my
main application uses. The service installs and starts OK, and I can use the
methods in the external .dll, but whenever I try to access the database, I
get the error message 'Login failed for user 'DOMAIN2K\STAT28$'. The server,
running Windows 2000 Server, is DOMAIN2K, and my username is STAT28 which is
an Administrator on DOMAIN2K. My questions are 1) Is a windows service the
best way to do this? and 2) Why am I getting a login error using the same
data access functions that work fine in the main application? Thanks in
advance.

Andy Baker

 

[ECathell]

how have you setup your service to run? I think in order for you to do what
you want you have to install it using a domain user account that has the SQL
permissions you want.

 

[zacks]

By default, a Windows Service logs into the special local "SYSTEM"
account. This account does not have access to any network objects, SQL
or otherwise. You'll need to change the service startup parameters to
log into a domain account instead.

 

[Andy Baker]

Thanks for the replies. I have created my service, then following the
instructions in Microsoft's Visual Basic.NET programmer's cookbook, I have
added an installer to the service project and then used InstallUtil to
install the service.Part of the code that is generated by the installer is

'
'ServiceProcessInstaller1
'
Me.ServiceProcessInstaller1.Account =
ServiceProcess.ServiceAccount.LocalSystem
Me.ServiceProcessInstaller1.Password = Nothing
Me.ServiceProcessInstaller1.Username = Nothing

'
'ServiceInstaller1
'
Me.ServiceInstaller1.ServiceName = "VPHousekeeping" 'Name of
maintenance service
Me.ServiceInstaller1.StartType =
ServiceProcess.ServiceStartMode.Automatic
'
'ProjectInstaller
'
Me.Installers.AddRange(New System.Configuration.Install.Installer()
{Me.ServiceProcessInstaller1, Me.ServiceInstaller1})


If I then change it to

Me.ServiceProcessInstaller1.Account =
ServiceProcess.ServiceAccount.NetworkService
Me.ServiceProcessInstaller1.Password = ""
Me.ServiceProcessInstaller1.Username = "STAT28"

I still get the logon error - also if I use
ServiceProcess.ServiceAccount.User. STAT28 is an administrator on the
domain, and there is no logon password (yet). I'm obviously missing
something, but I can't see what it is.

Regards,

Andy Baker

 

[Andy Baker]

Found it - if I change to

Me.ServiceProcessInstaller1.Username = "DOMAIN2K\STAT28"

it works OK.