Microsoft PHP SQL Server Connection Issues - How To Troubleshoot What Is Being Blocked.

Question

I am connecting through Windows Authentication. I have two webservers on local system. IIS 6.0 on port 80, Apache 80:80; I have created a DSN and connected. I have written simple ASP/VBS Script and connected with this DSN.

I've tried to use this DSN using odbc_connect in PHP and it failed. I then tried the connection as outlined here:

http://msdn.microsoft.com/en-us/library/cc296161%28SQL.90%29.aspx

I then created a local dsn using ODBC and verified the code actually works from localhost running on Apache.

My IT guys won't/can't create a user id/password on SQL Server. The issue is if I bring this up, they play a game of 'what's wrong.' I can deduce what is wrong ( lack of trusted authentication ); however, I can't rule other issues since I can't look at this server side.

I'm thinking I'm connecting with a DSN, but it's not using the same credentials as my windows account from my web browser. I attempted to run Fiddler to run this down and I'm not seeing anything jump out there.

I'm sure this is an 'authentication' issue or something be blocked. I just trying to see if there is something else I've missed or what could be blocked before I go play Twilight zone with the IT dept ( or the nasty old gnomes at the bridge in Dora's world...lol).

Answer 1

I have bad news for you: Windows Authentication stinks big time. The exact problem is called 'double hop authentication' - google for it and you'll find lots of people asking for help. Half of them would have solved the problem using something that already failed to work for you - while the other half apparently never solved the issue.

I wrote recently an extranet sensitive data gathering application for a customer that insisted everything being based on Active Directory and Windows Authentication. To cut a long story short, the whole project stopped for a whole month until they gave up and allowed a single SQL Server login for the network connection. Nothing else worked.

Lessons learned:

• don't believe M$ sales brochures where they say it 'just works' and 'perfectly integrated'
• a ten minute job of connecting a server to a DB can become a month long showstopper if you disregard the point above
• next customer that comes up with AD and the like can expect to pay dearly for the privilege of forcing me to work with Microsoft's stuff again...