Skip to content

FAQ

Jump to bottom
v-susanh edited this page Apr 13, 2018 · 74 revisions

Contents

How to troubleshoot connection issues in Linux/macOS

Most connection issues are due to the Microsoft ODBC for SQL Server Driver not being installed properly. Please verify your ODBC installation using the unixODBC tools to make sure you can connect to the Microsoft SQL server with the ODBC driver independent of the PHP driver.

Use the odbcinst tool to check the path of ODBC configuration files:

[~]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/user/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Verify ODBC Driver entry in odbcinst.ini:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1

[~]$ ls -l /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
-rwxr-xr-x. 1 root root 16463370 Jan  3 09:38 /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2

Define a test DSN in odbc.ini:

[MSSQLTest]
Driver = ODBC Driver 13 for SQL Server
Server = tcp:sqlserver.mydomain.com

Verify connection using isql:

[~]$ isql -v MSSQLTest uid password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

What is the default version for Microsoft ODBC Driver for SQL Server?

Current default version for Microsoft ODBC for SQL Server Driver is 13.1. i.e. if you install msodbcsql without providing a version number then the default version 13.1 will be installed. If you want to install the latest version 17 then you must specify msodbcsql17.

Please see the ODBC driver installation guide for instructions on how to install the Microsoft ODBC Driver for SQL Server on Linux and macOS.

What is this error "PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so'"?

PDO must be loaded before the PDO_SQLSRV driver otherwise would result in an "Unable to load dynamic library" error msg when running any PHP commands:

[~]# php -v
PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /usr/lib64/php/modules/pdo_sqlsrv.so (/usr/lib64/php/modules/pdo_sqlsrv.so: undefined symbol: php_pdo_register_driver), /usr/lib64/php/modules/pdo_sqlsrv.so.so (/usr/lib64/php/modules/pdo_sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0
PHP 7.2.4 (cli) (built: Mar 27 2018 17:23:35) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.4, Copyright (c) 1999-2018, by Zend Technologies

This could happen if your php.ini loads pdo_sqlsrv.so but pdo is loaded with its own pdo.ini file (it is probably named something like 10-pdo.ini). These extension-specific .ini files are loaded after php.ini. .

You could avoid this by putting the pdo_sqlsrv.so in its own ini file that has a load ordering AFTER pdo.ini.

e.g.

echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini

and remove the line extension=pdo_sqlsrv.so from php.ini

Why do I get an error about mismatched PHP modules?

Your installed PHP drivers must match your installed PHP version otherwise you might get an error:

[~]# php --ini
PHP Warning:  PHP Startup: pdo_sqlsrv: Unable to initialize module
Module compiled with module API=20160303
PHP    compiled with module API=20170718
These options need to match
 in Unknown on line 0

This could happen if you compiled the latest version of the drivers with one PHP version and pecl installed another. To rectify this problem, remove the existing modules sqlsrv.so and pdo_sqlsrv.so (which are probably in /usr/lib64/php/modules/) and then try installing everything again.

How do I connect to Named Instance in Linux/macOS?

A port number is required to connect to a named instance or will result in the error:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Please refer to the MSDN page for a description on connection to named instances.

How do I debug the error from unixODBC that ODBC driver is not found?

If you get the error message:

SQLSTATE: 01000 Code: 0 Message: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found #673

Verify the driver exists and you have permission to access it:

[~]$ odbcinst -q -d -n "ODBC Driver 13 for SQL Server"
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1
[~]$ ls -l /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
-rwxr-xr-x. 1 root root 16463370 Jan  3 09:38 /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2

If the driver is in place, use 'ldd' (or 'otool' on macOS) to check the library dependencies to make sure all the dependent libraries exist:

[~]$ ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
	linux-vdso.so.1 =>  (0x00007fff399d2000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f0b7e2d3000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f0b7e0cb000)
	libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f0b7deb8000)
	libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f0b7da57000)
	libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f0b7d76f000)
	libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f0b7d521000)
	libcurl.so.4 => /lib64/libcurl.so.4 (0x00007f0b7d2b8000)
	libssl.so.10 => /lib64/libssl.so.10 (0x00007f0b7d046000)
	libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f0b7ce40000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f0b7cb38000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f0b7c836000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f0b7c61f000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0b7c403000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f0b7c040000)
	/lib64/ld-linux-x86-64.so.2 (0x000055c4bf717000)
	libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f0b7be35000)
	libz.so.1 => /lib64/libz.so.1 (0x00007f0b7bc1f000)
	libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f0b7b9ec000)
	libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f0b7b7e7000)
	libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f0b7b5d9000)
	libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f0b7b3d5000)
	libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f0b7b1ba000)
	libidn.so.11 => /lib64/libidn.so.11 (0x00007f0b7af87000)
	libssh2.so.1 => /lib64/libssh2.so.1 (0x00007f0b7ad5d000)
	libssl3.so => /lib64/libssl3.so (0x00007f0b7ab10000)
	libsmime3.so => /lib64/libsmime3.so (0x00007f0b7a8e9000)
	libnss3.so => /lib64/libnss3.so (0x00007f0b7a5bf000)
	libnssutil3.so => /lib64/libnssutil3.so (0x00007f0b7a391000)
	libplds4.so => /lib64/libplds4.so (0x00007f0b7a18d000)
	libplc4.so => /lib64/libplc4.so (0x00007f0b79f88000)
	libnspr4.so => /lib64/libnspr4.so (0x00007f0b79d49000)
	liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x00007f0b79b3a000)
	libldap-2.4.so.2 => /lib64/libldap-2.4.so.2 (0x00007f0b798e6000)
	libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f0b796be000)
	libsasl2.so.3 => /lib64/libsasl2.so.3 (0x00007f0b794a0000)
	libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f0b7923e000)
	libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f0b79007000)
	libfreebl3.so => /lib64/libfreebl3.so (0x00007f0b78e03000)

Where are the mssql-tools I just installed?

After following instructions to install mssql-tools you will find the tools in /opt/mssql-tools/bin. You may want to add that to your path or link them to /usr/bin if you do not want to specify the full path when executing the tools each time:

sudo ln -sfnv /opt/mssql-tools/bin/* /usr/bin

Why am I getting a "sql.h not found" error when compiling the PHP driver?

The PHP driver requires the unixODBC-dev (or unixODBC-devel package depending on platform) package for compilation. Please see the Linux and macOS Installation Tutorial for the Microsoft Drivers for PHP for SQL for installation instructions.

How do I upgrade PHP on macOS?

Before upgrading to the latest PHP, please unlink the older versions of PHP first. For example,

brew unlink php56

brew unlink php71

If you already have an older version of PHP 7.2.x installed, such as PHP 7.2.0, and want to upgrade to the latest version, then you will need to brew upgrade --cleanup php72 (likewise with PHP 7.1.x).

How do I install pear on macOS?

If brew install php72 --with-pear or brew install php72 --with-pear fails, one possible reason is you have pear installed previously but not using brew. In this case, you can uninstall pear or simply install PHP using brew without the --with-pear option. Then, after PHP is successfully installed, take the following steps to install pear:

curl -O http://pear.php.net/go-pear.phar

sudo php -d detect_unicode=0 go-pear.phar

You will see a list of options of where to install pear, so

  1. Type 1 and hit return
  2. Type /usr/local/pear

The above step changes the installation base for pear. Then the next step is to change where pear's binaries go:

  1. Type 4 and hit return
  2. Type /usr/local/bin

After these two options have been configured, press return to install pear. When the installation is complete, type pear version and see if it's linked with the latest PHP version (php -v). For details, please read the Getting PEAR package.

If you have problems with pear/pecl, this checking if PEAR works might help.

Clone this wiki locally