Connecting to MSSQL server in PHP using integrated authentication

Question

I have XAMPP running on an MS Server 2012 R2. I need to make a connection to an MSSQL server which accepts only integrated win authentication.
If I simply try the below, I get a login failure and an error log on the SQL Server that SQL authentication is not an option. It only accepts connection from a certain user. Obviously the PHP script is not being run under that account.

$server = "sqlServerName";
$SQLUser = "username";
$SQLPass = "pw";
$SQLDatabase = "db";
$link = mssql_connect($server,$SQLUser,$SQLPass);

As I'm using PHP 5.3.1 sqlsrv_connect is not an option. I tried to load the php drivers for it but it's just not working. I can't change the authentication for the sql server and I can't use any other version of PHP.

I also can't turn the secure_connection on as I have to be able to connect to other sql servers which requires "normal" sql authentication:

mssql.secure_connection = Off

How to connect to my problematic sql server?

UPDATE: Upgraded xampp to the latest version. PHP is now version 5.6.8 I still can't use sqlsrv_connect() even though I installed the necessary driver and added every single dll to the php.ini. Restarted apache several times. Any clue?

error msg: Fatal error: Call to undefined function sqlsrv_connect()

Answer 1

Ok. It's hard to debug a server issue without being on the server but I've done a lot with php and SQL Server so I will do my best to share my experiences.

First, Very glad you updated from 5.3.1 that version of php is ancient and very insecure. Here are some sanity checks for your system. This may do nothing for you but all of it is worth checking.

1. First make sure you can connect to sql server using SQL Server Management studio with the credentials you provided. This means the same credentials you use in php not the windows authentication credentials. You should be able to have both connections at the same time so you can make changes and test the connection at the same time.

   1. Enable tcp. sql server configuration manager -> SQL Server network configuration -> protocols for sqlexpress -> tcp/ip (right click)-> properties -> Enabled (yes) -> ip Addresses -> IPAll -> TCP Port 1433 -> ok
   2. Enable sql server auth. Select server (right click) -> properties -> security -> sql server and windows authentication mode -> ok
   3. Open sql server port on firewall. Windows Control panel -> system and security -> windows firewall -> advanced settings -> Inbound rules -> New rule -> Port -> tcp -> 1433 (or whatever) -> Allow connection -> next -> Name -> sql server -> finish -> restart computer.
   4. Of course if you want to connect through a non-default user you need to add the user: sql server -> security -> logins (right click) -> add login -> server roles -> sysadmin -> ok
   5. If you make any of these changes restart sql server: Sql server configuration manager -> sql server services -> sql Server (right click) -> restart.

2. Once you confirm you can connect with management studio here are the php configuration checks:

   1. You can see if the extension itself is available by creating a php page with only the function phpinfo() in it. Then search for pdo_sqlsrv. If it is present the rest of these checks are probably not necessary but since you've been working this so long probably check them anyway.
   2. sql_srv extension for php should be version 3.2 for php 5.6 you can obtain that library here
   3. Version 3.2 requires an os extension available here Check the other requirements on the previous link. Your os may use a different extension from the one linked here.
   4. Find your php extensions directory. this is usually {php-install-directory}/ext. Make sure you copy the appropriate version of the downloaded sqlsrv libraries into this directory. Mine are called "php_sqlsrv_55_ts.dll" and "php_dpo_sqlsrv_55_ts.dll" Yours will have 56 instead of 55 I think and the "ts" should match your php install. "ts" means thread safe, the other option is "nts" not thread safe. The one you use is dependent on your php install.
   5. My php.ini file contains these lines extension=php_sqlsrv_55_ts.dll and extension=php_pdo_sqlsrv_55_ts.dll in that order. but I don't think order matters. and again yours will be 56 and the "ts" may be "nts".
   6. If you made any changes based on these make sure to restart apache then check if pdo_sqlsrv is in your phpinfo() report. Also after restarting apache check the apache and php error log to see if you get specific errors about php trying to load the extensions. Post those here if you need help with them.

3. Once you are connected to sql server through the auth creditionals in management studio and see pdo_sqlsrv in your phpinfo() here are the last things to look into in your code.

   1. Your code above is still for mssql extension. You probably just didn't update it with your latest changes. For sql server extension your code should look like this:

      $connectionInfo = array( 'UID' => $dbuser, 'PWD' => $dbpass, 'LoginTimeout' => 1, ); $host = $host . ', ' . $port; $connection = sqlsrv_connect($host, $connectionInfo); $error_messages = sqlsrv_errors();

   2. For windows authentication exclude the uid and pwd.

      $connectionInfo = array(); $conn = sqlsrv_connect( $host, $connectionInfo);

If you have more issues please tell me which step is not working so we can dig into more detail with that step.

Answer 2

Go back to your PHP 5.3.1 stack where mssqlconnect() was working. SQL Server integrated mode requires an authenticated user which has access to the machine running SQL Server. So if you can connect in SSMS with your user, you need to start your XAMPP,Apache/httpd with the credentials of your user. If apache is running as a service, go the services panel (services.msc on run), then go to Apache->properties->logon->'This Account' and put in your username and password so that apache service runs with your credentials, which is authenticated to connect to SQL Server instance. By default httpd is run with system account which is obviously not authenticated by SQL Server at remote machine, causing you the headache.