The exception Cannot open database requested by the login. The login failed. Login failed for user ‘NT AUTHORITY\LOCAL SERVICE’ occurs when we try to connect the database via application when we host the ASP.NET application on IIS Server. This exception specifically showing for the user NT AUTHORITY\LOCAL SERVICE.
I can say this article actually the continuation of Cannot open database requested by the login. The login failed. Login failed for user IIS APPPOOL. When I tried to host my web application on IIS Server, I faced three issues while make it work successfully. Among the three, the first issue already solved in the above article, and this is the second issue.
As discussed in the previous article It is recommended that using Windows Authentication to login into the database via the application with Trusted_Connection=True in the production environment for high security. Additional to this we supposed to use NT AUTHORITY\LOCAL SERVICE user to communicate between the application and the database via IIS Web Server.
Problem
As the issue description says, NT AUTHORITY\LOCAL SERVICE is not able to communicate with the database via IIS Web Server. We have changed the website App Pool Identity from ApplicationPoolIdenty to LocalService. After this change, LocalService tried to communicate with the database and failed to login into the database.
Solution
The solution would be for this issue is to give access to NT AUTHORITY\LOCAL SERVICE in the database. The steps to solve the issue by giving access to NT AUTHORITY\LOCAL SERVICE are:
Step 1: Go to the database and navigate to Security -> Logins folder.
Step 2: Right click on Logins folder and select New Login.
Step 3: In the new Login window select Windows Authentication and hit Search button
Step 4: In Select User or Group window click on Advanced button.
Step 5: Then click on Find Now button.
Step 6: Search and select LOCAL SERVICE from the list. Then hit Ok
Step 7: Hit ok in User or Group window
Step 8: Hit ok in Login New window
Step 9: Now under Logins folder we can able to see NT AUTHORITY\LOCAL SERVICE user.
Conclusion
Now LocalService can log in and access the database via IIS web server from the application. Out of my three issues (issues which I got when hosted my website for the first time in IIS) this is the second issue and below are the other two issues. After this step user may get the third issue that I faced during my hosting.