3

A bit of background first. I have an ASP.NET application which lives in the DMZ on our network. It needs to access SQL Server instances on machines which live within our trusted network. I will call the web server WebServer1 and an example SQL Server SqlServer1.

Currently, our web server is using connection strings which contain a user ID and password. This is not ideal, and we'd like to use Windows authentication to connect to the DB instead.

To do this, I set up a user account & password on our domain, I'll call it MyDomain\WebApp. I set the identity for our site's application pool to this new account, and set the anonymous authentication for the site to use the application pool's identity.

However, when I try to change our connection strings, replacing the UserID=fakeuser, Password=fakepass with Trusted_Connection=Yes, initiating a database call from the web application results in an error message

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Edit: here is the full error message from the SQL Server error log:

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: ]

To see what was going on, I thought I'd set up a simple page that would tell me what the current user is on the worker process. This is where I ran into very strange results. Here are the results and their server variables that gave them (with real names replaced with names mentioned above)

  1. WebServer1\WebApp = WindowsIdentity.GetCurrent().Name
  2. WebApp = Environment.UserName
  3. MyDomain\WebApp = pool.ProcessModel.UserName
  4. WebServer1\WebApp = Request.LogonUserIdentity.Name

1 and 4 are very strange, and do not exist as user accounts on the web server. Only #3 is returning the correct value seen in the IIS application pool for the site. This was gotten using the method in the comment on the following SO answer: https://stackoverflow.com/a/10101417/711674.

My assumption is that is not the account that is actually being used when the server is attempting to authenticate with the SQL Server instance, and one of the other values is being used, resulting in the error message mentioned above.

Community
  • 1
  • 1
Will P.
  • 8,437
  • 3
  • 36
  • 45
  • Check your IIS "Anonymous Authentication" settings and ensure it's set to "Use Application Pool Identity" - IIS and ASP.NET do Identity Impersonation. – Dai Aug 27 '16 at 00:45
  • What authentication system is your application using? Are you using your own authentication database, or are you using Windows Authentication (in ASP.NET - unrelated to the SQL Server)? If so, you may have hit a wall: getting Windows Authentication in web-applications to work with Impersonation and SQL Server is difficult. – Dai Aug 27 '16 at 00:47
  • We are using our own authentication in the DB. We currently use a connection string in the web.config with user & password specified to connect to the database, which is working fine. However I'd like to use a trusted connection instead with no user/password in the connection string. Is this very difficult? What would be the most secure alternative if so? Thanks for your help. – Will P. Aug 27 '16 at 00:53
  • Also, in response to your first comment, thank you for that, it was indeed set to the wrong user (that is where the admin user must have come from) someone must have set it up that way back when it was first configured. I will edit my question accordingly. However, I am still receiving the same error when a DB connection without user/password is made from the web application, and #4 from my question is now showing "WebServer1\WebApp" – Will P. Aug 27 '16 at 01:01
  • "WebServer1\WebApp" means the website is running under a Local User account, not the domain's user account ("MyDomain\WebApp"). Change your Application Pool to use the Domain Account instead of the Local User account, then it should just work. – Dai Aug 27 '16 at 01:06
  • 1
    The application pool is set to the domain user's account with the full name. However, I'm starting to think that the plan may not work because of the network configuration, with our web server on the DMZ and our DB server on the trusted internal network. i managed to cause an error in our test site when I set up impersonation to use the MyDomain\WebApp identity with password included there, `Could not create Windows user token from the credentials specified in the config file. Error from the operating system 'Logon failure: unknown user name or bad password` – Will P. Aug 27 '16 at 01:23
  • It appears that the application pool identity is configured to look at MyDomain but it is instead automatically replacing that with WebServer1 when it is unable to connect that domain's user. – Will P. Aug 27 '16 at 01:24

1 Answers1

3

There is a one good article about that matter here. In few words:

You need to answer on two main questions:

  1. What was the authentication mode for IIS? (Anonymous authentication? Windows authentication? Both?)

  2. Was impersonation for the FastCGI module on or off? (i.e. Was the fastcgi.impersonate setting in my php.ini file set to 0 or 1?)

A. IIS Anonymous Authentication enabled and fastcgi.impersonate = 1:

Because I was connecting to IIS anonymously, the built-in anonymous account (which is NT AUTHORITY\IUSERby default in IIS 7.0+) was impersonated. So, my connection attempt failed because this identity does not map to a valid login on my server.

B. IIS Anonymous Authentication enabled and fastcgi.impersonate = 0:

Because impersonation was off, my identity was not used as the identity of the PHP process. Instead, the actual identity of the PHP process was used in the connection attempt. In IIS 7.5, the identity of the PHP process depends on the identity of the application pool it is running in. In this case, PHP was in the default application pool and the identity used in the connection attempt was IIS APPPOOL\DefaultAppPool (so my connection attempt failed).

C. IIS Windows Authentication enabled and fastcgi.impersonate = 1:

With Windows authentication enabled (and Anonymous Authentication disabled), I connected to IIS with the identity that my Web browser was running under (Microsoft\brian.swan, the identity I logged in with). And, with impersonation on, the PHP process ran under the Microsoft\brian.swan identity. So, since that identity mapped to a valid login on my server, my connection attempt succeeded.

D. IIS Windows Authentication enabled and fastcgi.impersonate = 0:

The results here were the same as with Anonymous authentication enabled and fastcgi.impersonate =0 (the connection attempt failed). The only difference occurred when I requested the page from the Web server: a pop-up window asked for my identity when I requested the page.

E. Both Anonymous and Windows Authentication enabled:

Web browsers will try to access a Web server by using anonymous authentication first. So, if both Anonymous and Windows Authentication are both enabled, the results will be the same as those above where Anonymous Authentication is enabled (A and B).

gofr1
  • 15,741
  • 11
  • 42
  • 52