Create PHP apps using SQL Server on Ubuntu
Note: To ensure optimal performance of SQL Server, your machine should have at least 4 GB of memory. If you need to get Ubuntu, check out the Ubuntu Downloads website.
- Register the Microsoft Linux repositories and add their keys.
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | sudo tee /etc/apt/sources.list.d/mssql-server-2017.list sudo apt-get update sudo apt-get install mssql-server Reading package lists. Done Building dependency tree Reading state information. Done The following NEW packages will be installed: mssql-server . Unpacking mssql-server . Setting up mssql-server . sudo /opt/mssql/bin/mssql-conf setup Microsoft(R) SQL Server(R) Setup To abort setup at anytime, press Ctrl-C. The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746388 and found in /usr/share/doc/mssql-server/LICENSE.TXT. Do you accept the license terms? If so, please type YES: Please enter a password for the system administrator (SA) account: Please confirm the password for the system administrator (SA) account: You now have SQL Server running locally on your Ubuntu machine! Check out the next section to continue installing prerequisites.
Step 1.2 Install PHP and other required packages
To install PHP 7.2 or 7.3, replace 7.4 with 7.2 or 7.3 in the following commands.
sudo su add-apt-repository ppa:ondrej/php -y apt-get update apt-get install php7.4 php7.4-dev php7.4-xml -y --allow-unauthenticated You have successfully installed PHP on your Ubuntu machine!
Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server
SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.
sudo su curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - #Download appropriate package for the OS version #Choose only ONE of the following, corresponding to your OS version #Ubuntu 16.04 curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list #Ubuntu 18.04 curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list #Ubuntu 19.10 curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list exit sudo apt-get update sudo ACCEPT_EULA=Y apt-get install msodbcsql17 # optional: for bcp and sqlcmd sudo ACCEPT_EULA=Y apt-get install mssql-tools echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc # optional: for unixODBC development headers sudo apt-get install unixodbc-dev After installing SQLCMD, you can connect to SQL Server using the following command:
sqlcmd -S localhost -U sa -P yourpassword 1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements. This how to run a basic inline query. The results will be printed to the STDOUT.
sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION" -------------------------------------------------------- Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64) Apr 13 2017 11:44:40 Copyright (c) Microsoft Corporation on Linux (Ubuntu 16.04) 1 rows(s) returned Executed in 1 ns You have successfully installed SQL Server Command Line Utilities on your Ubuntu machine!
Have Questions?
Happy to help! You can find us on GitHub, MSDN Forums, and StackOverflow. We also monitor the #SQLServerDev hashtag on Twitter.
Linux based PHP install connecting to MsSQL Server
What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server. The PHP will only ever run on a Linux box. I’ve been trawling for the simplest answer for a while now.
6 Answers 6
Php 5.6
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 with something like this:
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:
Installation of SQL binaries for testing on any PHP version
sudo apt install freetds-bin Php 7+
Microsoft has native drivers we can use. Full instructions are here (Redhat / Ubuntu / Others).
That link also contains required steps to install Ms SQL server on your dev machine (working on Ubuntu, doesn’t work on AWS, but you can just spin up an RDS instance there). It also contains basic instructions on how to create test tables and data in the database, and PHP connectivity code.
You can also install the components for a newer version of PHP like this:
sudo apt-get install php7.2-sybase freetds-common libsybdb5 The above did not work for me in docker with PHP 5.6 but this did the trick -> github.com/docksal/docksal/issues/473#issuecomment-374855050
you must to install mssql driver for php on linux.
this is a best tutorial for you.
sudo apt-get install php5-sybase php5-odbc freetds-common Edit freetds.conf then connect MSSQL server with this PHP.
After searching and trying out the many suggestions here and in other posts, and spending a lot of time, a colleague suggested trying ADO.
As we already had an ADO enabled PHP install, it literally took less than 10 minutes to get up and running.
If your serious about connecting from Linux PHP to MS-SQL, consider ADO.
My apologies if I don’t understand the protocal here, but I’m pretty sure I gave pretty good answer. The question was: ‘What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server.’ My answer is: The best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server is to use the PHP ADO library. There are many examples. Here’s the first one I found: stackoverflow.com/questions/10881599/php-adodb-mssql-connection
It’s interesting to note that every answer here proposes one solution to the problem rather than answering the question asked which was which is the «best» solution. Unfortunately the OP forgot to tell us what the criteria were for «best».
Nobody so far has mentioned odbc. While this entails both configuring the odbc driver for connecting to the database and enabling the php extension, this method provides the best isolation of the php code from the underlying database making it easier to port the code later.
The microsoft provided drivers for php should give good compatibility but only support v7 up of php.
You may prefer to stick with the functionality provided by your Linux distro to ensure you have a supportable product and/or automated patch management. An alternative to odbc here may be the freetds driver but you didn’t tell us anything about which Linux distro this is.