Connect to SQL SERVER 2008 (using Windows Authentication) via PHP in IIS7?

Question

I need to connect to Sql Server 2008 using windows authentication from PHP code by passing the username and password like we do when we connect to Sql Server with SQL Authentication. I need to authenticate the user based on Windows Authentication's User/Password .

When I was using SQL Authentication I was able to connect by creating a System DSN named DBconnect in the Data Source (ODBC) configuration tool. Then I could successfully verify the login ID with SQL Server Authentication and connect to the database using Login ID and Password entered by a user with this php code:

$con = odbc_connect("DBconnect", "sql_user", "sql_password");

Now I need to do the same thing when SQL Server is using Windows Authentication. I want to create a System DSN named DBConnect_NT in Data Source (ODBC) Configuration tool and set it to verify the authenticity of the login ID with Windows Authentication using the network login ID.

I have this code:

$con = odbc_connect("DBconnect_NT", "windows_user", "windows_pass");

But it doesn't work. I get this error:

Error in query preparation/execution. 28000

I changed some settings in the Authentication section of IIS7 Manager

Windows Authentication = Enabled
Anonymous Authentication = Disabled

Now I can use this code:

$con = odbc_connect("DBconnect_NT", "", ""); 

and I see a popup prompting me to login. As I type username and password, It connects and hence allows me to do other queries using same $con connection object.

Unfortunately, I'm not able to authenticate the user for the web site based on this approach. When I type wrong username and password to the popup it gives no response (the popup comes again). If I cancel the popup, it will show this 401 error:

401 - Unauthorized: Access is denied due to invalid credentials.

Am I using correct way to connect to SQL Server via Windows authentication? Is there some other possible way to connect SQL Server by passing the Windows Authentication username and password dynamically?

I have also tried using LDAP to authenticate against Active directory, but its not working.

I'm using SQL SERVER 2008,IIS7 and PHP5.3 in an Amazon EC2 machine.

This is for a flex application I'm building using http://example.net/Webservice/connect.php. Flex dynamically passes the username/password to this popup from a form and hence connects. But when wrong username/password are supplied the popup is difficult to handle. It comes back continuously and the flex application hangs .

If it is possible to receive an error code when the wrong username/password are entered in the popup it may solve the issue. Some other method to connect SQL Server using Windows Authentication and pass the username/password directly would also solve my problem.

Solution

When you use Windows Authentication, Sql Server does not see your username or password at all. Stop and read that sentence again, because it's very important to understand this.

For Windows Authentication to work, you must first authenticate directly with an Active Directory Domain Controller (DC). When you successfully log in, the DC will issue a security token for your session. It's this token you provide for Sql Server Windows Authentication to work, and not your username or password. Sql Server only verifies the token.

This is why the LDAP methods do not work. LDAP only provides a "yes/no" response from Active Directory. LDAP does not provide the security token you need. Think of it as the Windows equivalent to using Certificate Authentication to a linux server. Moreover, this token is associated with the process making the connection. It's not a property you can just set on a connection object. This is a security feature to make it difficult to steal or hijack tokens.

That creates problems in a web site, where you need a way to store and manage the token for each user of the site, and attach it to your web server process. IIS provides a mechanism for this called Impersonation. It was built originally for ASP.Net, but it seems FastCGI does support it via the fastcgi.impersonate setting in the php.ini file. But note I've never personally tried to use this, so I can't speak to how well it works.

You also need a way to connect to the DB on public pages pre-authentication, and a way to connect the authentication to the Active Directory account, which will limit authentication mechanisms available to you. Not being a regular PHP user myself, and not knowing how your AD environment relates to your EC2 web server instance, this is as far as I can take you.