Msphpsql: Sporadic: SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

[lukeify]

Problem description

I am aware of issue #679, however, that seems to relate to a persistent login timeout, whereas my login timeouts issue is only sporadic. Maybe 1 in 5 queries will result in the error present in the title, additionally, query results are often extremely slow; which makes even something like development an unliveable environment.

I'm running Laravel 5.6 on a Homestead instance with a full provisioning configuration provided below. My post-provision file which should give an idea of the setup involved.

# https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu
# https://askubuntu.com/questions/930712/installation-problems-with-ms-sql-server-for-linux/945957

# Include bash configuration variables
. /home/vagrant/code/configuration.sh

# Add
curl https://packages.microsoft.com/keys/microsoft.asc --silent | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
sudo apt-get update -q

# Downgrade OpenSSL to 1.0.2 for MSSQL
sudo apt-get install -o Dpkg::Options::="--force-confnew" openssl=1.0.2g-1ubuntu4.10 -y --allow-downgrades
sudo apt-get install mssql-server -y

# Setup MSSQL
sudo MSSQL_PID=Express ACCEPT_EULA=Y MSSQL_SA_PASSWORD="$mssql_sa_pw" /opt/mssql/bin/mssql-conf -n setup

# Add MSSQL tooling
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev

# Add MSSQL tooling to our path
sudo ln -s /opt/mssql-tools/bin/* /usr/local/bin

# Create our database
sqlcmd -S localhost -U SA -P "$mssql_sa_pw" -Q "CREATE DATABASE $mssql_db_database"

# Follow configuration for PHP (from step 3, as above commands covered prior steps regardless)
# https://github.com/Microsoft/msphpsql
sudo pear config-set php_ini `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"` system

# Install SQL Server PECL extensions
sudo pecl install sqlsrv-5.2.0RC1
sudo pecl install pdo_sqlsrv-5.2.0RC1

# Include in configuration
echo "extension=sqlsrv.so" | sudo tee --append /etc/php/7.2/fpm/php.ini
echo "extension=pdo_sqlsrv.so" | sudo tee --append /etc/php/7.2/fpm/php.ini

Driver version or file name

Not sure how to find this. Tell me how and I will add.

SQL Server version

Not sure how to find this. Tell me how and I will add.

## Client operating system
Ubuntu 16.04.4 LTS (GNU/Linux 4.4.0-116-generic x86_64)

PHP version

PHP 7.2 / 5.2.0RC1 pdo-sqlsrv, 5.2.0 sqlsrv.

Microsoft ODBC Driver version

ODBC Driver 17 for SQL Server / unixodbc / msodbcsql17 (version 17.0.1.1-1)

## Expected behavior and actual behaviour

No login timeouts. Instead, I get sporadic login timeouts as described above.

## Repro code

Sporadic, but even simple queries made by Eloquent such as SELECT * FROMitems`can result in a failure. I don't use thesqlcmd` command line tool all that often, so I cannot determine if this is a PDO driver issue.

[david-puglielli]

Hi @lukeify for the most updated instructions on installing the drivers please see this page. Note that there is no need to install 5.2.0RC1 anymore, as pecl install sqlsrv and pecl install pdo_sqlsrv now grab the latest versions.

I also see that in your post-provision file there is no installation for the ODBC driver itself (only unixODBC). To install the latest ODBC driver, please look at these instructions. It's important to get the latest ODBC driver because older versions had a known bug that caused intermittent disconnects.

Please let us know if this helps.

[lukeify]

Hi @david-puglielli, thanks for the reply. I did notice that 5.2.0 drivers were released, however, simply altering:

# Install SQL Server PECL extensions
sudo pecl install sqlsrv-5.2.0RC1
sudo pecl install pdo_sqlsrv-5.2.0RC1

to remove the RC1 (or the -5.2.0RC1) suffix results in this error:

PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_sqlsrv.so' (tried: /usr/lib/php/20170718/pdo_sqlsrv.so (/usr/lib/php/20170718/pdo_sqlsrv.so: undefined symbol: php_pdo_register_driver)

So, the 5.2.0 build is unusable for me unless this can be resolved. Installation of RC1 doesn't have this problem.

With respect to the installation of msodbcsql, it appears that unixodbc/mssql-tools requests to install msodbcsql17 anyway (it is indeed installing, if not explicitly, in my VM instance version 17.0.1.1-1), but I'm not sure which one—msodbcsql17 or msodbcsql— is actually needed here. Additionally, why is msodbcsql not listed on this page as something to install?

[david-puglielli]

How are you installing PHP? It's quite peculiar that the error message you see occurs only with 5.2.0, because that error message is usually caused by not installing php-pdo, so if you see it with 5.2.0 you should have seen it with 5.2.0RC1 as well. It could also be that PDO is not configured to load before PDO_SQLSRV. Detailed instructions for handling the load order are found here.

msodbcsql17 is version 17, the latest and recommended version (you could also use msodbcsql, which is version 13, but as I've noted above it does not include the intermittent disconnection fix).

To get back to the login timeouts you are seeing, have you noticed if they happen when running queries via the command line (using sqlcmd)? If they happen with sqlcmd, the issue may be in the ODBC driver, if not it confirms the problem is with the PHP driver.

[luiz-luxfacta]

We have the same issue over here in one of our projects that uses php and SQL Server.

PDOException(code: HYT00): SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

We already had the problem using msphpsql 4.3 with msodbc 13 and were hoping that msodbc 17 would fix the issue (based on the reports from issue #679).
Sadly we were able to update to msphpsql 5.2 (stable) and msodbc 17 this week and the issue persists.
Our PHP version is 7.0.25.

[ChrisBachi]

I have same. Fresh installed Debian 9, msodbc 17 (17.0.1.1-1)
SQL Server 2014

./sqlcmd -S myserver\myinstance -U sa -P mypassword
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AFA.
Sqlcmd: Error: Microsoft ODBC Driver 17 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..

nc -zv myserver 1433
myserver.mydomain.local [myip] 1433 (ms-sql-s) open

And sqlcmd on sql server itself works.

[lukeify]

@david-puglielli PHP is included OOTB with the virtual machine instance that I'm running for my development environment: laravel/homestead 5.2.0. This box runs PHP7.2. Regarding the failed installation of the 5.2.0 driver, I've further figured out I can install the 5.2.0 release of sqlsrv.so without problems, but 5.2.0 release of pdo_sqlsrv.so does not work out of the box when I issue:

sudo pecl install pdo_sqlsrv
echo "extension=pdo_sqlsrv.so" | sudo tee --append /etc/php/7.2/fpm/php.ini

whereas 5.2.0-RC1 does. However, this is a separate issue. I have updated my issue to state that I am using msodbcsql17 as per my confirmation that unixodbc also installs the former.

I use sqlcmd rarely, if ever; so I'll have to do some testing on that and get back to you.

[david-puglielli]

@luiz-luxfacta Could you clarify, does the problem happen intermittently, or does it happen whenever you try to connect? Do you get the same behaviour when connecting with sqlcmd?

@ChrisBachi You are trying to connect to a named instance, so you need to specify the port number. Try

./sqlcmd -S myserver\myinstance,1433 -U sa -P mypassword

1433 is the default port, replace it if appropriate for your configuration. For more information, see issues #442 and #470, and this page.

[luiz-luxfacta]

@david-puglielli The problem happens intermittently.
I will try to do some testing with sqlcmd but since it is intermitent it might be hard. I will check if we are able to make a script to loop a connection test with sqlcmd.

[luiz-luxfacta]

@david-puglielli we have confirmed that the error also occurs with sqlcmd.

We ran the following script:

#/bin/bash
while true
do
        sqlcmd -S <server> -U <user> -P <password> -Q GO
        date
        sleep 5
done

\

The script ran for about 18 hours (from 12 PM to 6 AM) and we got the following error a couple of times (between 8PM and 6AM):

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AF9.
Sqlcmd: Error: Microsoft ODBC Driver 17 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..

[yitam]

Hi @luiz-luxfacta
Based on your reply above, we suspect the intermittent timeout is caused by problems in your network or your sql server(s) not accepting a connection.

[luiz-luxfacta]

Thanks for you feedback @yitam, we were already investigating with out infraestructure team.
We were not able to find any network related issues but we will continue to do some digging and I'll let you know if we find anything out.

[david-puglielli]

@lukeify @luiz-luxfacta Any updates on this issue? If there is no activity in the next few days, we will close this issue.

[lukeify]

I switched to using MySQL (precisely due to this bug).

[david-puglielli]

@lukeify Did you test with sqlcmd?

[luiz-luxfacta]

@david-puglielli
We are still unable to determine if it's an instance, a network or a driver related issue.
You can close this issue if lukeify has no more insterest in pursuing it.
We will reopen it or open a new one if we are able to discard other possibilities.

[luiz-luxfacta]

@yitam @david-puglielli
We are still unable to find out what is happening but we were able to run the same tests on our application UAT environment (which is outside our local network) and the issue isn't happening in there.
We will continue to monitor but as you said the issue is problably related to some kind of network or instance error.
Thank you for trying to help us figure this out.

[david-puglielli]

@luiz-luxfacta Interesting. Do you use the same PHP driver and ODBC driver in your UAT environment? It does sound like a network or configuration issue on the server side...

[yitam]

Closing this due to inactivity @lukeify and @luiz-luxfacta. Please feel free to reopen if you have any question about this.

[shadab95]

Dear @meet-bhagdev ,I'm getting the issue "OperationalError: ('HYT00', u'[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')" and I have installed msodbcsql and its dependencies..my code is this "pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + config_id[0].server +',1433' + ';DATABASE=' + config_id[0].db_name + ';UID=' + config_id[0].user_name + ';PWD=' + config_id[0].password)". Please help @meet-bhagdev
or anyone if possible

[david-puglielli]

@shadab95 We don't work on pyODBC, but we suspect there is a network issue involved. Can you connect using sqlcmd?

[shadab95]

@david-puglielli yes I can connect using sqlcmd but the connection using driver is giving me the issue on the python files.Please could you suggest me something

[david-puglielli]

@shadab95 If you are still having trouble please post your question to the pyODBC page.

[esetnik]

I am having what I believe is the same issue as originally described. In a moderate volume application I am having intermittent login timeout expired exceptions to an AWS RDS instance running SQL Server Web Edition Engine version 14.00.3035.2.v1. The database server does not report any abnormal load during this time and the login timeout resolves itself within ~60 seconds.

The following NEW packages will be installed:
  autotools-dev libc-l10n libltdl-dev libltdl7 libodbc1 libtool locales
  msodbcsql17 mssql-tools odbcinst odbcinst1debian2 unixodbc unixodbc-dev
0 upgraded, 13 newly installed, 0 to remove and 1 not upgraded.
Need to get 11.3 MB of archives.
After this operation, 19.4 MB of additional disk space will be used.
Get:1 https://packages.microsoft.com/debian/9/prod stretch/main amd64 msodbcsql17 amd64 17.2.0.1-1 [4069 kB]
Get:2 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 libc-l10n all 2.24-11+deb9u3 [820 kB]
Get:3 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 locales all 2.24-11+deb9u3 [3287 kB]
Get:13 https://packages.microsoft.com/debian/9/prod stretch/main amd64 mssql-tools amd64 17.2.0.2-1 [1331 kB]
Get:4 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 autotools-dev all 20161112.1 [73.4 kB]
Get:5 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 libltdl7 amd64 2.4.6-2 [389 kB]
Get:6 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 libltdl-dev amd64 2.4.6-2 [161 kB]
Get:7 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 libodbc1 amd64 2.3.4-1 [214 kB]
Get:8 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 libtool all 2.4.6-2 [545 kB]
Get:9 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 odbcinst1debian2 amd64 2.3.4-1 [75.2 kB]
Get:10 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 odbcinst amd64 2.3.4-1 [43.5 kB]
Get:11 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 unixodbc amd64 2.3.4-1 [54.7 kB]
Get:12 http://cdn-fastly.deb.debian.org/debian stretch/main amd64 unixodbc-dev amd64 2.3.4-1 [249 kB]

Dockerfile

FROM php:7.2-fpm-stretch

# Set timezone to America/New_York
ENV TZ=America/New_York

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime \
  && echo $TZ > /etc/timezone \
  && echo "date.timezone=\"$TZ\"" >> /usr/local/etc/php/conf.d/docker-vars.ini

RUN apt-get update && \
  apt-get install -y --no-install-recommends gnupg \
  apt-transport-https \
  cron \
  gettext \
  libicu-dev \
  && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -  && \
  curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
  apt-get update && \
  ACCEPT_EULA=Y apt-get install -y \
  unixodbc-dev \
  # libgss3 \
  # odbcinst \
  msodbcsql17 \
  mssql-tools \
  locales && \ 
  ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd && \
  ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp && \
  echo "en_US.UTF-8 UTF-8" > /etc/locale.gen && \
  locale-gen && \
  rm -r /var/lib/apt/lists/*

RUN docker-php-ext-install -j$(nproc) intl

RUN pecl install sqlsrv pdo_sqlsrv && \
  docker-php-ext-enable sqlsrv pdo_sqlsrv

[yitam]

hi @esetnik,

the login timeout resolves itself within ~60 seconds.

Intermittent login timeouts may be inevitable at times, so starting with ODBC Driver 17.2, the feature connection resiliency is enabled. By default, it will attempt to reestablish a connection upon detecting a broken connection. You may want to try configuring the settings of ConnectRetryCount and ConnectRetryInterval in the connection string (you can find more details in our documentation).

[esetnik]

I am already using 17.2 and it looks like the default is to retry a single time. Additionally multiple servers are having these sporadic connection issues at the same time so I don't think it's related to a network issue (they are all in the same VPC). Intermittent login timeouts may be inevitable at times, What other scenarios would cause login timeouts to be inevitable?

Is the issue possibly that i'm using unixodbc v2.3.4 and not v2.3.7?

[yitam]

@esetnik when connecting to a database in the cloud, one can't guarantee it always works. Since the connection can resolve itself within 60 seconds, it is not a problem with the driver.

About unixodbc, the older version does have bugs, but it's up to you to decide whether to upgrade to version 2.3.7.

[esetnik]

Unfortunately v2.3.7 isn't available on debian stretch. Are there good instructions published by microsoft for installing v2.3.7 manually on stretch?

[yitam]

@esetnik we do have some old instructions but are currently working on updating that. Please stay tuned for the upcoming release of ODBC 17.3