How to connect to sql-server with windows authentication from windows-service?

Question

I have programmed a Windows Service in C# which should connect to an SQL-Server 2005 Express Database with System.Data.SqlClient.

As Microsoft prefers to use Windows Authentication over SQL Authentication I tried to connect to the DB with Trusted Conenction / Integrated Security.

However that doesn't work as I get a System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\LOCAL SERVICE'.

Is it possible to login with a different Windows Account?

Answer 1

You need to grant access to the LOCAL SERVICE account in SQL Express. As per my comment, my advice would be to create a new account which your service can run under and then add the relevant permissions inside SQL Express, in other words, don't run your service under LOCAL SERVICE.

Answer 2

If you want to use trusted windows authentication, the easiest thing to do is to run the service with a domain account (with the least privilleges required) that is permissioned to the sql server database.

Answer 3

At the moment, the service appears to currently run under the LocalService Account and this service account is not currently authorized on SQL.

The situation can be fixed in one of two ways: running the account under an account whichis authorized in SQL or add the LocalService account as a login in SQL. Specifically:

• Change which account the service is ran as, in the Service management console. (ex: Computer Management | Services and Applications | Services then right-click for "Properties" on the service in question)
• Alternatively, in "Microsoft SQL Management Studio", add the LSA account as a login and set this new principal (login) in a way that it can access the desired database objects.

Edit: The first approach is probably preferable, because the LocalService account is so pervasively found in the system that granting it access to SQL would expose SQL and the databases would a particular service or driver using it become compromised.
Instead by introducing a specific account one has more control over who accesses SQL objects and how. This of course bring the issue of configuring such an account, with regards to the privileges it should be granted, at the level of the system (not of SQL), and depending on what the underlying Service does, on may need to make this account rather powerful, hence a potential liability in other ways....

Answer 4

When a Windows Service is started by the Service Control Manager, the process executes as a particular user just as with any other process running on the OS.

There are a number of "built-in" user accounts that are used for running Windows Services. You can see the complete set of Windows Services and the account they run as (called "Log On As" in Windows 7) if you look at the "Services" node within Computer Management".

From my experience, when we want a Windows Service to talk to the DB using integrated security we take the second approach below:

1) Assign one of the built-in accounts as the "Log On As" account and add this account as a login on the SQL Server instance with the appropriate DB permissions

2) Use/create a local or domain account for the Windows Service to use and then add this account as a login with the appropriate DB permissions. It is possible thru the installer to prompt for the user account credentials during the installation of the service.

I can't claim to be expert enough to point out all pros and cons of each approach, however it's worth considering the following:

• with approach 1 all services and processes that run as the selected built-in account will have permission to access you database. This is not the case with approach 2.

• with approach 1 the password configuration is managed by the machine itself but with approach 2 the password can be managed by the administrators and also conform to any required security policies in place.

I hope this helps

Answer 5

As mjv said, you need to either give Local Service Account access to the database or use a different account to run the service. You asked how you can change the account programmatically, which is accomplished by creating an Installer for the service and changing the Account property to "User" then specifying the Username and Password to run the service.

The following link contains information on creating the installer if you scroll to the bottom:

http://msdn.microsoft.com/en-us/library/aa984464(VS.71).aspx

While the following provides a little more detail about the Account property:

http://msdn.microsoft.com/en-us/library/system.serviceprocess.serviceprocessinstaller.account(VS.71).aspx

Hope this helps.