I have two machines: WEB-FE and WEB-BE. WEB-BE is running SQL Server and on WEB-FE is running IIS. Both are on Server 2019, and are a part of a workgroup. They are not joined to any AD domain. We are using .NET 6.

My current working connection string is this:

Data Source=web-be;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyApp;Password=thebesteverpassword

I want to connect to the database via the application pool user and not use the userid/password combo, but I haven't been able to make that happen, in spite of my efforts.

As far as I know, the Application Pool needs to be set up and run as a local user, so that when it connects to SQL Server on the other machine, the user will be the same as what SQL Server has on its side for a user.

I haven't been able to find actual instructions on how to do this step by step, which surprises me. Most everything I've seen says that AD and integrated security are the way to go, but I can't do integrated security because these two machines are not on the domain and won't be; they're in a workgroup. I am able to connect by specifying the user/password combination in my appsettings.json, but since this is not a best practice, I want to not do that.

I would like to get instructions on what the appsettings.json connection string should look like, what set up needs to be done on the FE server with IIS, and what set up needs to be done on the BE server with SQL Server, so that when I make a database call (via Entity Framework Core) I'll get the data that I'm looking for.

CodePudding user response：

This should be as simple as

1. Create a local user with the same name and password on both server.
2. Create a login and user with permissions for this windows user in SQL Server.
3. Configure your IIS App Pool to run as this user, and use the in-process hosting model.
4. Use Integrated Security=true in your connection string.