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.
· Configure Windows Authentication (IIS 7)
· Specify an Identity for an Application Pool (IIS 7)
· Change an Application Pool for an Application (IIS 7)
· Create an Application Pool (IIS 7)
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.
Thanks.
-Brian
Comments
- Anonymous
June 07, 2010
Thanks Brian!I am struggling with this problem on my dev machine for weeks. - Anonymous
June 08, 2010
Glad this helped...let me know if you run into more questions.-Brian - Anonymous
January 31, 2011
THANK YOU VERY MUCH BRIAN !I was very upset due to this error !You made my day :) - Anonymous
February 01, 2011
Once again...glad this post helped. :-) - Anonymous
March 14, 2011
Hi Brian, I wonder if you can help or point me in the right direction? I was using mssql_connect, but have upgraded php to 5.3.5 and so am now using sqlsrv_connect. I have seperate IIS and SQL servers.I am using sql server authentication so have assumed that the double hop issue should not be an issue and our UID and PWD should be passed all the way through however the following is not connecting to my SQL server.$server = "SERVERINSTANCE";$connectionInfo = array("Database"=>"dbname", "UID" => "user", "PWD" => "password");$con = sqlsrv_connect($server,$connectionInfo);Do you haVE any idea what I need to do to make this work? Any pointers, hints or tips you can share however big or small would be very, very much appreciated. Many thanks - Anonymous
March 15, 2011
Please ignore my previous questions and even remove it if you can. I realise a forum is a better place to post this comment so I have posted this in the SQL server driver for PHP forum. - Anonymous
April 10, 2011
Brian,just thinking perhaps is not enough time to update this post ? what's your recommendation in today approach to double step situation ?Thanks - Anonymous
April 11, 2011
Luis-The easiest way to deal with double-hop authentication is to use SQL Authentication (user/password). Is that not an option for you?-Brian - Anonymous
July 20, 2011
Good work explaining things and linking to other resources. - Anonymous
April 05, 2012
This article references IIS. Can Windows Authentication to remote SQL Server be accomplished with Apache 2.2, PHP 5.3.8? - Anonymous
April 06, 2012
@techuser: I haven't tried using Windows Authentication with Apache in the mix. This article, however, looks like it would be a good start in figuring out what needs to be done: moinmo.in/.../WindowsDomainAuthentication-Brian - Anonymous
May 02, 2013
I have experimented with this and I have successfully been able to configure PHP with IIS 7.5/FastCGI and pass user credentials to a remote SQL datasource using Kerberos constrained delegation. - Anonymous
August 20, 2013
The comment has been removed - Anonymous
August 20, 2013
Glad to know this is still helpful. I'll take you up on the beer. :-) - Anonymous
November 25, 2013
Hi Brian,Very useful post but we have am scenario I don't know how to apply these settings.We want to run a php application and we need to access to a local sql server. We want to use the integrated security to allow the access to the application avoiding to use login page and we want akso to make use of the username pass in some functions to retrieve/insert data but we want to use a a single custom user to access the database. It's possible? How can we configure the sql connections and the application pool?Thans a lot! - Anonymous
November 11, 2014
Hi Brianthank you for making this issue so understandable in your excellent article.Can you please devote all your spare time to rewriting every other confusing "explanation" on the web. :)regardsBasil Bear - Anonymous
June 29, 2016
Great! It was very helpful! Thank you! - Anonymous
November 23, 2016
Im using A, and am actually having my identity being used to connect, succesfully, to SQL. I'm using chrome, from a seperate machine. App pool identity is no-privleges "restserver"Very weird.- Anonymous
November 23, 2016
you can delete this mate. I worked out I had a user set for "Physical Path Credentials"Very strange things happen.
- Anonymous