CakePHP SQL Server Windows Authentication

Question

I have CakePHP connecting to a SQL Server database using standard SQL Server authentication. If I want to connect via Windows Authentication instead has anyone tried this?

Firstly I think it requires using the SQL Server drivers i.e. not the standard MSSQL PHP drivers so that PHP itself can use Windows Authentication. Then its the issue of replacing all the places in CakePHP where it assumes that its using the /app/core/database.php config file.

Has anyone tried either using the SQL Server drivers with CakePHP or better yet tried Windows Authentication? Or simply maybe its just not worth the hassle.

Answer 1

Well blow me down with a feather and I'll be damned and all that... after a bit of research, I tried turning on NT Authentication in PHP just using the mssql driver (the main PHP site has a good explanation), added the IUSR (IIS domain account) as a login to SQL Server and a {mydatabase_iis} user with db_readers and db_writer permissions and the bloody thing worked.

Steps to recreate:

1. In C:\Program Files\PHP\php.ini change this line (i.e. turn NT Authentication on): mssql.secure_connection = On
2. Restart IIS
3. In SQL Server Management Studio Express
4. Go to Security | Logins (right-click) | Add New Login...
5. Click Search next to the Login input box
6. Type IUSR and click Check name (should underline the word - see screenshot)
7. Click OK
8. Leave Windows Authentication selected and Click OK
9. Go to Databases | {MyDatabase} | Security | Users (right-click) | New User...
10. User name {mydatabase_iis}
11. Login - click '...' and find IUSR Login
12. For Database roles tick db_datareader and db_datawriter and hey presto we're in (or at least I was)

It seems that CakePHP is quite happy to use the database.php file for the database name and I assume that SQL Server just ignores the username and password given there and uses Windows Authentication.