Skip to content

FAQ

Jump to bottom
Sicong edited this page Jul 25, 2023 · 72 revisions

Contents

OpenSSL unsupported protocol

Error: SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000102:SSL routines::unsupported protocol]

Usually caused by SQL Server version don't support TLS 1.2.

Solution1: Update SQL Server with cumulative update package Latest updates and version history for SQL Server.

Solution2: Configure OpenSSL to use old TLS, see comment here.

Unable to connect to SQL Server from Debian 10, Ubuntu 20.04 or above

If your server is older than SQL Server 2017, check TLS 1.2 support and How to confirm SQL connection is using TLS 1.2.

For your information, please find detailed suggestions and/or workarounds in these related issues: #1021, #1023, #1112

Why pecl install fails with "Makefile: recipe for target" error?

Most pecl compilation issues are due to installing sqlsrv or pdo_sqlsrv drivers against an unsupported php version. Please check the Support Matrix for PHP versions.

For example, when installing sqlsrv with sudo pecl install sqlsrv without the version number, pecl will install the latest stable release. Since sqlsrv does not support PHP 7.4 until version 5.8.0, installing it prior to the release of version 5.8.0 with PHP 7.4 will guarantee it fails, as you can see in issues 1071, 1072 and 1078.

Note that your Linux or macOS systems may have shipped with php, or some systems have more than one php versions installed. If you're using Ubuntu, consider reading How to Switch between Multiple PHP Version on Ubuntu, or you can find an example of installation script provided by a user.

Before you do a pecl install, make sure you run php -v to check the php version. How about which php and php --ini? Do the versions match? In addition, run pecl version to see if it is associated with the correct php version.

For example, if php -v gives you PHP 7.4.4 (cli) (built: Mar 20 2020 13:47:45) ( NTS ) then pecl version should show something like this:

PEAR Version: 1.10.8
PHP Version: 7.4.4
Zend Engine Version: 3.4.0

Another useful script is php-config.

For PHP 7.4.*, php-config --phpapi should show 20190902. Likewise, php-config --includes will display a long list of include paths, like -I/usr/include/php/20190902 -I/usr/include/php/20190902/main -I/usr/include/php/20190902/TSRM ... etc.

This table shows the php versions and their corresponding php apis and include paths:

PHP Version PHP API Include path
PHP 8.0.* 20200930 /usr/include/php/20200930
PHP 7.4.* 20190902 /usr/include/php/20190902
PHP 7.3.* 20180731 /usr/include/php/20180731
PHP 7.2.* 20170718 /usr/include/php/20170718

The pecl compilation log normally displays php's various include paths, so by looking for /usr/include/php you can easily tell which php version pecl is used when attempting to compile the extension.

Lastly but not necessarily required, you can run pear config-show to examine the PEAR's configuration options. It might be helpful to troubleshoot the compilation issues.

Why pecl install fails on macOS with "Warning: mkdir(): File exists in System.php" message?

When following the macOS instructions to install sqlsrv or pdo_sqlsrv, sometimes pecl install responds with an error like this one below (see issue 1186):

Build process completed successfully
Installing '/usr/local/Cellar/php/7.4.9/pecl/20190902/sqlsrv.so'

Warning: mkdir(): File exists in System.php on line 294

Warning: mkdir(): File exists in /usr/local/Cellar/php/7.4.9/share/php/pear/System.php on line 294
ERROR: failed to mkdir /usr/local/Cellar/php/7.4.9/pecl/20190902

Because a symbolic link might have been created but the directory does not exist. You can fix this by creating the directory manually.

How to troubleshoot connection issues in Linux

Please first verify your ODBC installation in Linux 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. For example, in Ubuntu 18.04:

[~]$ odbcinst -j
unixODBC 2.3.7
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 by cat /etc/odbcinst.ini:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
UsageCount=1

[~]$ ls -l /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
-rwxr-xr-x 1 root root 2063728 Jun 26 03:33 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1

Define a test DSN in /etc/odbc.ini:

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

Verify connection using isql:

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

Some useful references:

How to troubleshoot connection issues in macOS

Please first verify your ODBC installation in macOS 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. For example, in Catalina:

[~]$ odbcinst -j
unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/bamboo/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Verify ODBC Driver entry by cat /usr/local/etc/odbcinst.ini:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=1

[~]$ ls -l /usr/local/lib/libmsodbcsql.17.dylib
-rwxr-xr-x 1 user1 admin  56 Oct 13 12:42 /usr/local/lib/libmsodbcsql.17.dylib -> ../Cellar/msodbcsql17/17.8.1.1/lib/libmsodbcsql.17.dylib

Or you can do a brew info msodbcsql17. You should find the ODBC version and other information in the output.

Verify connection using isql:

[~]$ isql -v -k "Driver=ODBC Driver 17 for SQL Server;Server=<server>;UID=<uid>;PWD=<pwd>;database=<db>"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

When using Apple M1, pay attention to this: if your command prompt is running in x64 emulation mode on the M1, the x64 package will be installed. If you're not running in emulation mode in your command prompt, the ARM64 package will be installed.

Use the odbcinst tool to check the path of ODBC configuration files. For example, in Big Sur (ARM Mac):

[~]$ odbcinst -j
unixODBC 2.3.9
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/bamboo/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Verify ODBC Driver entry by cat /opt/homebrew/etc/odbcinst.ini:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib
UsageCount=1

[~]$ ls -l /opt/homebrew/lib/libmsodbcsql.17.dylib
-rwxr-xr-x 1 user1 admin  56 Oct 15 11:27 /opt/homebrew/lib/libmsodbcsql.17.dylib -> ../Cellar/msodbcsql17/17.8.1.1/lib/libmsodbcsql.17.dylib

Or you can do a brew info msodbcsql17. You should find the ODBC version and other information in the output.

Do not mix binaries installed in emulation mode with others in native mode. You can check which isql, and it should show /opt/homebrew/bin/isql. Another way to check if isql is native, run file /opt/homebrew/bin/isql. The expected output is /opt/homebrew/bin/isql: Mach-O 64-bit executable arm64.

Then you can do a simple connection test using isql as shown above. Please check this related thread for more insights.

If you still have connection issues with ODBC driver in macOS, please post your question to homebrew-mssql-release.

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?

To connect to a named instance on a static port, use Server=servername,port_number. Connecting to a dynamic port is not supported before ODBC version 17.4. Otherwise, you will see an error like this:

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 online doc for details about connecting 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.

If using Apple M1 ARM64, set the flags as below for pecl install:

sudo CXXFLAGS="-I/opt/homebrew/opt/unixodbc/include/" LDFLAGS="-L/opt/homebrew/lib/" pecl install sqlsrv
sudo CXXFLAGS="-I/opt/homebrew/opt/unixodbc/include/" LDFLAGS="-L/opt/homebrew/lib/" pecl install pdo_sqlsrv

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 php@5.6

brew unlink php@7.1

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 php@7.2 (likewise with PHP 7.1.x).

How do I install pear on macOS?

Normally, brew install php@7.x should install pear by default. If it does not, one possible reason is that you have installed pear previously but not using brew. In this case, you can uninstall pear or simply install PHP as usual. 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.

Why do I get "This extension requires the Microsoft ODBC Driver for SQL Server"?

If the supported ODBC driver is missing, you would see this error when using the PHP drivers. However, if ODBC driver is installed but this error persists, please follow the steps in How to troubleshoot connection issues in Linux/macOS.

If the driver file does exist, perhaps the Driver Manager has troubles locating it. In that case, check if the environment variables are correct.

For Red Hat users, issues 805 and 799 might give you some insights. If you still need help, please create a new issue.

Clone this wiki locally