I've built a Blazor application, but when we deployed it into a Testing environment, I received the following error:
An exception occurred while iterating over the results of a query for context type 'TestApp.Data.DBContext.LookupDbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
But I'm not entirely sure why it is attempting to send in NT AUTHORITY\ANONYMOUS LOGON when my connection string contains a User ID field.
"DefaultConnection": "Server=testServer;Initial Catalog=TestDatabase;User ID=user;Password=password;Encrypt=False;Trusted_Connection=True;"
When I was debugging and using the application locally, I never experienced this error, and my connection string was the same.
The Trusted_Connection=True part of the connection string means to use the authentication token issued for the user who owns the process, instead of a username/password. When building and testing locally, this tends to be a token issued for your user account, which likely also has access to the database. Once deployed to a server your user is no longer present, and so we have something else. The actual user depends on how the application is configured, and in this case it seems to be NT AUTHORITY\ANONYMOUS LOGON. Other common users are NT Service or something from the IIS_IUSRS group, though these are fading as more apps move to native .NET Core.
Remove the Trusted_Connection section from the connection string, and things should function. Just remember to take very good care your connection string is well-protected on your web server.
If I could further prattle on for a minute.
Personally, anything NEW I build I want to use some kind of certificate-based authentication for this rather than username/password. For SQL Server, that means using the Trusted_Connection option and configuring the app to run under the desired user account. But I also don't want any public web sites running as a privileged user. If there is a security issue elsewhere that allows an attacker to execute arbitrary code under this user account, whether the issue is in the web server software/OS or my own app, I want to limit that damage.
This means I now (again: for anything new; it's less often worthwhile to re-architect existing functional software) tend to build a separate data service application, which is not directly accessible from the outside. My actual web application will run as an unprivileged user and make requests to the data service, similar to API calls. It will not have access to talk directly to the database. The data service application will run as a user with database access and use a trusted connection to connect to the database (which is certificates under the hood). It then passes the data back to the public web app.
This now resembles a classic service oriented architecture, where the data service application is also a great place to add things like caching or connections to other data sources, lets me move tasks like sorting off of the expensive SQL Server CPU cores, scales out better than the database itself, consolidates business rules, and helps modularize the application with separate pieces for, say, internal sales vs public retail but can still both talk to the same data service.