SQLSTATE[HYT00]: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired on Docker php:7.2-fpm

[AimDiab]

PHP Driver version or file name
PDO
SQL Server version
SQL SERVER 2017 DOCKER IMAGE, SQL SERVER 2017 EXPRESS
Client operating system
WINDOWS 10 running Docker php:7.2-fpm image
PHP version
7.2.19
Microsoft ODBC Driver version
ODBC Driver 17 for SQL Server
Table schema
new server, master database
Problem description
Can't connect from PHP(served using nginx) running inside a docker container (php:7.2-fpm) to SQL SERVER running inside docker container (mcr.microsoft.com/mssql/server:2017-latest) or even SQL Server Express running locally on my windows 10 machine.
Expected behavior and actual behavior
I would expect that I could make a database connection since I've installed all the requirements carefully. But I get a login timeout error every time.
Repro code or steps to reproduce

Some background:
I've ran all of this in my docker container (php:7.2-fpm) to get the drivers installed and enabled:

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 msodbcsql17
ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
apt-get install unixodbc-dev
pecl install sqlsrv
pecl install pdo_sqlsrv
echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini
echo extension=sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/20-sqlsrv.ini

I've created my sql server docker container and ran it. Docker ps shows it running, and I can connect to it using Azure Data Studio and run queries. I've installed sql server express on my windows 10 and created a login, user and password and confirmed they are in there, and ran queries successfully in the sqlcmd that opens when you install sql server.

docker pull mcr.microsoft.com/mssql/server:2017-latest

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<removed-for-my-protection>" -p 1433:1433 --name myr_sqlsrv -d mcr.microsoft.com/mssql/server:2017-latest

I've tried connecting using PDO to the dockerized sql server:

$connection = new PDO(
    "sqlsrv:Server=myr_sqlsrv,1433;Database=master",
     'SA',
    '<removed-for-my-protection>'
 );
print_r($connection);

AND using PDO to the local one running on my machine:

$connection = new PDO(
        "sqlsrv:Server=localhost\SQLEXPRESS,1433;Database=master",
        'myr',
        'root1234!@#$'
 );
print_r($connection);

Which both result in:

Error Code: 500
Error: PDOException: SQLSTATE[HYT00]: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

I've tried using the most basic php described in the php documentation:

$serverName = "myr_sqlsrv";
$connectionInfo = array( "Database"=>"master", "UID"=>"sa", "PWD"=>"<removed-for-my-protection>");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
        echo "Connection established.<br />";
}else{
        echo "Connection could not be established.<br />";
        die( print_r( sqlsrv_errors(), true));
}

AND:

$serverName = "localhost\sqlexpress";
$connectionInfo = array( "Database"=>"master", "UID"=>"myr", "PWD"=>"root1234!@#$");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

Which results in:

Connection could not be established.
Array ( [0] => Array ( 
[0] => HYT00 [SQLSTATE] => HYT00 
[1] => 0 [code] => 0 
[2] => [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired 
[message] => [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired ) 
[1] => Array ( 
[0] => 08001 [SQLSTATE] => 08001 
[1] => 11002 [code] => 11002 
[2] => [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2AFA 
[message] => [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2AFA ) 
[2] => Array ( 
[0] => 08001 [SQLSTATE] => 08001 
[1] => 11002 [code] => 11002 
[2] => [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. 
[message] => [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. ) )

And:

Connection could not be established.
Array ( [0] => Array ( 
[0] => HYT00 [SQLSTATE] => HYT00 
[1] => 0 [code] => 0 
[2] => [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired 
[message] => [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired ) 
[1] => Array ( 
[0] => 08001 [SQLSTATE] => 08001 
[1] => -1 [code] => -1 
[2] => [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. 
[message] => [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. ) 
[2] => Array ( 
[0] => 08001 [SQLSTATE] => 08001 
[1] => -1 [code] => -1 
[2] => [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. 
[message] => [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. ) )

I've also read every dozens of posts including other issue posts on this board with the same error message and none of the solutions posed have worked.

[yitam]

Hi @AimDiab this might be related to #302

Let's try to tackle one problem at a time. To try to connect to your SQL Server 2017 Linux in docker:

What's the output of the following command?

docker exec -t myr_sqlsrv /opt/mssql-tools/bin/sqlcmd -S localhost,1433 -U <your_uid> -P <your_pwd> -Q 'select @@Version'

If the above works, you should see an output similar to this one below:

Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64) 
	Jun 15 2019 00:45:05 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)                                                                                                        

(1 rows affected)

In your php scripts, try using localhost,1433 as your server, not myr_sqlsrv.

For your local SQL Server, have you made sure that "it is configured to allow remote connections"?

[AimDiab]

For some reason when I put my password in the command it fails to connect:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'SA'..

But when I leave it out and enter it when it is prompted:

Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)
        Jun 15 2019 00:45:05
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)
(1 rows affected)

I'm copying and pasting my password in both cases, and I'm 100% sure it's correct.

I have tried using localhost,1433 but that doesn't work. The sql server is in a different docker container than the php scripts.

Does the dockerized sql server need to be configured to allow remote connections as well then?

I'll install SSMS and follow the steps in that URL.

Edit:
I was able to get it to connect as one line as well. Still no success with the PHP connection though.

[yitam]

Hi @AimDiab, since your sql server linux is in a different docker container from where you have installed PHP and the drivers, I suppose you have linked them? FYI, you can check how to link containers

About your password problem, from your info above, I figure your password contains $. See this stackoverflow question

[AimDiab]

Hi @yitam
I finished checking that remote connections are allowed on both my sql servers. The setting is enabled for both. Their network protocols are set to .

I noticed in #302 someone mentioned docker mapped their container to 0.0.0.0 instead of localhost. I checked the port mapping using docker port myr_sqlsrv and found the result to be 1433/tcp -> 0.0.0.0:1433. So I tried using 0.0.0.0,1433 as the server but that was unsuccessful too.

I have not linked the containers. I will link them and attempt to connect again.

Yes, my password contains several special characters. Thank you for the resources.

[yitam]

@AimDiab I suggested using localhost because I didn't realize you were using separate containers. After you have linked the containers you might be able to use the name myr_sqlsrv.

This documentation might help you troubleshoot.

For now, perhaps you can try using a password without the special characters first.

[AimDiab]

@yitam Thank you for your help! I really appreciate it.

• I was able to connect finally after adding my container to my docker network that the other container is in. I'm sure linking would work as well but I have a network so I went that route instead.

  • Using: docker network connect <network_name> <container_name>

• I was able to connect with both the name myr_sqlsrv,1433 as well as the i.p. address of the network which I checked using docker network ls to list docker networks.

• I was able to connect using both the PDO and the PHP sqlsrv_connect() method.

It's also worth mentioning:

The app I'm connecting to sql server is running CAKE PHP 2.10. After I was able to make the connection in PHP, I attempted using those credentials to make the connection in CAKE's database.php model.

I ran into some issues with that. I was still getting login timeout expired errors even though the exact same dsn connection string was working if I wrote it myself; once passed through CAKE's infrastructure though, it would give me the error.

I'm guessing there's an issue with CAKE (2.10)'s SQL SERVER database adapter that causes this error when attempting to connect to the newest SQL SERVER 2017.

My work around was to go into app/cake-core/cakephp/cakephp/lib/Cake/Model/Datasource/Database/Sqlserver.php and comment out their $this->_connection = new PDO() and paste in my working PDO connection code. That is in the connect() method.

The reason I'm including the above information is because it was frustrating for me, running around the internet and reading posts about connecting SQL SERVER to CAKE PHP and not finding a fix that worked for me. This will hopefully help anyone attempting to do the same thing as me.

[yitam]

Thanks @AimDiab glad to hear that you have figured it out now. I used linking before so that should also work.

I don't know much about CAKE PHP but thanks for the info. Yes some people might find this useful.

[Hossien-Salamhe]

DB_CONNECTION=sqlsrv
DB_HOST=127.0.0.1 // localhost not working
DB_PORT=1433
DB_DATABASE=NameDB
DB_USERNAME=SA
DB_PASSWORD=[yourPassword]

[yitam]

Hi @Hossien-Salamhe we need more details than what you've provided. Please create a new issue to provide more info for us to investigate the problem, and if you like you can reference this issue.

[mraheelkhan]

@yitam , @AimDiab thank you, the details you shared worked for me too.

I was in the same case, the containers were different and also on different networks so it was throwing timeout.

I had to check the container network for my PHP/Laravel app via
docker inspect containerName -f "{{json .NetworkSettings.Networks }}"

And register the SQL Server container within that network via
docker network connect php_laravel_network_name sql_server_2022_containerName