<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

PHP and Microsoft SQL Server

Published on
10,037 Points
6,537 Views
Last Modified:
Dave Baldwin
Zippity doo dah, zippity ay, my oh my what a wonderful day!!
Questions about connecting to Microsoft SQL Server using PHP come up fairly often.  This article covers a little of the basics and history.
History
It has almost always been easy to use PHP with MySQL.  They are both Open Source and have 'grown up' together.  In fact the MySQL people have always supplied the drivers for PHP.  And all-in-one programs like WAMP, XAMPP, and MAMP come with MySQL pre-configured to work.  http://php.net/manual/en/set.mysqlinfo.php

I don't know of any install that includes  Microsoft SQL Server that is pre-configured.   It has always been more difficult to use PHP with Microsoft SQL Server.  MS SQL was derived from SyBase many years ago and has always been a proprietary product. 

Drivers
Up until PHP 5.2, there was a 'mssql' driver http://php.net/manual/en/book.mssql.php which was written by the community with versions for both Linux and Windows.   'mssql' has been removed from PHP as of version 7.0.  With PHP 5.3, Microsoft started supplying the 'sqlsrv' driver http://php.net/manual/en/book.sqlsrv.php and wouldn't allow the 'mssql' driver to be used on Windows anymore.  The Linux 'mssql' driver requires FreeTDS and maybe UnixODBC.  The 'mssql' host is an entry in the FreeTDS table, not a direct connection to the database.  FreeTDS has not been updated for any SQL Server past SQL Server 2000 and does not handle some of the newer datatypes in Microsoft SQL Server.

While the MySQL drivers supplied with PHP were complete, the drivers for MS SQL have always required other programs and/or OS drivers to work.  The 'mssql' and 'sqlsrv' drivers have never been complete in themselves.  In Linux a program called FreeTDS has been required.  UnixODBC may also be required.  In Windows, one of the ODBC drivers has been required.  With SQL Server 2005, the SQL Native Client was required to access all of the features.  With the latest SQL Servers, the Microsoft ODBC Driver 11 for SQL Server is required.  System requirements for 'sqlsrv':  https://msdn.microsoft.com/en-us/library/cc296170%28sql.105%29.aspx

'mssql' driver does Not work with PHP 5.3 and later on Windows .  See here for 'sqlsrv' :  https://www.microsoft.com/en-us/download/details.aspx?id=20098   You will need version 3.2 to work with PHP 5.6.  And you will have to put the drivers in the 'ext' directory and add them to your 'php.ini'.  Please read the help file.  You will also need either the SQL Native Client or the Microsoft ODBC Driver 11, there are links under System Requirements on that page.

You will also probably need to rewrite some of the PHP code to use the 'sqlsrv' driver.  It is Not plug-in compatible with any other drivers.  The Help file SQLSRV_Help.chm has a lot of good code examples.

The 'sqlsrv' PHP driver for MS SQL is Not part of PHP and is Not updated when PHP is.  If someone updates your PHP version for you, it is likely to completely break your application.  Second, SQL statements for MS SQL are based on T-SQL which is Not exactly the same as the SQL used in MySQL.  https://msdn.microsoft.com/en-us/library/bb510741.aspx  In particular, the LIMIT statement used frequently in MySQL is not available in T-SQL.

Server Access
With MySQL, you have to create a user with external 'anyhost' privileges to connect to it from another computer and you may have to tell the server to allow external access.  With Microsoft SQL Server and the SQL Native Client or the Microsoft ODBC Driver 11, you have to enable TCP/IP access and on the server you have to enable external or remote access.  The default is Off.  And like MySQL, you have to create a user with the privileges you need.  A lot of people use the 'sa' account but that is like using 'root' in MySQL and not recommended.  The privileges are too high and allow access to almost everything on the server.  A more limited user is recommended that has only the privileges necessary to do their work.  Here is a screen shot of the SQL Server related configuration items in the Computer Management part of the Administrative Tools on Windows 7.  I have the same 3 protocols enabled for the client and the server.
SQL-Protocols.jpgUtility Programs
MySQL has MySQL Workbench and Microsoft SQL Server has SQL Server Management Studio (Express).  In addition, MySQL has phpMyAdmin https://www.phpmyadmin.net/ which will run on the web server.  And there is adminer4  https://www.adminer.org/ (formerly phpMinAdmin) which will connect to MySQL, MS SQL, PostgreSQL, SQLite, Oracle, Firebird, SimpleDB, Elasticsearch and MongoDB (if you have the PHP drivers for them).

Miscellaneous
This page https://msdn.microsoft.com/en-us/library/ms810810.aspx  shows an overview of "Microsoft's data access technologies."

This page covers PHP ODBC:  http://php.net/manual/en/intro.uodbc.php  It turns out that PHP ODBC requires the same MS SQL drivers as the MS PHP driver called 'sqlsrv'.  The 'sqlsrv' driver functions are more current than the PHP ODBC functions.
 
0
0 Comments

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month