Msphpsql: sqlsrv connection issues via browser - error: "There is some problem in connection: SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server"

[leepolanco]

Hello, i need help on identifying what i am missing on this setup.
I need to connect MSSQL server via php/apache/linux setup.
Connections works fine CLI (php mytest_connect_script.php) , however not from browser.

-----details----
+## PHP Driver version or file name
Array
(
[0] => odbc
[1] => sqlite
[2] => mysql
[3] => sqlsrv
)
+## SQL Server version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
+## Client operating system
Red Hat Enterprise Linux Server release 7.3 (Maipo)
+## PHP version
7.2
+## Microsoft ODBC Driver version
unixODBC-2.3.1-11 and have /etc/odbcinst.ini with freeTDS as well.
+## Table schema
+
+## Problem description
testing connection:
mytest_connect_script.php - works fine from command line (CLI) but it does not on browser (apache)

+## Expected behavior and actual behavior
connected via web browser
+## Repro code or steps to reproduce
//-----------------------
// PDO connection
//-----------------------
class pdo_connection {
protected $con;
public function openConnection(){
try {
$this->con = new PDO("sqlsrv:Server=".DB_HOST.";Database=".DB_NAME, DB_USER, DB_PASS);
return $this->con;
}
catch (PDOException $e) {
echo "There is some problem in connection: " . $e->getMessage();
}
} //end of func

public function closeConnection() {
$this->con = null;
}//end func

} //end of clas

comments:
Any hints on this specific issue will be appriciated.
I have bieng gloogling nothing lead me to good solution.

[leepolanco]

using this : Driver=ODBC Driver 13 for SQL Server

[yitam]

hi @leepolanco

Did you follow every step in our instruction documentation?

What's the output when you ran odbcinst -q -d -n 'ODBC Driver 13 for SQL Server'

FYI, this is the FAQ which might help.

[leepolanco]

Thank you Yitam for getting back to me.
Somewhat i did follow steps, php/apache were compiled locally due to network restriction on our site.
sqlsrv items, the setup was followed as described (phpsize, etc.. as described)

but still looking for hints.
what's funny.. testscript.php (which connect SQL DB) works fine from command line.
just not working on browser.

Output of: odbcinst -q -d -n 'ODBC Driver 13 for SQL Server'
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1
Server=##.##.##.## {this is target DB ip address}
Port=1433
charset=UTF-8

[yitam]

Hi @leepolanco, something is wrong, but please try the followings:
(1) cat /etc/odbcinst.ini
(2) ls -l opt/microsoft/msodbcsql/lib64/ (you should see libmsodbcsql-13.1.so.9.2)
(3) dltest /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2 SQLGetInstalledDrivers

[yitam]

Hi @leepolanco, depending on some environment variables, where unixODBC finds odbcinst.ini can be different.

Please try doing an strace like this

strace [apache process] | grep odbcinst.ini

FYI about strace
strace: must have PROG [ARGS] or -p PID
Try 'strace -h' for more information.

[leepolanco]

thank you again.
but still searching.
keep you posted if anything

[yitam]

Hi @leepolanco, you might want to try this in a clean environment. Just a suggestion, of course.

So what's the output of strace [apache process] | grep odbcinst.ini?

[yitam]

@leepolanco just thought it's worth trying to run phpinfo() in your browser.

Do you have more than one php version installed? Is there more than one php.ini in your env, for example? This link might be useful.

[leepolanco]

thank you!

fyi:
Only one php.ini and phpinfo() runs fine on browser..

This is test SQLconnection code runs well command line but on not browser!
test code:
require_once("../../libs/php/common/sqlsrv_properties.php");
$conn = new PDO("sqlsrv:Server=".DB_HOST.",".DB_PORT.";Database=".DB_NAME, DB_USER, DB_PASS);
if( $conn )
{
echo "Connection established.\n";
}
else
{
echo "Connection could not be established.\n";
}
?>

command line:
$date; php mytest2.php ;date
Tue Jun 26 23:08:31 EDT 2018
Connection established.
Tue Jun 26 23:08:31 EDT 2018

[leepolanco]

require_once("../../libs/php/common/sqlsrv_properties.php");

$conn = new PDO("sqlsrv:Server=".DB_HOST.",".DB_PORT.";Database=".DB_NAME, DB_USER, DB_PASS);

if( $conn )
{
echo "Connection established.\n";
}
else
{
echo "Connection could not be established.\n";
}

?>

[leepolanco]

"

require_once("../../libs/php/common/sqlsrv_properties.php");

$conn = new PDO("sqlsrv:Server=".DB_HOST.",".DB_PORT.";Database=".DB_NAME, DB_USER, DB_PASS);

if( $conn )
{
echo "Connection established.\n";
}
else
{
echo "Connection could not be established.\n";
}

?>
"

[leepolanco]

strace -p 29753 | grep odbcinst.ini
Process 29753 attached
select(0, NULL, NULL, NULL, {0, 18617}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}) = 0 (Timeout)
wait4(-1, 0x7ffd7a3ea764, WNOHANG|WSTOPPED, NULL) = 0
select(0, NULL, NULL, NULL, {1, 0}^CProcess 29753 detached

[yitam]

Hi @leepolanco, I have not been able to reproduce this. I don't have freeTDS in my env however. Can you try this in another setting without freeTDS? Meanwhile I will do some investigation.

[yitam]

Hi @leepolanco, even with freeTDS installed I have no problem connecting using browser (Apache). Yet, I didn't really configure freeTDS or specify any DSN.

That being said, you don't need freeTDS to connect to MS SQL Server. The MS ODBC driver is what the php drivers need to connect to SQL Server.

This is another useful reference you might find useful.

[yitam]

hi @leepolanco , any update? Another user (see issue #805) has experienced a similar issue and managed to find a solution. Please take a look and see if it helps.

[leepolanco]

Thank you for following up.
fyi - we are trying to upgrade from RHEL 7.3 to 7.4.
i think, this might be related to openssl (1.0.1) version we have installed on RHEL 7.3.
on RHEL 7.4, we will have openssl 1.0.2
keep you posted.

[leepolanco]

Updates:

• After upgrade to RHEL7.4 and openssl1.0.2 libs installed

• the msodbcsql17-17.2.0.1-1.x86_64.rpm, mssql-tools-17.2.0.1-1.x86_64.rpm drivers installed.

• compiled pdo_sqlsvr.so , sqlsvr.so load.

works fine

thank you!!!