IIS, .NET Core 6, and SQL Server connection in a work group

Hires, Russell 1 Reputation point
2022-11-13T01:17:51.1+00:00

I have two machines WEB-FE and WEB-BE. On 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, not part of a domain, and not on AD. And my framework is .NET 6.

I want to connect to the database via the application pool, 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 alternatives are just not available. So that means I can't do integrated security. 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 Frame Work Core) I'll get the data that I'm looking for.

Thanks!

Internet Information Services
{count} votes

2 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 61,026 Reputation points
    2022-11-13T19:08:11.327+00:00

    typically when doing work groups and local accounts, you need to create the local account with the same name and password on all machine its will access.

    also you will need to open the firewall on the sql server machine and enable network access if you haven't already.

    not sure standard security is less secure in your case. just be sure to use encryption for the password in the connection string


  2. Albert Kallal 5,231 Reputation points
    2022-11-13T21:43:41.2+00:00

    Just use SQL server logons, and not try to use a windows authentication account.

    That way? Just setting up a standard connection string in web config will just work.

    Might have to open the firewall on the one web server, but other than checking that issue, setting up a SQL logon/user will in near all cases just work, and work without issues.

    you not have to mess with app pool or anything else.