we have a setup where our web server is hosting on a machine in our DMZ, which is behind a public facing firewall with the typical ports 80 and 443 directing web traffic to our web server. The DMZ is not domain controlled.
We also have several DB servers living inside our domain-controlled LAN, which is protected by a firewall from the DMZ, with the typical port 1433 open to allow the web site access to the SQL servers.
Currently, it is working, but only when our site's connection strings contain the user/pass to access the database. This is not ideal. What I'd like to do is to give the site's application pool identity access to the database from within SSMS and use a trusted connection in our connection string. However, I am having trouble understanding what kind of user account would be able to do this.
First, I tried creating a domain user in the LAN. Putting the DOMAIN\User, and password into the application pool identity in IIS, appeared to work correctly, and I was able to add the domain user as a SQL login as well. However, when I tried to run the application and connect to the database, the following error occurred:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: ]
Error: 18452, Severity: 14, State: 1.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: ]
Using the web app to output its current user name revealed to me that the application identity was being changed from the domain account to a (non-existent) account on the web server, instead of the expected "DOMAIN\WebAppUser" I put into the application pool identity in IIS, it was outputting "SERVER\WebAppUser", which is what I believe was the reason for the error message when connecting to the DB.
I then tried creating a local account on the web server and granting that access in SSMS. However, SQL it is unable to find the user I created on the web server, I assume due to it's being outside of the LAN area the SQL server is in.
I have read other threads and articles, which indicate that we may need to set up a trust relationship between the DMZ space and the LAN space, which would mean setting up a domain controller for our DMZ. I would prefer to avoid the added cost/complexity of this solution.
Is there some way to grant a user the type of network access needed to accomplish this? Thanks.