SQL Server Driver for PHP: Understanding Windows Authentication
In my last post, I provided an overview of using the SQL Server Driver for PHP. One of the driver features I didn’t mention in that post is the support for Integrated Windows Authentication when connecting to a server. So, that’s what I’ll take a closer look at in this post.
I must confess that when I first tried using Windows authentication with the driver, I was puzzled. I was logged in to my computer as Microsoft\brian.swan, and I know that is a valid login for my database server. But every time I tried to connect, I got this error:
Login failed for user 'NT AUTHORITY\IUSR'
Where did the NT AUTHORITY\IUSER identity come from, and why was it being used to connect to my server? Why wasn’t my identity used in the connection attempt? I had some digging to do.
The syntax for using Windows Authentication with the driver was easy: omit the UID and PWD entries in the $connectionOptions array when you establish a connection:
$serverName = “(local)”;
$connectionOptions = array(“Database” => “TestDB”);
// Because UID and PWD are not specified in the connection options,
// the connection is made with Windows Authentication.
$conn = sqlsrv_connect($serverName, $connectionOptions);
But understanding what identity was being used in the connection attempt was confusing. What helped clear up my confusion was learning this: The identity that is used to connect to the server will always be the identity of the process in which PHP is running. That may be a bit oversimplified (impersonation allows the process to temporarily use a different identity), but understanding this allowed me to move forward. After some more digging and some experimentation, I found I needed to answer two questions to know what identity would be used in the connection attempt:
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?)
Here’s what I found:
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\IUSER by 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). This article provides more information about different versions of IIS and the identity of applications: Who is my IIS Application Process Identity?
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). For more background on this, see How IIS authenticates Web browser clients.
You can play with these settings and identities yourself:
· To turn impersonation on or off, set fastcgi.impersonate = 1 (on) or fastcgi.impersonate = 0 (off) in your php.ini file and restart your Web server.
A note about impersonation: My Web server and database server are on the same physical machine. If your client, Web server, and database server are all on different machines, the client identity may not be passed to the database server even when impersonation is on. This article has more information: IIS, Windows Authentication, and the Double Hop Issue.
Now that you know how to use Windows Authentication with the SQL Server Driver for PHP, you need to be careful. Make sure that the identity that is used to connect to the database server has only the permissions that you want it to. This article provides an overview of SQL Server security: Securing SQL Server.
I hope this helps you avoid some of the confusion I ran into with Windows Authentication and the SQL Server Driver for PHP.