The facts:
- If I use a normal domain user XYZ, to "log on as" the service all works fine, which implies the database exists and the connection string is good.
- If I use Local Service, I get Error 18456, State 38
- I have created
<DOMAIN>\<Machine>$login account - I have given the same permissions to
<DOMAIN>\<MACHINE>$as XYZ, including user mapping to the DB, server roles, securables, and status
My connection string is:
"SERVER=localhost;DATABASE=<TARGET_DATABASE>;TRUSTED_CONNECTION=True;Persist Security Info=False;"
The error message says:
Login failed for user '<DOMAIN>\<MACHINE>$'. Reason: Failed to open the explicitly specified database '<TARGET_DATABASE>'. [CLIENT: <local machine>]
Note: in above: [CLIENT: <local machine>] is the actual text, not substituted.
Other Considerations
This comes from a website that is being installed via a WIX setup program. The setup installs the service and I can select what account to use, currently it is using "Local System", but if that is bad practice we can change it, but it looks like every other service (other than the SQL ones) are using that same pattern.