I am designing a multi-environment Windows-based software product that will extensively employ Windows services running as LocalService to perform data access operations. All of the data access endpoints are .NET applications that use Entity Framework to access the databases.
I don't want to use SQL Server authentication if I can avoid it; I want to use Windows authentication everywhere. Currently there are no roles or users at the database level for any of the system's databases. My current plan is to grant SELECT/INSERT/UPDATE/EXECUTE privileges to the LocalService accounts associated with each of the service host environments.
First, is that approach even possible? After a dizzying amount of research, I can't make sense of how SQL Server will distinguish between the LocalService account on two remote environments.
Second, am I missing a serious security implication? I understand the principle that a role should be limited and narrow in scope. I also understand that any other applications on the servers running as LocalService — including malware — will have database access using the current plan. The challenge that I'm facing is that the product is licensed and will be deployed on customer site, and I'm not sure how to tell our customers that our software needs to commission its own domain accounts or, even worse, that they need to commission them.
If it makes the most sense, I will just abandon the idea of using LocalService to access databases and plan on creating purpose-specific service accounts. My expertise is stretched beyond its limit for this particular problem. Thanks in advance for sharing your thoughts.
Best Answer
Machine accounts such as LocalService cannot be trusted over the network.
There is essentially no way to authenticate the remote machine without domain authentication of some kind.
Use SQL Server authentication; that's what it is meant for.
Alternately, you could provide a setup procedure that allows your clients to provide a domain account for each service to access the SQL Server database. You'd need to provide a mechanism where they can enter domain credentials for each service when they are setting up the server end.