ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:database:microsoft_sql_server

Microsoft SQL Server

Description

There are 2 supported drivers that allow connections to Microsoft SQL Server databases from SQL Server 2005 upwards.

mssqlnative

This driver, which runs on both Windows and Linux clients, allows connections to Microsoft SQL Server databases versions 2008 and up, and uses the Microsoft Native Client libraries. This driver is recommended for Windows Clients running PHP 5.3 and higher, and Linux Clients running PHP 7.0 and higher, and provides the fastest PHP connection.

More about the Microsoft SQL Server client for Linux here

mssql

This allows connections from Unix based clients using the freetds libraries. It can be used with version 5.x of PHP

More about the FreeTDS libraries here

mssqlnative

Specification
Driver Namemssqlnative
Data Providermssqlnative
StatusActive1)
WindowsYes
UnixYes
ADOdb V5Yes
ADOdb V6Yes
Alternatives

PDO driver for SQL Server
ODBC driver for SQL Server

See Also

setConnectionParameter()

This is the preferred driver for connecting to a SQL server database from both Windows and Linux clients.

The original driver was contributed by Microsoft and provides connections to SQL server databases greater than SQL Server 2003.

  • On Windows clients, It requires PHP version 5.3 or greater, Microsoft native client software and the appropriate PHP driver.
  • On Linux clients, PHP version 7.0 and higher is required.

Sequences are supported in native mode in SQL Sever 2012, and emulated in earlier versions.


DateTime Fields

The default behavior for the PHP mssqlnative driver is to return date/time fields as a PHP dateTime object, that looks like this:

[RequiredDate] => DateTime Object
(
    [date] => 1996-08-01 00:00:00.000000
    [timezone_type] => 3
    [timezone] => America/Denver
)

However, in order to improve portability, the default behaviour of the ADOdb mssqlnative driver is to return the date as a string:

[RequiredDate] => 1996-08-01 00:00:00.000000

In order to return the dateTime as an object, the following action is required:

$db = ADONewConnection('mssqlnative');
$db->setConnectionParameter('ReturnDatesAsStrings',false);
$db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');

Unicode Strings

You can set the character set to UTF-8 using:

$db = ADONewConnection('mssqlnative');
$db->setConnectionParameter('characterSet','UTF-8');
$db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');

Using Windows Authentication

To use Windows, instead of SQL Server authentication, pass NULL as the user and password as shown below

$db = ADONewConnection('mssqlnative');
$db->connect('SERVER\SQLEXPRESS',NULL,NULL,'NORTHWND');

Common Connection Problems

Recent versions of SQL Server fail to connect with an error Message: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.

This problem can be resolved by adding

$db->setConnectionParameter('TrustServerCertificate',1);

before issuing the $db→connect() statement


How to create a connection under Linux

Establishing a connection to a SQL server database from a Linux client can be difficult, but by installing the freetds tools as well as the Microsoft ones provides a highly useful tool, tsql

In the example, our database is running on a server 192.168.0.65:

# tsql -LH 192.168.0.65
    ServerName STUDY
   InstanceName SQLEXPRESS
    IsClustered No
        Version 12.0.2000.8
            tcp 55644
             np \\STUDY\pipe\MSSQL$SQLEXPRESS\sql\query

The response from the command tells us the

  • The SQL server instance is visible from the client
  • the service is running on port 55644

We can now create a connection string:

$db = ADONewConnection('mssqlnative');
$db->connect('192.168.0.65,55644',$user,$pass,'NORTHWND');

mssql (Unix)

Specification
Driver Namemssql
Data Providermssql
StatusActive2)
WindowsNo
UnixYes
ADOdb V5Yes
ADOdb V6Yes
Alternatives

PDO driver for FreeTDS

This is the preferred driver for connecting to a SQL server database from Linux/Unix clients, when the PHP version is less than 7.0 This driver provides access to Microsoft SQL Server databases from unix systems via Freetds


mssql (Windows)

Specification
Driver Namemssql
Data Providermssql
StatusObsolete3)
WindowsYes
UnixNo
ADOdb V5Yes 4)
ADOdb V6No

This driver was the primary connection method to SQL Server databases up to PHP Version 5.2. The windows dll associated with this driver (php_mssql.dll) was removed from the standard PHP distribution in PHP Version 5.3. There is no community support for this driver.


mssqlpo

Specification
Driver Namemssqlpo
Data Providermssql
StatusInactive5)
WindowsNo
UnixYes
ADOdb V5Yes
ADOdb V6Yes

This driver provides undocumented portability extensions to the Unix mssql driver

mssql_n

Specification
Driver Namemssqlpo
Data Providermssql
StatusInactive6)
WindowsNo
UnixYes
ADOdb V5Yes
ADOdb V6Yes

This driver appears to provide Unicode enhancements to the mssql driver. You can set character sets using the setConnectionParameter() command

1) , 2)
This driver is actively supported by ADOdb project members
3)
This driver will be removed in ADOdb version 6
4)
PHP Version 5.2 and lower
5) , 6)
This driver is not actively supported or enhanced by ADOdb project members, but you can provide fixes and enhancements if you have the expertise
v5/database/microsoft_sql_server.txt · Last modified: 2022/11/15 02:13 by mnewnham