10

I have a laravel application that connect a sql server db on Azure.

On my local Wamp server the application works. I have installed on my linux server using a docker image, and don't connect the Azure DB. Every time returns this error message:

SQLSTATE[HYT00]: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (SQL: select * from [mytable])

MSDOBC Driver, sqlsrv and pdo_sqlsrv are correctly installed.

I suppose is something related with laravel because if I query the db with a php script works without problem.

My .ENV file has db settings:

DB_CONNECTION=sqlsrv
DB_HOST=db.database.windows.net
DB_PORT=1433
DB_DATABASE=db_name
DB_USERNAME=db_user
DB_PASSWORD=pwd

Laravel log and docker log don't tell nothing interesting, I don't have any idea how to solve.

cesare
  • 2,098
  • 19
  • 29
  • "On my local Wamp server works": What's running on WAMP? The db server or the webserver? – Ben Hillier Jul 28 '20 at 10:48
  • Where does "db.database.windows.net" point to? Your docker container? – Ben Hillier Jul 28 '20 at 10:48
  • Is port 1433 open on the DB server? – Ben Hillier Jul 28 '20 at 10:49
  • The sql server is on Azure cloud. On WAMP there is the webserver (for development) – cesare Jul 28 '20 at 10:52
  • Yes the port 1433 is open, if I connect from my pc i connect without problem. I tried set port=null and nothing changed. – cesare Jul 28 '20 at 10:53
  • SQL server driver is different in Linux, please ref: https://learn.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver15#installing-the-drivers-with-php-fpm-on-ubuntu and https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15 – Leon Yue Jul 29 '20 at 05:59
  • I think my driver are installed correctly. I tried the sqltest.php script and the response is correct: "Microsoft SQL Azure (RTM) - 12.0.2000.8 Jul 17 2020 00:35:17 Copyright (C) 2019 Microsoft Corporation" – cesare Jul 30 '20 at 11:07

1 Answers1

8

Azure SQL Database supports only the tabular data stream (TDS) protocol (accessible over TCP and the default port of 1433) and uses its own IP-based firewall. So you may try the following:

  • Use connection string with protocol, server name and port. In your case you need to use tcp:db.database.windows.net,1433 as a value of DB_HOST.

  • Add the IP address of your LINUX server as a firewall rule. This is explained in the documentation:

When a computer tries to connect to your server from the internet, the firewall first checks the originating IP address of the request against the database-level IP firewall rules for the database that the connection requests.

If the address is within a range that's specified in the database-level IP firewall rules, the connection is granted to the database that contains the rule.

  • If the address isn't within a range in the database-level IP firewall rules, the firewall checks the server-level IP firewall rules.

  • If the address is within a range that's in the server-level IP firewall rules, the connection is granted. Server-level IP firewall rules apply to all databases managed by the server.

  • If the address isn't within a range that's in any of the database-level or server-level IP firewall rules, the connection request fails

Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • Thanks, the ip of linux server is in the firewall list (it works properly if connect directly with php test script) – cesare Jul 30 '20 at 15:10
  • Laravel version: 7.10.3 – cesare Jul 31 '20 at 07:17
  • 1
    @cesare What is the result with `tcp:db.database.windows.net,1433` as a value of `DB_HOST`. I'm trying to find if `Laravel` can cause problems. `Laravel` simply creates a new `PDO` isntance using `sqlsrv` driver (but only if the driver is available) or a new PDO instance using `dblib` driver. I assume, that `sqsrv` is available (source is in ``..\database\connectors\SqlServeConnector.php`). – Zhorov Jul 31 '20 at 14:35
  • with `tcp:db.database.windows.net,1433` is working! Thanks, for your help. Please update the answer so I can accept the solution. – cesare Aug 01 '20 at 18:35
  • I'm facing the same error, this did not fix it for me. Still getting "Login failed for user ..." – Deepak Thomas Aug 30 '22 at 11:03