10

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()

Improve this question
9
  • Mssql is designed for linux distributions. Not sure how you got it installed on a windows machine (post a link to the build I'd love to see it) but I wouldn't expect it to ever work. Upgrade to php 5.4 if you need to it's much more secure. and 5.3.1 is very outdated and insecure anyway. Commented Jun 10, 2015 at 14:47
  • updated the question, please check it Commented Jun 18, 2015 at 13:57
  • Not sure if I understood you correctly. You are saying, that your SQL Server is configured to accept only integrated Windows authentication. So, either run your php script under Windows user that has rights to connect to SQL Server, or enable SQL Server authentication in addition to integrated Windows authentication, as @danielson317 described in the first point of his answer. Commented Jun 19, 2015 at 11:29
  • @VladimirBaranov Enabling sql server authentication is not an option. So how can you run a php script on behalf of a user? Furthermore how can you use mssql_connect() that way, I mean without providing user/pass. Cause like I said I can't make sqlsrv_connect to work. Commented Jun 19, 2015 at 12:08
  • @fishmong3r, In these docs the first comment says to set mssql.secure_connection = On in php.ini file to enable Windows authentication. But you are saying that you can't do it. I don't know what to do in this case. Commented Jun 19, 2015 at 12:33

2 Answers 2

Reset to default
8
+25

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

First of all thank you for your efforts. So I skipped the whole 1st section as I use the database on a daily basis from other applications. 2.1 I have no pdo_sqlsrv loaded in php info, (2.2) though I already had the drivers. 2.3 I have never heard of this, and honestly I don't know which one to download. Can you please help with that? Also in the system requirements I can't see Win 2012 R2, but that is the OS my web server runs on.
The requirements listed under the link in direction 2.2 send you Here: msdn.microsoft.com/en-us/library/cc296170(v=sql.105).aspx where it states 2012 R2 is supported by sqlsrv version 3.2. So just download the software from link in direction 2.3 and install it. Then restart your computer and finish the steps.
@fishmong3r And as for where the information about the link in direction 2.3 see this screenshot: evernote.com/shard/s600/sh/3a7e52ec-9e87-4092-9e79-32f3555fe21c/…
Does your php error log have any information in it. or your php error log? If the extensions are included in your php.ini and not your phpinfo you will have apache or php errors in your log explaining why their not included. Please find and post those errors. If you don't have any errors the extension lines themselves are wrong.
php error: [21-Jun-2015 10:45:08 UTC] PHP Warning: PHP Startup: Unable to load dynamic library 'e:\xampp\php\ext\php_sqlsrv.dll' - The specified module could not be found. I've already set the extension folder properly in the php.ini. All the other extensions are being loaded without any issues. Also somewhere I read that the path of the php folder has to be added to the environment variables, so I did that too. apache error log doesn't contain any related information.
|
0

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.

Improve this answer

10 Comments

OK, and after the above how can I use mssql_connect()? It requires user/pass, right? How can you set integrated auth/SSPI?
@fishmong3r No, it doesn't. There is an overload of mssql_connect($servername) which only requires the server name. Are you running Apache as a service?
Yes, it's running as a service.
then please go the service console, stop the service, provide your Windows credentials with which you are able to access the SQL Server and restart the service. Let me know the output.
php 5.3.1 has dozens of security issues. If your going to use 5.3 at least use the latest security patch. 5.3.29 I think. But it looks like 5.3 is at end of life. It's just plain not a good Idea to use it.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.