PHP and Microsoft SQL Server

Dave BaldwinFixer of Problems
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.
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.

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. 

Up until PHP 5.2, there was a 'mssql' driver 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 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':

'mssql' driver does Not work with PHP 5.3 and later on Windows .  See here for 'sqlsrv' :   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.  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.jpg Utility Programs
MySQL has MySQL Workbench and Microsoft SQL Server has SQL Server Management Studio (Express).  In addition, MySQL has phpMyAdmin which will run on the web server.  And there is adminer4 (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).

This page  shows an overview of "Microsoft's data access technologies."

This page covers PHP ODBC:  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.
Dave BaldwinFixer of Problems
Zippity doo dah, zippity ay, my oh my what a wonderful day!!

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.