0

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
NovaDev
  • 2,737
  • 5
  • 29
  • 43
  • I've never done Windows Authentication outside of either being in a domain or within the same computer. But my understanding is you must setup a user account on both machines with the the same username and password. Then you setup SQL Server to have a login for that user and the IIS App Pool must run as that user. – Joel Coehoorn Nov 14 '22 at 16:50
  • @JoelCoehoorn And you need to fiddle with User Rights Assignments too, as `w3wp.exe`'s user needs to be a member of the `IIS_IUSRS` group with the _Permission to run as a service_ set - if I remember correctly. – Dai Nov 14 '22 at 16:53
  • _"I want to connect to the database via the application pool user"_ - **why** do you want to do that? Without an AD Domain it's just added work, and it introduces more moving-parts (e.g. it'll break when passwords go out of sync) which means _more things that can go wrong_. You can still keep passwords out of connection-strings by using DPAPI to safely store secrets without needing to use SSPI/Integrated Auth - and you wouldn't be alone: Azure SQL _only_ supported `Password=`-based connections for years until recently. – Dai Nov 14 '22 at 16:55
  • @Dai - I want to do security according to best practices. If it means just have a plaintext connection string in my appsettings.json, then so be it. It seems that there is another way, DPAPI, which I haven't heard about. I looked it up, but could you send me in that direction? – NovaDev Nov 14 '22 at 17:06
  • DPAPI is documented here: https://learn.microsoft.com/en-us/dotnet/standard/security/how-to-use-data-protection – Dai Nov 14 '22 at 17:07
  • There are some instructions at the end of this [post](https://stackoverflow.com/a/70321177/10024425) (below "Find name of IIS application pool") which may be helpful. – Tu deschizi eu inchid Nov 14 '22 at 17:32

1 Answers1

1

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.
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Integrated Security means using AD, which I don't have. – NovaDev Nov 14 '22 at 17:03
  • 2
    No. Integrated Security means using SSPI, which is a wrapper for either NTLM or Kerberos. The configuration you are trying to set up will use NTLM. "Workgroup auth" is a valid form of Windows "Integrated Security". – David Browne - Microsoft Nov 14 '22 at 17:05
  • @DavidBrowne-Microsoft I remember trying to do this decades ago on a SQL Server 2005 set-up and I ran into all kinds of problems with DCOM Authorization, Windows Firewall, SPNs, etc - are things any better nowadays? – Dai Nov 14 '22 at 17:08
  • DCOM isn't involved in any way. SPNs are only for Kerberos. No additional network ports are required. Remote access to the old SSIS service required DCOM configuration and was generally useless. – David Browne - Microsoft Nov 14 '22 at 17:14
  • To complete the answer - connection string: "Data Source=web-be;Initial Catalog=MyDb;Persist Security Info=True;Integrated Security=true;". Set App Pool Identity on WEB-FE to MyApp/myawesomepassword. On WEB-BE, on SQL Server for the server security logins create MyApp/myawesomepassword with sql server auth. Make sure this user has perms for the database instance you care about. @DavidBrowne-Microsoft - Please let me know if this looks correct to you. Thanks! – NovaDev Nov 14 '22 at 20:07
  • You can refer to the local user as `\MyApp` on both the WEB-FE and SQL Server. And you create the login with windows auth, eg `create login [SqlHostName\MyApp] from windows` – David Browne - Microsoft Nov 14 '22 at 21:18