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

[namsudtho]

+## Driver version or file name
+php72-php-sqlsrv-x86_64-5.2.0~RC1-1.el7.remi
+
+## SQL Server version
+Please tell us what the SQL Server version is.
+
+## Client operating system
+CentOS 7.4
+
+## PHP version
+PHP 7.2
+
+## Microsoft ODBC Driver version
+ODBC Driver 17 for SQL Server
+
+## Table schema
+Please tell us the table schema
+
+## Problem description
+SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
+
+## Expected behavior and actual behavior
+Please tell us what should happen and what happened instead
+
+## Repro code
This is the page I test to connect using the sqlcmd command.

This is the page I test to connect with the php language.

This is the page that outputs the code above.

[david-puglielli]

Hi @namsudtho, it looks like you are trying to connect to a named instance. To connect to a named instance, you need to specify the port in the connection string. For example, if you are using the standard SQL Server port (1433), try

$dbh = new PDO("sqlsrv:Server=$host,1433;Database=$dbname", $user, $pass);

Issue #190 and this link may help.

[namsudtho]

Thank you for your help.
But I found another problem. Error message displayed by image.
I am sure my "username" and "password" are correct.

[david-puglielli]

That error message can pop up if the name of the database is wrong as well, so you should double check that. Also, why does it say ODBC Driver 13 when you were using 17? If you are running multiple instance of the ODBC driver you can use the Driver keyword to pick one; for example, add the following to the connection string:

Driver={ODBC Driver 17 for SQL Server};

[namsudtho]

Hi @david-puglielli, I try to follow the way you tell everything. But it still does not connect. Please remote to help?

[benyanke]

I'm getting this happening randomly as well. I know(?) it's not a config issue, as it only happens intermittently. Any ideas as to why this may happen intermittently?

Next Illuminate\Database\QueryException: SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired (SQL: Select [...] from [...])

[david-puglielli]

@benyanke @namsudtho If the error message is intermittent, it may be caused by an issue in version 13 of the ODBC driver affecting time stamps. This has been fixed in the latest release of the ODBC driver, available here or through your package manager.

Even if the error message is not intermittent, I would advise trying the latest ODBC driver as well from the links above, as it has just gone RTW. Please let us know if this fixes the issue.

[namsudtho]

Hi @david-puglielli, I'm can connected. Thank you for your help.

[benyanke]

@david-puglielli is there a way to use the latest version with pdo_sqlsrv from pecl? Having the latest ODBC driver is all well and good, but if the rest of the stack doesn't support it, we're still out of luck.

[david-puglielli]

@benyanke you can download using pecl install pdo_sqlsrv-5.2.0RC1 to get the latest preview. (pecl install pdo_sqlsrv only installs the latest stable release, which is version 4.3 and does not support the Driver keyword.)

[yitam]

@benyanke @namsudtho we are closing this issue due to inactivity. Please feel free to reopen if you still have any question.

[raylucker]

@benyanke @namsudtho If the error message is intermittent, it may be caused by an issue in version 13 of the ODBC driver affecting time stamps. This has been fixed in the latest release of the ODBC driver, available here or through your package manager.

Even if the error message is not intermittent, I would advise trying the latest ODBC driver as well from the links above, as it has just gone RTW. Please let us know if this fixes the issue.

Tried to use this suggestion to my problem. But it ends up on this error "SQLSTATE[IM004]: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed".
Dont know what to do now :/

[williammoreschi]

I am having trouble connecting the database. I created a new VM using Ubuntu 18.4 LTS.
I followed the steps given on the Microsoft website (https://bit.ly/2Mfz7YZ).

sudo su
add-apt-repository ppa:ondrej/php -y
apt-get update
apt-get install php7.3 php7.3-dev php7.3-xml -y --allow-unauthenticated
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo 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
sudo apt-get install unixodbc-dev
sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv
sudo su
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
exit
sudo su
apt-get install libapache2-mod-php7.3 apache2
a2dismod mpm_event
a2enmod mpm_prefork
a2enmod php7.3
echo "extension=pdo_sqlsrv.so" >> /etc/php/7.3/apache2/conf.d/30-pdo_sqlsrv.ini
echo "extension=sqlsrv.so" >> /etc/php/7.3/apache2/conf.d/20-sqlsrv.ini
exit
sudo service apache2 restart

Using the code below is turning on the error (SQLSTATE[HYT00]: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired)

<?
$server = "111.111.111.111\instance";
$db = "DB";
$pass = "xxx";
$user = "yyy";

try {
    $dbh = new PDO('sqlsrv:Server=$server;Database=$db',$user,$pass);
    foreach($dbh->query('SELECT * from users') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
}

$server = "111.111.111.111\instance,1433";
$db = "DB";
$pass = "xxx";
$user = "yyy";

try {
    $dbh = new PDO('sqlsrv:Server=$server;Database=$db',$user,$pass);
    foreach($dbh->query('SELECT * from users') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
}

?>

I also tried using an example from microsoft (https://bit.ly/2H4h0mT)

Error information:
SQLSTATE: HYT00
Code: 0
Message: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
SQLSTATE: 08001
Code: -1
Message: [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLSTATE: 08001
Code: -1
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.

Could someone help me understand the error.
In IIS and XAMPP I made it work.
In ubuntu it does not work.

[yitam]

@williammoreschi looks like you're using a named instance, and please check our FAQ

It's high priority for ODBC driver to support named instances outside Windows. Please stay tuned.

[williammoreschi]

HI @yitam
MY problem was port by default (1433), putting or not the error was persistent. I solved the problem using the code ( Sarke - https://bit.ly/2EVY5rZ )

[yitam]

Good to know you got it working @williammoreschi
As mentioned in #927 , this will be fixed in the future releases.
FYI, this is another reference about setting up DSNs

[bsakizli]

Hi,

SSH Command -> sudo setsebool -P httpd_can_network_connect_db 1

:)

[andrconstruction]

Hi @namsudtho, it looks like you are trying to connect to a named instance. To connect to a named instance, you need to specify the port in the connection string. For example, if you are using the standard SQL Server port (1433), try

$dbh = new PDO("sqlsrv:Server=$host,1433;Database=$dbname", $user, $pass);

Issue #190 and this link may help.

If using named innstance you should escape backslash
'sqlsrv:Server=192.168.0.2\SQLEXPRESS,1433;Database=BigBrotherData',