php adodb MSSQL connection

Question

I have a linux server that I'm trying to use php adodb to connect to a MSSQL server.

include('adodb5/adodb.inc.php'); 

$conn =& ADONewConnection('odbc_mssql');
$dsn = "Driver={SQL Server};Server=MSSERVER;Database=Northwind;";
$conn->Connect($dsn,'sa','password')or die("Unable to connect to server");

I've install mssql through yum etc and I know the server can connect to it as I've tried the following:

$db = @mssql_connect("MSSERVER","sa","password") or die("Unable to connect to server");
mssql_select_db("Northwind");

// Do a simple query, select the version of 
// MSSQL and print it.
$version = mssql_query('SELECT @@VERSION');
$row = mssql_fetch_array($version);

echo $row[0];

// Clean up
mssql_free_result($version);

Any ideas why my adodb wont connect, or any examples on how I can connect would be much appreciated.

Answer 1

I've solved this by looking at this forum: http://ourdatasolution.com/support/discussions.html?topic=4200.0

The correct code is:

<?php
include("adodb5/adodb.inc.php"); 
//create an instance of the  ADO connection object
$conn =&ADONewConnection ('mssql');
//define connection string, specify database driver
$conn->Connect('xxx.xxx.x.xxx:1400', 'user', 'password', 'DbName');
//declare the SQL statement that will query the database
$query = "select * from table";
$rs = $conn->execute($query);
//execute the SQL statement and return records
$arr = $rs->GetArray();
print_r($arr);
?> 

Hope that helps somebody else.

Answer 2

For PHP 7.4 you can download the drivers here:

https://learn.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver15

Copy the files to the ext directory of your php installation. In the php.ini file then add the extensions as follows:

extension=php_pdo_sqlsrv_74_ts_x64
extension=php_sqlsrv_74_ts_x64

The extension also requires the ODBC driver for SQL Server to be installed: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

Answer 3

With php 5.3 of above the php_mssql modul is no longer supported for windows.

A Solution is to download the MicroSoft PHP Driver from http://www.microsoft.com/en-us/download/details.aspx?id=20098.

This installer will extract the modul dll files to you php extension directory.

Include the correct version in you php ini (e.g. like this for php 5.3 ThreadSafe):

extension=php_sqlsrv_53_ts.dll  

After this you can use adboDb again, but you have to use mssqlnative as adodbtype. And the connection with ip and port didnt work for me, but ipaddress\\SERVERNAME worked (see examplecode)

<?php include("adodb5/adodb.inc.php");  
//create an instance of the  ADO connection object
$conn =&ADONewConnection ('mssqlnative'); 
//define connection string, specify database driver 

// $conn->Connect('xxx.xxx.x.xxx:1400', 'user', 'password', 'DbName'); 
$conn->Connect('xxx.xxx.x.xxx\\SERVERNAME', 'user', 'password', 'DbName'); 

//declare the SQL statement that will query the database 
$query = "select * from table"; 
$rs = $conn->execute($query); 
//execute the SQL statement and return records 
$arr = $rs->GetArray(); 
print_r($arr); ?>