Accessing SQL Server from windows service

Question

I am trying to connect SQL Server from a Windows service. Now my Windows service is installed in different system. Now if I want to connect to my SQL Server, how can I connect? I am able to connect if I add the respective system userid in the SQL Server login users. I don't think that is the right way..because if i required to install the windows service in different system again I need to add that user to SQL Server.

Answer 1

Unless you're using trusted connections then the install process should request the credentials that should be used on that system.

Answer 2

I presume you are using InstallUtil to install the service. You should configure the service to run under a domain account. Let's call this YourDomain\WindowsServiceDomanAccount

In the connection string, use the tag INTEGRATED SECURITY=SSPI

On the SQL Server, create a login for YourDomain\WindowsServiceDomanAccount and grant it appropriate permissions.

You server should be configured for remote connections to allow non-local connections.