ASP.NET Core - Application not connecting to database after publishing

Question

I created a simple ASP.Net Core application with user authentication (so all the Entity Framework has been preloaded into the web app template). It is connecting to my database with the connectionString that is located in my appsettings.json file, with "data source = {computerName}\\{serverName}" setup.

The database instance and Visual Studio are located on the same machine. This works fine in returning data to the web api while within development and debugging mode. When I publish it and try to go to the site from a domain name it does allow me to view the webpages that are not needing database connection but the rest that need data from the database returns pages like this:

Error.
An error occurred while processing your request. Development Mode Swapping to Development environment will display more detailed information about the error that occurred.

Development environment should not be enabled in deployed applications, as it can result in sensitive information from exceptions being displayed to end users. For local debugging, development environment can be enabled by setting the ASPNETCORE_ENVIRONMENT environment variable to Development, and restarting the application.

I have tried to change the Environment Variables for ASPNETCORE_ENVIRONMENT from Development to Production to no success.

I have tried adding appsettings.Production.json to publishOptions in my project.json file, even though there is no file appsettings.Production.json, and this did not help. dotnet publish

Adding the evironment variable in the web.config file did not work Deployment

I need help getting the published web api to connect to my SQL Server database from outside the development Visual Studio setup.

The last thing I can think of is that maybe I am incorrect in how I understand the connection string. If the web api uses the connection string to connect to the database from the server-side then it should work just fine like it does when in development calling to http://localhost:port# since it's all on the same machine. But, if the database string needs to be based on a client-side call then it would have to be with domain names and IP addresses. Can someone tell me which one it is?

The only thing else that comes to mind is that there is something I am not doing, and need to do, inside of IIS Manager. I see connect string there as well but unsure what that is for our does since the connection string is inside the application. Also maybe I am suppose to give the app some kind of authorization to communicate with the database server even though they are on the same machine???

Answer 1

After much research, finally googling "how to deploy a web api in iis" I was able to learn from Host ASP.NET Web API in IIS using Visual Studio Publish that I needed to add the security entity BUILTIN\IIS_IUSRS. Then placing a mapping to my database tables and giving db_datareader (and possibly db_datawriter) for the database to the IUSER to allow access from my self hosted IIS web api. This from the above mentioned link with the part stating

Accessing Database under IIS APPPOOL\ASP.NET v4.0 As we are using ASP.NET v4.0 App Pool make sure IIS APPPOOL\ASP.NET v4.0 is added to your Database Server -> Security -> Logins.