Linux-based PHP establishing a connection to MsSQL Server

Question

What is the best way to connect via PHP to a Linux box to a remote Microsoft SQL Server.

PHP will only work in the Linux box.

I tried for a simple answer for a while.

Answer 1

Php 5.6

Ubuntu

sudo apt-get install php5.6-sybase freetds-common libsybdb5 

AWS / Centos / Redhat

 sudo yum install php56-mssql 

After that, you can connect to the MsSql database through PHP like this:

 <?php $server = 'localhost'; $user = 'someUser'; $pass = 'somePassword'; $database = 'theDatabaseName'; try { $pdo = new \PDO( sprintf( "dblib:host=%s;dbname=%s", $server, $database ), $user, $pass ); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo "There was a problem connecting. " . $e->getMessage(); } $query = "SELECT * FROM TestSchema.Employees"; $statement = $pdo->prepare($query); $statement->execute(); $results = $statement->fetchAll(PDO::FETCH_ASSOC); var_dump($results); 

You can troubleshoot MsSQL with something like this on the command line:

 tsql -H your.server.name -p 1433 -U yourusername -P yourpassword -D yourdatabasename 

To install tsql, you can run this:

Install SQL binary files for testing on any version of PHP

 sudo apt install freetds-bin 

---

Php 7+

Microsoft has its own drivers that we can use. Full instructions here (Redhat / Ubuntu / Others) .

This link also contains the necessary steps to install the Ms SQL server on your developer's computer (works in Ubuntu, does not work in AWS, but you can simply deploy an RDS instance there). It also contains basic instructions on how to create test tables and data in a database, and PHP connection code.

You can also install components for a newer version of PHP as follows:

 sudo apt-get install php7.2-sybase freetds-common libsybdb5 

Answer 2

you need to install the mssql driver for php on linux.
This is the best tutorial for you.

Answer 3

Try: for Ubuntu

 sudo apt-get install php5-sybase php5-odbc freetds-common 

Edit freetds.conf, then connect the MSSQL server to this PHP.

Answer 4

After searching and testing many offers here and in other posts, and having spent a lot of time, a colleague suggested trying ADO.

Since we already had PHP installed with ADO support, it literally took less than 10 minutes to get up and running.

If you are serious about connecting from Linux PHP to MS-SQL, consider ADO.

Answer 5

It is interesting to note that each answer here offers one solution to the problem, rather than the answer to the asked question, which was the "best" solution. Unfortunately, the OP forgot to tell us what the criteria are for the "best."

No one has mentioned odbc so far. Although this entails configuring the odbc driver to connect to the database and enable the php extension, this method provides better isolation of the PHP code from the base database, making it easy to port the code later.

Microsoft, the provided drivers for php , should provide good compatibility, but only support v7 php.

You may prefer to use the features provided by your Linux distribution to ensure that you have a supported product and / or automatic patch management. An alternative to odbc here might be the freetds driver , but you did not tell us anything about which Linux distribution it is.

Answer 6

Get sqlsrv extension from microsoft for php http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx