Skip to content

FAQ

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

Contents

How to troubleshoot connection issues

Most connection issues are due to the Microsoft ODBC for SQL Server Driver not being installed properly. You can verify your 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.

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 it's 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 Instances

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.

Clone this wiki locally