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 database with Trusted Connection / 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

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....