sqlsrv_connect: Windows authentication

Question

I have this PHP code running under IIS:

$serverName = "someServer\someServer";
$connectionInfo = array( "Database"=>"SOME_DB");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

When I try to visit the page, I get the error:

[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Which is confusing, as the documentation here says "The connection will be attempted using Windows Authentication".

The flow of this is:

1. User using x Windows account on workstation1 visits this website hosted on server1
2. server1 executes above PHP code and tries to retrieve data from server2 using y Windows account (y is defined in the IIS settings, under Application Pools->Identity)
3. Windows account y has access to the database

The IIS pool is running under a Windows domain user "domainadm" which has access to the SOME_DB database and the server it sits on.

Why is it trying to authenticate anonymously and how can I fix this so it runs under the user the IIS pool is running as?

In php.ini:

fastcgi.impersonate = 1;

Checking the Authentication section in IIS, the only options I have are "Anonymous Authentication" or "ASP.NET Impersonation". No Windows authentication?

Anonymous is currently enabled, which gives the error above. If I switch to the ASP.NET one I instead get this error when visiting the page:

500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed. An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

My updated web.config:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <httpErrors errorMode="Detailed" />
        <validation validateIntegratedModeConfiguration="false"/>
    </system.webServer>
    <system.web>
        <identity impersonate="true" />
        <authentication mode="Windows" />
        <customErrors mode="Off" />
        <compilation debug="true" />
    </system.web>
</configuration>

Answer 1

The issue is IIS is sending the connections anonymously to SQL despite the AppPool identity you are running as. To resolve this, you have to enable anonymous connections from the web server (server1) to the SQL server (server2).

On your SQL server, create a login for <YourDomain>\server1$ and assign appropriate permissions to Some_DB under the User Mappings tab. For example, give this account db_datareader and db_datawriter rights and you should be able to connect and successfully complete CRUD operations. For the sake of testing, you may just want to grant this login db_owner rights on Some_DB temporarily to ensure SQL permissions are not at issue.

With respect to the PHP fastcgi.impersonate setting - I believe you can set this to 0/false as it isn't going to matter in this case.

Answer 2

In IIS, in the authentication section, enable Windows authentication and ASP.NET Impersonation.

Those will be the only authentication types that will be enabled in IIS.

In the web.config file, you will want the following (this should solve the problem if Windows not appearing):

<system.web>
    <identity impersonate="true" />
    <authentication mode="Windows" />

The reason for the identity.impersonate is that you will need it to impersonate the logged in user credentials through windows.

Once the user is authenticated, you can use anonymous authentication to access the database.

For example to prove the above works, in your database go to Security - Logins on SQL Server and Add Login.

Add: NT AUTHORITY\ANONYMOUS LOGON

and assign it dboOwner role to ensure it is working.

Doing the above will prove that there are no other conflicts with the way that IIS is set up - e.g. incorrect web.config entries.

Make sure the app pool is running in integrated mode and that you have Integrated Security=true in your database connection string.

You can then assign a different account if necessary e.g. your "domainadm" account a service account to run the app pool under. But there may be issues if the user is not on the same domain and other issues that could arise because of the way that the server/accounts are configured that could potentially cause this to fail. But trying the above steps will get it running so that you could diagnose those issues if necessary.

Answer 3

The suggestions for Windows authentication or impersonation here are not applicable to your case. Windows auth would be for the client computer "x" to auth with your front end "server1", and impersonation would be used to forward that credential "x" to SQL "server2". That would require every user using the site be given SQL access. You instead want anonymous access to your site, and for the site to manage its own access to SQL.

Problem Summary: The default user for Anonymous Auth is IUSR, regardless of what your app pool setting is. When it tries to connect to a network resource, it authenticates as "NT AUTHORITY\ANONYMOUS LOGIN". Obviously, that's not very useful when trying to lock down access.

You can see this mentioned here: http://www.iis.net/learn/get-started/planning-for-security/understanding-built-in-user-and-group-accounts-in-iis

Note: The IUSR account is similar to LOCALSERVICE in the manner in which it acts anonymously on the network. The NETWORKSERVICE and LOCALSYSTEM accounts can act as the machine identity, but the IUSR account cannot because it would require an elevation of user rights. If you need the anonymous account to have rights on the network, you must create a new user account and set the user name and password manually, as you did in the past for anonymous authentication.

Further information can be found here: http://www.iis.net/learn/get-started/whats-new-in-iis-7/changes-in-security-between-iis-60-and-iis-7-and-above

Solution: Go to the Authentication section, select Anonymous Authentication, click Edit..., and change from "Specific User: IUSR" to "Application pool identity". That should force the site to use the account you've set for the app pool.

Once done, if you are using the default app pool setting of "Application Pool Identity", your application will attempt to auth with SQL as DOMAIN\MACHINENAME.